M
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
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