By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Handling possible Null value when Insert or Update record

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2. Set dbs = CurrentDb
  3.  
  4.  Dim strRID As String
  5.  Dim strRver As String
  6.  Dim strAllergen As String
  7.  Dim strDescription As String
  8.  
  9.  strRID = Me.[txt_recipeID]
  10.  strRver = Me.[txtRecipeVer]
  11.  strAllergen = Me.[Txt_Recipe_Allergen]
  12.  strDescription = Me.[txt_Description]
  13.  
  14.  
  15. If IsNull(DLookup("productID", "TProduct", "([productID] ='" & Me.[txt_recipeID] & "') and ([ProductVersion] = '" & Me.[txtRecipeVer] & "')")) = True Then
  16.  
  17.  ' Create a new record in the TProduct table:
  18.  
  19. dbs.Execute " INSERT INTO TProduct " _
  20.  & "(productID, ProductVersion, Description, Allergen, vendorID, VendorLocation) VALUES " _
  21.  & "('" & strRID & "', '" & strRver & "', '" & strDescription & "', '" & strAllergen & "', '10000', 'NYC');"
  22.  
  23. dbs.Close
  24.  
  25. MsgBox "Open ingredient to verify changes!"
  26.  
  27. Else
  28.  
  29. Dim Msg, Style, Title
  30. Msg = "ID " & strRID & " and Version " & strRver & " Exist. OK to Proceed, Cancel to Cancel."
  31. Style = vbOKCancel
  32. Title = "Record Exist"
  33.  
  34. Response = MsgBox(Msg, Style, Title)
  35.  
  36.   If Response = vbOK Then
  37.  
  38.      dbs.Execute "Update TProduct SET " _
  39.       & "[productID] = '" & strRID & "'," _
  40.       & "[ProductVersion] = '" & strRver & "'," _
  41.       & "[Description] = '" & strDescription & "'," _
  42.       & "[Allergen] = '" & strAllergen & "'," _
  43.       & "[vendorID] = '10000'," _
  44.       & "[VendorLocation] = 'NYC'" _
  45.       & "Where [productID] = '" & Me.[txt_recipeID] & "' and [ProductVersion] = '" & Me.[txtRecipeVer] & "';"
  46.      dbs.Close
  47.  
  48.     Else
  49.     Exit Sub
  50.     End If
  51.  
  52. MsgBox "Open ingredient to verify changes!"
  53.  
  54. End If
  55.  
  56. End Sub
  57.  
May 7 '12 #1

✓ answered by Rabbit

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

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,356
Use the NZ function to convert the nulls to an empty string.
May 7 '12 #2

P: 68
Thanks. That worked. I am still learning VBA's build-in functions.
May 8 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.