Not in List - Have the new value selected in combo box

[Mark]

I am using the not in list event in a combo box for customers when placing
an order. When a customer isn't in the list, the code asks the user if they
wish to add the customer and opens a customer input form. When this form is
closed, I want the combo box to fill with the new value, instead of the user
having to reselect the new data from the combo box. I have tried
Me.cboCustomer = NewData but I get a RunTime Error 2113: The Value you
entered isn't valid for this field. My combo is bound to CustomerID
(Autonumber) and therefore isn't allowing the text of a surname. I have
tried doing dlookups to get the CustomerID from the new surname and can't
get that to work either. Any suggestions? My code is below.

thanks


Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
If Not IsNull(DLookup("CustomerSurName", "tblCustomer",
"CustomerSurName=""" & NewData & """")) Then
Response = DATA_ERRADDED
Exit Sub
End If
If MsgBox("""" & NewData & """ is not in the customer list. Add it?",
33, "New Customer?") <> 1 Then
Response = DATA_ERRCONTINUE
Me!cboCustomer = ""
Exit Sub
End If
Me.cboCustomer.Undo
DoCmd.OpenForm "frmCustomerNew", A_NORMAL, , A_ADD ' Data Entry Mode.
Forms!frmCustomerNew!CustomerSurname = NewData
Response = acDataErrAdded
Response = DATA_ERRCONTINUE
Me.cboCustomer.Requery
Me.Refresh
Me.cboCustomer = NewData
'Me.cboCustomer = DLookup("CustomerID", "tblCustomer", "CustomerSurname=
'" & NewData & "'")

End Sub

 

[Brian Bastl]

Mark,

first, make sure that the Limit To List property of the combobox is set to
yes.
Then see if the following will work for you.

HTH,
Brian

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

If MsgBox(NewData & " is not in the customer list. Add it?", vbYesNo,
"New Customer?") = vbNo Then
'user doesn't want to add customer, so clear combo
Response = acDataErrContinue
Me.Undo
Else
'otherwise, open form and pass the string
DoCmd.OpenForm "frmCustomerNew",,,,acFormAdd, acDialog, NewData
'requery combo box
Response = acDataErrAdded
End If

End Sub


'code to place NewData in appropriate control on frmCustomerNew

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.CustomerTextBox = Me.OpenArgs
End If

End Sub