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:
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.
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.
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.