Topics Search

Reset a text field to NULL with VBA code

Reset a text field to NULL with VBA code
Views: 224
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:

Similar posts...

 

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

updating field whose value is the word NULL and Not a typical 'null' value Icon
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.
 

Combo Box Reference

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

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

How to support nullable integer (number) field in Access / VBA Icon
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 a form having "Required" fields

Resetting a form having "Required" fields Icon
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

how to clear a validation of text box in VBA Icon
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 Code - Replacing Null Values with 0

VBA Code - Replacing Null Values with 0 Icon
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:
 

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

VBA to clear or reset text fields from a click of a command button Icon
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?
 

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

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

How to handle Null Date in email ?

How to handle Null Date in email ? Icon
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
 

Auto Populate Field Based on other fields

Auto Populate Field Based on other fields Icon
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