combo box with new record option

[btappan]

I have an access 2007 database with a combo box that is filled with part number (which is also the uid) from its corresponding table. when you enter a part number that is already a record and then Tab out of that field, it takes you to that record, but when you enter a part number that doesnt exist and tab out it does nothing. Is there away I could make it display a message that says "the number you entered does not exist in the table, would you like to add it as a new record?" and then you could click OK and proceed with the entry? Your help on this one would be greatly appreicated.

 

[RuralGuy]

See if this link helps. Mastering the Combo Box NotInList Event

 

[btappan]

Thanks man. I was able to get it to add the part number to the list but it doesnt prompt the user at all, nor does it take you to that record like i need it to. I also am looking for it to clear all the other fields on the form when doing this to clear it for entry of the new part number with new details. is it possible

 

[RuralGuy]

What code did you put in the NotInList event?

 

[btappan]

in the visual basic view i have:

Option Compare Database

Private cboCombo65_NotInList(NewData As String,
Response As Integer)
On Error GoTo cboCombo65_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The job title " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCATALOG NUMBER([CATALOG NUMBER]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCATALOG NUMBER_NotInList_Exit:
Exit Sub
cboCATALOG NUMBER_NotInList_Err:
MsgBox Err.DESCRIPTION, vbCritical, "Error"
Resume cboCombo65_NotInList_Exit
End Sub

the table column i need the data to go to is "CATALOG NUMBER" and the combo box that i am trying to use is "Combo65"

 

[RuralGuy]

Do you have LimitToList set to YES? You said it is adding the [CATALOG NUMBER] but *not* asking any questions?

 

[btappan]

i just changed it to yes, but am still having problems. Heres is the code:

 

[RuralGuy]

Posting the code in line as you did before is *much* better that providing a picture of it. It is even better if you surround your code with code tags to preserve the style like so:

Option Compare Database

Private cboCombo65_NotInList(NewData As String,
   Response As Integer)
    On Error GoTo cboJobTitle_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The job title " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Acme Oil and Gas")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblJobTitles([JobTitle]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new job title has been added to the list." _
            , vbInformation, "Acme Oil and Gas"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Acme Oil and Gas"
        Response = acDataErrContinue
    End If
cboCATALOG NUMBER_NotInList_Exit:
    Exit Sub
cboCATALOG NUMBER_NotInList_Err:
    MsgBox Err.DESCRIPTION, vbCritical, "Error"
    Resume cboCombo65_NotInList_Exit
End Sub

What problem are you having now that LimitToList is YES?

 

[btappan]

I attached the first error i get and then after clicking oki get the box that says "the text you entered isnt an item in the list" ,"select an item for the list or enter text that matches one of the items in the list" I get no option to add it to the list. I posted the pic b4 so you could see the code in red.

 

[RuralGuy]

In the properties sheet for the ComboBox next to the On Not In List does it say [Event Procedure]? You may need top press the "..." button again to get things in sync. It looks like you might have renamed the ComboBox at least once.

 

[RuralGuy]

The first line of the code should be:

Private [B][COLOR="Red"]Sub [/COLOR][/B]cboCombo65_NotInList(NewData As String,
   Response As Integer)

 

[btappan]

I now get an error that says "Expected: identifier." instead of the "Expected: list seperator or )."

 

[RuralGuy]

Did you press the "..." button and does it take you to your code?

 

[btappan]

yes, and i have saved it with every button i see.

 

[btappan]

RuralGuy, check your PM's

 

[boblarson]

Looking at your screen shot of your code I noticed that you have the same event listed twice for the control. Remember, you can't type your own (or copy) events in. You can copy the code in between them, but since you have two Private Combo65_NotInList events listed, it will choke on it.