To start, unfortunately, we still use Access 97 at work. Hopefully we will be converting to Access 2002 in the near future.

The CustomerNameID ComboBox is Bound to tblCustomers. Only the Customer Name is displayed in this ComboBox. I put the Event Procedure in the “On Not in List” event property field

I would like to create an Event Procedure that will ask “Add New Customer” then open the data entry form frmCustomers to add the New Customer Name, Phone Number, Fax Number and Contact Name. After the information is filled in, I would like it to close form frmCustomers and return to the CustomerNameID ComboBox and have the CustomerNameID ComboBox accept the newly Customer Name.

Below is what I have so far. I put the Event Procedure in the “On Not in List” event property field.

Private Sub CustomerNameID_NotInList(NewData As String, Response As Integer)
Dim db As Database

Set db = CurrentDb()

If vbYes = MsgBox(NewData & " -- Add New Customer?", vbYesNo) Then

     Dim stDocName As String
     Dim stLinkCriteria As String

     stDocName = "frmCustomer"
     DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End Sub

This VBA Code will open the form frmCustomers and allow me to add the new information. After I close frmCustomers, I return to the CustomerNameID ComboBox, however, I receive an information box that states “The text you entered isn’t an item in the list”, “Select an item from the list, or enter text that matches one of the items”. How do I write the Code to Update the newly added Customer to the CustomerNameID ComboBox?

Any help would be greatly appreciated.


D. Bartelt

__________________
D. Bartelt

Try this inside the NotIntList event Sub...

CustomerNameID.RowSource = CustomerNameID.RowSource
'This refreshes the Combo Box
Response = acDataErrAdded
'This tells Access that the data has been added and stops the error from going any further.

If I may add a word of caution as well. We had a few people try out Office 2002 last year as part of a Beta test, and it has caused a number of headaches. I would strongly suggest going to 2003 instead. It has similar features to 2002, but is a lot more stable. The biggest problem we had was that any 2000 database which was opened by 2002 was changed somehow, and was then inaccessible to 2000. We have yet to see the problem 2003.

Hope that helps.

Mike

Mike
EchoVue.com

I concur with Echovue. Access 2002 is a little schizophrenic in that it cannot really decide whether it is 2000 or 2002. If you are forced to use 2002 and want to preserve 2000 formats, before you open any databases with 2002 go into Tools:Options and set the default database type to 2000. This still causes a few problems when the 97 databases are converted, but the majority are resolved, and you will still be able to use the 2002 enhancements in a 2000 format. Also be careful that in 2002, the default Reference Libraries are ADO, so if you have DAO databases, you will have to, sometimes(!), reset the Libraries, but always when creating a new database. All in all, 2003 is a MUCH better option.