OnN otInList won't work

I've done this countless times before.  But for some reason if I enter a new string of characters in this particular combobox, it won't work.  I get the message telling me it is a new item and asks if I want to add it, I answer Yes, I get "The text you added isn't an item in the list....."

I don't get it.

Here is the code:

Private Sub cboCompanySoldTo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboCompanySoldTo_NotInList

    Dim intAnswer As Integer
    Dim strSQL As String

    intAnswer = MsgBox(NewData & " is not in the list. Would you like to add it?", vbQuestion + vbYesNo, "Company")

    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblCompaniesLU([Customer]) " & "VALUES ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbSeeChanges
        Response = acDataErrAdded
    End If

Exit_cboCompanySoldTo_NotInList:
    Exit Sub

Err_cboCompanySoldTo_NotInList:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_cboCompanySoldTo_NotInList
    
End Sub

Open in new window

SteveL13Asked:
Who is Participating?
 
SteveL13Connect With a Mentor Author Commented:
I was able to fix this issue by making Active in the underlying table a default value.  Problem solved.
0
 
bfuchsCommented:
dont you have to requery the combo box after adding?
0
 
SteveL13Author Commented:
I can't even get that far.  I get the message that says "The text you added isn't an item in the list....." and can't go any farther.
0
 
SteveL13Author Commented:
I think I just figured out what's wrong.  In the combobox is another field named ActiveStatus.  The record being added to the table has to be "Active" (text) and I don't know how to make that happen.  I tried:

    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblCompaniesLU([Customer]) " & "VALUES ('" & NewData & "');"
        strSQL = "INSERT INTO tblCompaniesLU([ActiveStatus]) " & "VALUES ('" & "Active"
        CurrentDb.Execute strSQL, dbSeeChanges
        Response = acDataErrAdded
    End If

Open in new window


But it doesn't work.. Soo how do I get the new string of characters in the table AND mark it as "Active" also?
0
 
SteveL13Author Commented:
I was able to fix this issue by making Active in the underlying table a default value.  Problem solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.