CODEWITHSUNDEEP
ms-accessvba
Dan Wood
Dan Wood

Reputation: 25

Avoid Invalid use of Null error when the field is empty

I created a form in Access 2010 and I fill in the form based on text boxes (B and C) and selection form a combobox (A). The problem is if any of the text box left empty I get “Invalid use of Null” error. I noticed I can avoid this error if I Dim the text boxes as Variant instead of Integer. I am not sure if this is the right solution. Can I change the following script to avoid this error?

Private Sub ABCBoxEnter_Click()
Dim A As String
Dim B As Integer
Dim C As Integer
If Not IsNull(Me!ComboBox.Value) Then
    A = Me!ComboBox.Value
    B = Afield
    C = Bfield
    values = "VALUES ("
    values = values & "'" & ID & "','" & A & "','" & B & "','" & C & "')"
    SQL = "INSERT  INTO ContactTable (ID, A, B, C)"
    SQL = SQL & values
    DoCmd.RunSQL SQL
    Me.B.Value = ""
    Me.C.Value = ""
End If
End Sub

Upvotes: 2

Views: 6023

Answers (2)

Gustav
Gustav

Reputation: 55816

You can use Nz:

values = values & "'" & Nz(ID) & "','" & Nz(A) & "','" & Nz(B) & "','" & Nz(C) & "')"

Or, better, implement my function CSql

Upvotes: 2

Erik A
Erik A

Reputation: 32642

The big question is: what do you want to happen when a value is Null.

As you noticed, integers and strings can't contain Null, while a Variant can. Dimming your intermediary variables as Variant is an approach that's okay in your code. You can also use the Nz function to replace Null with an empty string if referred to as a string, or 0 if referred to as an integer.

Note that if you were to remove the ' apostrophes from your SQL statement, you would run into an error, and you would have to use Iif(IsNull(B), "Null", B) where you just have B in the SQL statement.

Upvotes: 1

Related Questions