NotInList code in Access 97

[meadows43]

Hi,

I successfully use the code below in Access XP at home, but when I try at work in Access 97, nothing happens. I need the user to be prompted to add the value to the table if it is not in the combo box. Any suggestions? Here's the code:

Private Sub cboAcct_NotInList(NewData As String, Response As Integer)
Dim dbs As DAO.Database

Dim rs As DAO.Recordset

If vbNo = MsgBox("The 'Master Customer Number' specified is not in the list." & vbCr & vbCr & "Do you want to add it now?", vbQuestion + vbYesNo + vbDefaultButton2, "basNotInList") Then

Response = acDataErrContinue

End If



Set db = CurrentDb

Set rs = db.OpenRecordset("Accounts", dbOpenDynaset)

rs.AddNew

rs.Fields(0) = (cboAcct.ListCount + 2)

rs.Fields(1) = NewData

rs.Update

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

Response = acDataErrAdded
End Sub
Thanks,
Chris

 

[Mile-O]

Adding New Entries to Table From a Combobox (DAO)

 

[meadows43]

Thanks for this. But, now I get the error "Item not found in collection." and it still doesn't add it. I made a few changes. Here's what I've got:

Private Sub cboAcct_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
    
    ' provide text constants to reduce text later and allow for faster execution
    ' due to added speed from the compilation of constants
    Const Message1 = "The master customer number you have entered is not in the list."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    
    ' database and recordset object variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
        ' open a connection to the current database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Accounts", dbOpenDynaset)
        ' using the recordset object
        With rs
            .AddNew ' prepare to add a new record
            .Fields("Master_Number") = NewData ' add unfound data into field
            .Update ' update the table
            .Close ' close the recordset object
        End With
        Response = acDataErrAdded ' confirm record added
    Else
        Me.cboAcct.Undo ' clear the entry in the combobox
        Response = acDataErrContinue ' confirm the record is not allowed
    End If
    
Exit_ErrorHandler:
    ' de-initialise our object variables
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
Err_ErrorHandler:
    ' display error message and error number
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler


End Sub

 

[Mile-O]

Comment out the On Error Goto Err_ErrorHandler line and tell me which line the code, when you run it, stops on.

 

[meadows43]

Mile,

The problem was that I had an underscore in the field name where there didn't need to be one. I changed that and now it works. Thanks again.

Cheers,

Chris