Problem with unbound combo box and NotInList event

[Carl Rapson]

I have a table that contains a customer ID (Long), linking to a master
customer table. I want the user to be able to add new customers by typing in
a new customer name (not an ID), so I set up a combo box on my form that's
not bound to the customer ID field in my underlying table, but has a query
as its row source:

SELECT [Customer Name],ID FROM [Customer Master] ORDER BY [Customer Name];

(The customer ID field is a hidden field on the form, and I set the customer
name value in the OnCurrent event). I figured this should allow the user to
type in a new customer name. I set the Limit To List property of the combo
box to Yes and set up a NotInList event as follows:

Private Sub cboCustomerID_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rs As DAO.Recordset

strMsg = "'" & NewData & "' is not a recognized Customer Name" & vbCrLf
& vbCrLf
strMsg = strMsg & "Do you want to add " & NewData & " as a new
Customer?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to try
again."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new customer?") = vbNo Then
Response = acDataErrContinue
Else
Set rs = CurrentDb.OpenRecordset("Customer Master", dbOpenDynaset)
rs.AddNew
rs![Customer Name] = NewData
rs.Update
rs.Close
Set rs = Nothing
Response = acDataErrAdded
End If
End Sub

Selecting "No" from my custom message box works just fine, but the default
"The text you entered isn't an item in the list" message box appears after I
type a new customer name and select "Yes". I checked the [Customer Master]
table at that point, and the new customer name has indeed been added.
Furthermore, the new customer name is in the combo box list, but the combo
box for some reason doesn't move to the new entry. If I then manually select
the new customer in the combo box list, everything proceeds as expected.

Can anyone give me a clue as to why this is happening? Might it have
something to do with the combo box being unbound?

Thanks,

Carl Rapson

 

[Michel Walsh]

Hi,


I would try to signal the combo box that its list has changed, adding a line
like:

cboCustomerID.RowSource=cboCustomerID.RowSource

just before the Response = acDataErrAdded. That should do the job.


Hoping it may help,
Vanderghast, Access MVP