Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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, 01:57 PM
Authorized User
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Event Procedure help

To start with, 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 DocName, , ,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 19th, 2005, 07:37 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


  You could simply do this:

Add this line to your code

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

This will close the first form while the user works in the second form adding the new customer. Then in the On Close event of the new customer form, re-open the first form. This will force a refresh.

Better still, re-open the first form and pass the necessary parameters to open it on the customer just added.

I usually force a form closed when the user goes to another form to prevent clutter and confusion for the user. There is usually only one form open at a time in my databases, with clear navigation buttons, and all the min, max, close etc buttons disabled or removed.


Old May 19th, 2005, 04:07 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

Change the value of the Response argument.

This might be telling you what you already know, it might not, but others are listening too.

The arguments that are in the Access built in events have very official sounding names (Cancel, Response, NewData), but they are merely variables created in the calling procedure (which procedures we have no access to), and which are then passed by reference to the event that we do have access to.

In cases such as the variable Cancel, the calling procedure checks its value when the event procedure ends, and acts accordingly.

You can actually change the names of those variables, and all will run identically. You could change this event to
  Private Sub CustomerNameID_NotInList(Nerd As String, DoWhat As Integer)
with [u]no chan</u>g[u]e</u> in the behavior of Access.

There are three values that you can set Response to, each of which has an associated constant:
   acDataErrContinue (0)
   acDataErrDisplay  (1)
   acDataErrAdded    (2)
   I can't find a reference for what those values do, so I'll wing it:
  • acDataErrDisplay instructs Access to display the default message. You might come here, log what was attempted, then tell the user to “go blow.”
  • acDataErrContinue suppresses the error message. It does nothing about that which caused the error.
  • acDataErrAdded suppresses the error message, and requeries the control. This tells Access that the data is now there to be used, so go get it.
The following is an example from Help:
Private Sub Colors_NotInList(NewData As String, Rsp As Integer)

    Dim ctl As Control
    Set ctl = Me!Colors

    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list.  Add it?", vbOKCancel) = vbOK Then

        ' Set Rsp argument to indicate that data is being added.
        Rsp = acDataErrAdded   ' <———<<< Does nothing at this moment, but
                               '         modifies behavior when Sub ends.

        ' Add string in NewData argument to row source.
        ctl.RowSource = ctl.RowSource & ";" & NewData


        ' If user chooses Cancel, suppress error msg & undo changes.
        Rsp = acDataErrContinue   ' <———<<< Does nothing at this moment, but
        ctl.Undo                  '         modifies behavior when Sub ends.

    End If

End Sub
I think you should do this:
Private Sub CustomerNameID_NotInList(NewData As String, Response As Integer)

    Dim s As String
    s = """" & NewData & _
        """ -- Add New Customer?"

    If MsgBox(s, vbYesNo) = vbYes Then

        ' Possibly pass NewData, as I've done here.
        DoCmd.OpenForm "frmCustomer", , , , , acDialog, NewData 

        Response = acDataErrAdded

  ' Else do nothing;  let the default behavior run.

    End If

End Sub
Opening the form as a dialog will halt execution of the _NotInEvent() procedure until the form closes. Passing the data in the OpenArgs of the form gives you localized access to that piece of info within the form that opens.

It might be a good idea to examine the data in the _NotInEvent() procedure once the form closes, just to make sure the data actually got added rather than the form just being closed.
If it wasn't added, exit the sub before setting [brown]response[/brown, and the default behavior will do its job.
Old May 20th, 2005, 05:36 PM
Authorized User
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts


Thank you for your advice. The code you suggested performs exactly as I wanted. I did have to add an Event Procedure to "frmCustomers" "On Open" to go to a new record. All error messages disappeared and the "CustomerNameID" combobox accepts the newly added Customer.

Thanks again,

D. Bartelt

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 2 May 19th, 2005 03:35 AM

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