Topics Search

Reset a text field to NULL with VBA code

Reset a text field to NULL with VBA code
Views: 210
I have a database which contains a text field called TransactionDescription. I want to reset this field back to a NULL (ie: blank value) on all records in my table.
Sponsored Links:

More topics

 

Combo Box Reference

Preview
I am new to VBA for Access. I have created a form using the Access GUI and would now like to reference the controls in VBA. Forgive me if this is a dumb question. This is only my first bit of code after reading a short book on Access 2010 VBA, but I do have experience in other programming languages.

I am struggling with referring to my combo boxes on the main form.

My code:

Sub TestMacro()

Dim strcboBox1 As String
Dim strcboBox2 As String
Dim ReSet As Recordset

strcboBox1 = frmRsales!byComboBox!Text
strcboBox2 = eyComboBox.Text

If strcboBox1 == "1993" And strcboBox2 == "1993" Then _
Set ReSet = CurrentDb.OpenRecordset("1993_All_Quarters")
Do Until ReSet.EOF
MsgBox ReSet!Company
ReSet.MoveNext
Loop

End Sub

How would I pass the combo box content to a string variable
 

updating field whose value is the word NULL and Not a typical 'null' value

Preview
I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL. and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.
 

Resetting a form having "Required" fields

Preview
I have a form which has 4 fields set as "Required". When I try to dirty the form and then try to reset it using a button, it does clear all the fields, but when I try to close the form, it gives me the error that the "xyz" field is required. I want to keep those fields set as required, but yet would like to have a reset button that can clear the fields for the user if they have made a mistake during data entry.

Second problem is that on the same form, I have a subform. The Reset button is in the header section of the main form. By clicking this button, I would also like the subform to be reset. The reset button includes code for the subform to reset, but it also has the same problem error. There are required fields on it. How can I reset a form and provide the primary key fields and the required key fields Null values when they can't have Null values
 

VBA to clear or reset text fields from a click of a command button

Preview
Is it possible to add vba code to a command button that when clicked the event will clear existing data or reset data, so when the user is on the form, the previous data entereddoesn't display? if so how can this be done?
 

how to clear a validation of text box in VBA

Preview
I created a added data form with input text box.

I set a validation rule in there. such <1000.

However, even I remove all number in text box, but I still got validation message and much enter quality number (<1000) before closed form. I set a reset button with codes as Me.SALECODE = Null and Me.Refresh

Reset button does not work. I still got validation message when I move into other text box even there are no any number in first validation text box.
 

How to support nullable integer (number) field in Access / VBA

Preview
I have run into a dilemma that integer (number) variables in VBA do not seem to support null, but as for a column type in a table, that is allowed to support null.

I recall while developing with the DB2 database, that the DB2 driver / API supported the concept of fields having null indicator variables. That is a second variable that may be checked to find out the null state of the field in question. The correct way to deal with nullable fields on DB2 was to use that second variable, do the null check BEFORE attempting to access the variable which is suppose to contain the value of said field.

Does Access / VBA have anything comparable in ADODB.Recordset objects?

As-is, Access / VBA turns the NULL that was in that field of the record into 0 upon issuing a SQL UPDATE as the Integer variable defaults to 0.
 

Resetting Autonumber Field In Code

Preview
I know. I Know. But is it possible to reset an Autonumber Field in Code. Here's what I have tried.

1) Delete all records in an existing Table.

Now I would like to change the attributes of the IDField, so

The current Field Attributes are dbAutoIncrField. I want to reset it to dbFixedField to clear the AutoNumber Attribute

fldIDX.Attributes = dbFixedField

But it doesn't like this. Error is Invalid Operation.

I ASSUME THIS MEANS I CANNOT CHANGE THE ATTRIBUTE. OH WELL.

If anyone has any insight into how to Reset an Autonumber Field without repairing and compacting the database, I woull really enjoy seeing how you did it.
 

VBA Code - Replacing Null Values with 0

Preview
I'm a struggling to come up with a custom function that replaces Null values in a table with Zero.

My table in its basic form has two fields.
Field A: Client
Field B: Quantity

The data is pulled from elsewhere in the database, but when it extracts it will have the client, and quantities. but some clients will have a Null quantity. e.g. below:
 

not null validation

Preview
I am connecting oracle 11G database with access and then trying to create a form.
In the form we have to validate that the text filed is not null.

I have tried using this simple code :

if isNull(text0.text) then
msgbox "null values not allowed"
end if

but then its not working. I am using it in before_update section.

which code should be used to verify that and also where should I pasting the code.
I have been trying to fix this since the last 2 days. but its not helping
 

If and Then statement to return a null or blank field value

Preview
As the user name states I am new at vba for access. I just need some help with a line or 2 of code let me display it first then explain what I need it to do

Private Sub_Report_Open
Dim AnyText As String
If [Remarks TX] ="AnyText" Then
[IWC WC NM1] = Null
Else Cancel
End If
End Sub

I probably wrote that all wrong and I apoligize, but what I need it to do is If the remarks tx field contains text then the iwc wc nm1 field should be displayed as a blank field otherwise leave it alone. It's probably a simple fix like all things in access but getting to that solution is a pain. Is this correct