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?

[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.

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

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
SteveL13Author Commented:
I was able to fix this issue by making Active in the underlying table a default value.  Problem solved.
0
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.