Combo NotInList

[Dave]

The message board contains numerous posting regarding the NotInList event. I
used most of the suggested code with the exception of a popup form. For some
reason, I can't get Access 2003 to stop displaying the "text not in list ..."
error message. The new item appears in the box unselected so when it is
selected, the problem goes away. I checked the cbobox listcount property and
it doesn't increase after acDataErrAdded. So what might I be missing? The
code is below:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available ShipVia choice. " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DM?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblShip", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs.Fields("ShipVia").Value = NewData
rs.Update
lg = rs!ShipID
rs.Close

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

Set rs = Nothing
Set db = Nothing

End Sub

Always appreciate the help.

 

[Arvin Meyer [MVP]]

Try this generic NotInList code. It has been well tested:

http://www.datastrat.com/Code/NotInListCode.txt

 

[Dave]

Thank you Arvin

I'm guessing there might be a time delay issue adding the item to the table
and the requery of the combobox. Plus the code I was using did not have the
Undo command included. I did use a popup form to display to the user and it
looks like things are working as they should. Seems to be a good event
(NotInList), but seems to create difficulties until you get use to it.

Thank you for your help and the code.

Dave