Reset a text field to NULL with VBA code

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:

Related Topics

Combo Box Reference
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

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
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
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

how to clear a validation of text box in VBA
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.

VBA to clear or reset text fields from a click of a command button
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 support nullable integer (number) field in Access / VBA
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
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.


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
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
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

How to handle Null Date in email ?
Forum, Access 2010.
Email code works fine except where one field has a null date.
An SQL in the vba code collects the data however, one field relates to Date Last Repayment Made

LastLoanRepayDate(CStr([LoanID])) AS LastRepayDate

LastLoanRepayDate() is a function that returns the date of the most recent repayment.
Being smart. I have the function returning 1/1/1995 where no repayment has been made.
Normally not an issue because other code filters any date prior to our company start date but for the email, we want the text to be blank or "NA" or other but not 1/1 1995.

Any ideas how this can be handled

If and Then statement to return a null or blank field value
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

Text to Currency formatting issue VBA
I have code that creates a new table with designated fields(see below). The rstDest table has Currency values for each month and the rstSource table has text values. I need to change these text values to currency and am running into some issues.

One thing is yes, the text field can be null, there won't always be a value.

I have tried this:

Auto Populate Field Based on other fields
I am a total access newbie when it comes to designing a database. I have managed to create a simple web database for project management but have run into a snag.

I want to have a field (field 3) auto-populate based on what is entered into two other fields (field 1 & field 2).

Field 1 is a date field (when a presentation is due). Field 2 is a yes/no checkbox (if checked, it means no presentation is necessary). Field 3 would be a calculated text field (?) because text cannot be entered into Field 1.

If field 1 is null and field 2 is checked, then I want "NA" to auto populate into field 3. However, if field 1 is null and field 2 is not checked, I want "TBD" to auto populate into field 3. If field 1 is not null, then I want field 3 to be null

Problem with IIf
I use the following in a field in my report.
IIf(IsNull([X]), ,[Y])
where the
X field is defined as text, and populated with either null (no entry) or a text string.
Y field is defined as number and carries some number value.
Both X and Y are table entries.
Problem I have is that when X is null, I get the desired empty space, but when X is not null, I get #Error.

Executing MySQL Statement
I have a variable that contains a perfectly acceptable MySQL statement (as in, I can copy it into mysql directly and it works) yet when I run it through CurrentDb.Execute VariableName I get "Too few parameters. Expected 1."


Which the makes


The only thing I can think of is that I'm not declaring the column names because as you can see by the code, it's rather dynamic. So.

Reset created item counter on form
So lets say I create 80 text boxes. They'd be auto named Text1 through Text 80. Then I delete the last 40 of them. So now I have Text1 through Text 40. If I create a new one, it becomes Text81. Is there a way to make it start with the next available (Text41) instead? Also if I create a combo or check it uses the same number (combo82, check83). So can those start at 1 for each type? I can go through and rename them all manually in the property sheet, other tab, name field, but If I can just reset the counter to the next available I'd really prefer that.

VBA IF then Else's
I'm still somewhat new to this, and I've been unable to find what I'm looking for. I don't have any code for this at this point, but I can try to demonstrate what I want. (Do not want anyone to write this for me, but nudge me in the right direction.)


SQL Query to pull data from a table with 6 fields.
Then, based on the query results

If Field 1 is the only one not null, than run Function1
If Field 1 and 2 are are not null than run Function2

Also, during this process the results of the query need to somehow populate to the TempVars, because the Functions refer to TempVar values

(Each field is always populated in order, Field 2 cannot be populated if Field 1 is not

Importing text file with blank fields changes to null
When I import a text file with fields separated by ~ character, if I have a field with blanks, the field in the table appears as null. Also if I have valid characters in the text field plussome spaces at the end, it will trim out those spaces.
I need access to import the field just as it is, respecting any blanks. The field es defined as text. Is there some option I need to set up ? looks like its doing an auto trim. Mi version es Access 2003.

Importing text file with blank fields changes to null
When I import a text file with fields separated by ~ character, if I have a field with blanks, the field in the table appears as null. Also if I have valid characters in the text field plus some spaces at the end, it will trim out those spaces.

I need access to import the field just as it is, respecting any blanks. The field es defined as text. Is there some option I need to set up ? looks like its doing an auto trim. Mi version es Access 2003.

Using an expression to add a record based on a field containing any text
In Access 2002 I am attempting to add text to Field 2 if Field 1 contains any text at all using a query expression.

I have tried innumerable combinations of IIf, Is Not Null, Like using wildcards etc. For example IIf([Composer] is not null), "CA" (Suggested on an MS site) however I can't seem to find the right expression.