Handling possible Null value when Insert or Update record

[Joe Y]

I use a command button to copy several text box data on a Form and insert as new or update record in Table ‘TProduct” using below code.

This has been working fine except when any of the fields is null then the error message “Run-Time error ‘94’: Invalid use of Null” showed and VBA stopped.

For example, if the textbox “Allergen” is null. When I click the button, the error message appeared. The debug highlighted, in yellow, the line “strAllergen = Me.[Txt_Recipe_Allergen]”.

Can anyone tell me how to modify the code to resolve this issue?

Thanks.

Dim dbs As Database
Set dbs = CurrentDb
 
 Dim strRID As String
 Dim strRver As String
 Dim strAllergen As String
 Dim strDescription As String
 
 strRID = Me.[txt_recipeID]
 strRver = Me.[txtRecipeVer]
 strAllergen = Me.[Txt_Recipe_Allergen]
 strDescription = Me.[txt_Description]
 
 
If IsNull(DLookup("productID", "TProduct", "([productID] ='" & Me.[txt_recipeID] & "') and ([ProductVersion] = '" & Me.[txtRecipeVer] & "')")) = True Then
 
 ' Create a new record in the TProduct table:
 
dbs.Execute " INSERT INTO TProduct " _
 & "(productID, ProductVersion, Description, Allergen, vendorID, VendorLocation) VALUES " _
 & "('" & strRID & "', '" & strRver & "', '" & strDescription & "', '" & strAllergen & "', '10000', 'NYC');"
 
dbs.Close
 
MsgBox "Open ingredient to verify changes!"
 
Else
 
Dim Msg, Style, Title
Msg = "ID " & strRID & " and Version " & strRver & " Exist. OK to Proceed, Cancel to Cancel."
Style = vbOKCancel
Title = "Record Exist"
 
Response = MsgBox(Msg, Style, Title)
 
  If Response = vbOK Then
 
     dbs.Execute "Update TProduct SET " _
      & "[productID] = '" & strRID & "'," _
      & "[ProductVersion] = '" & strRver & "'," _
      & "[Description] = '" & strDescription & "'," _
      & "[Allergen] = '" & strAllergen & "'," _
      & "[vendorID] = '10000'," _
      & "[VendorLocation] = 'NYC'" _
      & "Where [productID] = '" & Me.[txt_recipeID] & "' and [ProductVersion] = '" & Me.[txtRecipeVer] & "';"
     dbs.Close
 
    Else
    Exit Sub
    End If
 
MsgBox "Open ingredient to verify changes!"
 
End If
 
End Sub
 

[Rabbit]

Use the NZ function to convert the nulls to an empty string.

[Joe Y]

Thanks. That worked. I am still learning VBA's build-in functions.