Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 18th, 2005, 03:03 PM
Authorized User
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Event Procedure help

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
Old May 18th, 2005, 03:11 PM
Friend of Wrox
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts

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.


Old May 19th, 2005, 03:35 AM
Authorized User
Join Date: Jul 2004
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Event Procedure malfunction - duplicating records dstein4d Access VBA 0 April 4th, 2008 03:02 PM
Event - Sender & Event args dash dev C# 2005 9 December 9th, 2007 07:24 AM
Choosing the correct Event Procedure ru1 Access 3 October 31st, 2006 04:30 PM
Form Event Procedure Help dbartelt Access 2 June 7th, 2005 04:10 PM
Event Procedure help dbartelt Access VBA 3 May 20th, 2005 05:36 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.