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

Hi,

  You could simply do this:

Add this line to your code

stDocName = "frmCustomer"
DoCmd.Close
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.

HTH


mmcdonal

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 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:    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: I think you should do this: 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.

BrianWren

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