Weird combo box update issue

[Tara]

I have an issue I'm hoping someone can help me with. Basically, in some
circumstances, when a new record is entered into a form, part of the data is
not saved and an ID number is saved in it's place.

First, I have a form with a combo box (cboCustomer) that displays Customer
names. The Row Source for the combo is:

SELECT [tblCustomers].[CustID], ([CustLast]+" " & [CustFirst]+" " &
[Business]) AS Name, [tblCustomers].[CustLast], [tblCustomers].[CustFirst],
[tblCustomers].[Business] FROM tblCustomers WHERE
((([tblCustomers].[CustLast]) Is Not Null)) ORDER BY
[tblCustomers].[CustLast], [tblCustomers].[CustFirst];

The combo box has code in it's Not In List event that opens fires a custom
message box. By choosing yes in the message box, another form is
opened(frmAddCustomer) to allow users to enter new customers. Then they can
go on to add information about that customer into a subform. However, after
frmAddCustomer is opened and the user has added new data and tries to close
the form, my custom message appears again. If the user chooses No, then they
get the standard run-time error that says: The text you entered isn't an
item in the list. Debug or End. If they choose End, it gives the standard
"can't save this record" message. But, if they go ahead and close the form
and look at the table, the data is there - last name and all. If they choose
yes the second time my custom message appears, it seems to save the record
too. However, when the user closes that record and goes to another customer,
the record they just entered changes. Instead of saving the customers last
name, it changes the data in the last name field to the customerID.

The combo's NotInList Event has the following code:

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list of contacts " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this contact to the current list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new contact?") = vbYes Then
Response = acDataErrContinue

DoCmd.OpenForm "frmAddCustomer", , , , , acDialog
Response = acDataErrAdded
End If

End Sub

Any help is appreciated!

 

[Beetle]

I can't say how a CustomerID value could end up in the CustLast
field, but as far as your other issues your Not In List code is a little
off. It should be like this;

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list of contacts " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this contact to the current list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new contact?") = vbYes Then
DoCmd.OpenForm "frmAddCustomer", , , , , acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
--
_________

Sean Bailey

I have an issue I'm hoping someone can help me with. Basically, in some
circumstances, when a new record is entered into a form, part of the data is
not saved and an ID number is saved in it's place.

First, I have a form with a combo box (cboCustomer) that displays Customer
names. The Row Source for the combo is:

SELECT [tblCustomers].[CustID], ([CustLast]+" " & [CustFirst]+" " &
[Business]) AS Name, [tblCustomers].[CustLast], [tblCustomers].[CustFirst],
[tblCustomers].[Business] FROM tblCustomers WHERE
((([tblCustomers].[CustLast]) Is Not Null)) ORDER BY
[tblCustomers].[CustLast], [tblCustomers].[CustFirst];

The combo box has code in it's Not In List event that opens fires a custom
message box. By choosing yes in the message box, another form is
opened(frmAddCustomer) to allow users to enter new customers. Then they can
go on to add information about that customer into a subform. However, after
frmAddCustomer is opened and the user has added new data and tries to close
the form, my custom message appears again. If the user chooses No, then they
get the standard run-time error that says: The text you entered isn't an
item in the list. Debug or End. If they choose End, it gives the standard
"can't save this record" message. But, if they go ahead and close the form
and look at the table, the data is there - last name and all. If they choose
yes the second time my custom message appears, it seems to save the record
too. However, when the user closes that record and goes to another customer,
the record they just entered changes. Instead of saving the customers last
name, it changes the data in the last name field to the customerID.

The combo's NotInList Event has the following code:

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list of contacts " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this contact to the current list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new contact?") = vbYes Then
Response = acDataErrContinue

DoCmd.OpenForm "frmAddCustomer", , , , , acDialog
Response = acDataErrAdded
End If

End Sub

Any help is appreciated!

 

[Tara]

Thanks for getting back with me. I'm getting ready to leave for the day, but
I'll make the changes you suggested first thing tomorrow morning.

I appreciate the help!

I can't say how a CustomerID value could end up in the CustLast
field, but as far as your other issues your Not In List code is a little
off. It should be like this;

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list of contacts " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this contact to the current list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new contact?") = vbYes Then
DoCmd.OpenForm "frmAddCustomer", , , , , acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
--
_________

Sean Bailey

I have an issue I'm hoping someone can help me with. Basically, in some
circumstances, when a new record is entered into a form, part of the data is
not saved and an ID number is saved in it's place.

First, I have a form with a combo box (cboCustomer) that displays Customer
names. The Row Source for the combo is:

SELECT [tblCustomers].[CustID], ([CustLast]+" " & [CustFirst]+" " &
[Business]) AS Name, [tblCustomers].[CustLast], [tblCustomers].[CustFirst],
[tblCustomers].[Business] FROM tblCustomers WHERE
((([tblCustomers].[CustLast]) Is Not Null)) ORDER BY
[tblCustomers].[CustLast], [tblCustomers].[CustFirst];

The combo box has code in it's Not In List event that opens fires a custom
message box. By choosing yes in the message box, another form is
opened(frmAddCustomer) to allow users to enter new customers. Then they can
go on to add information about that customer into a subform. However, after
frmAddCustomer is opened and the user has added new data and tries to close
the form, my custom message appears again. If the user chooses No, then they
get the standard run-time error that says: The text you entered isn't an
item in the list. Debug or End. If they choose End, it gives the standard
"can't save this record" message. But, if they go ahead and close the form
and look at the table, the data is there - last name and all. If they choose
yes the second time my custom message appears, it seems to save the record
too. However, when the user closes that record and goes to another customer,
the record they just entered changes. Instead of saving the customers last
name, it changes the data in the last name field to the customerID.

The combo's NotInList Event has the following code:

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not currently in the list of contacts " &
vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this contact to the current list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new contact?") = vbYes Then
Response = acDataErrContinue

DoCmd.OpenForm "frmAddCustomer", , , , , acDialog
Response = acDataErrAdded
End If

End Sub

Any help is appreciated!