Why getting invalid use of null

I have the following code in the not in list event of a combobox on a form.  But when I enter a value that is not in the list I get an invalid use of null message.  ????

Private Sub cboInsurance_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_CustomerID_NotInList

    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        MsgBox "Please try again."
    Else
        Set db = CurrentDb
        Set Rs = db.OpenRecordset("tblInsuranceCompaniesLU", dbOpenDynaset)

        Msg = "Please enter an insurance company name" & vbCr & "InsuranceCompany."
        NewID = Me.cboInsurance   '.Value        'InputBox(Msg)
        Rs.FindFirst BuildCriteria("InsuranceCompany", dbText, NewID)

        Do Until Rs.NoMatch
           NewID = InputBox("InsuranceCompany " & NewID & " already exists." & _
                    vbCr & vbCr & Msg, NewID & " Already Exists")
           Rs.FindFirst BuildCriteria("InsuranceCompany", dbText, NewID)
        Loop

        Rs.AddNew

        Rs![InsuranceCompany] = NewID

        Rs![InsuranceCompany] = NewData

        Rs.Update

        Response = acDataErrAdded

    End If

Exit_CustomerID_NotInList:
       Exit Sub
Err_CustomerID_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

End Sub

Open in new window

SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
NewID is defined as a string.  Are you sure the input box is returning a value?  It is much safer to define NewID as a variant and then check it for null after the input.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Please tell us what line of he code the error the error is generated on?
It might not be the "NewData" that is null...

You have a lot more there than simple code to add a value to a list, ...so there may be other factors at play here.
Helen FeddemaCommented:
I have somewhat similar boilerplate code to add a new item -- you could try this instead:

Private Sub cbo________NotInList(strNewData As String, intResponse As Integer)
'Set Limit to List to Yes
'See Add-to Combo Boxes (AA 161).mdb
'Created by Helen Feddema 24-Apr-2011
'Last modified 7-Apr-2010

On Error GoTo ErrorHandler
   
    Dim cbo As Access.ComboBox
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
  
    'The name of the table that is the combo box's row source
    strTable = "________________"
  
    'The type of item to add to the table
    strEntry = "_____________"
  
    'The field in the lookup table in which the new entry is stored
    strFieldName = "_______________"
  
    'The add-to combo box
    Set cbo = Me.ActiveControl

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set rst = CurrentDb.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = strNewData
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.