MsgBox error

[Tara]

I have a combo that uses the NotInList event to trigger a custom message box
and open up another form. I keep getting a error that says: Compile error:
Syntax error. I'm not sure what the issue is. Here's the code:

Private Sub Agency_NotInList(NewData As String, Response As Integer)

If MsgBox("This Agency is not currently in the list. Would you like to
add this Agency?", vbYesNo + vbQuestion) = vbYes Then

Response = acDataErrAdded
DoCmd.OpenForm "frmAddAgencies"

Else
Response = acDataErrContinue
MsgBox ("Please enter a valid Agency from the drop down box.")

End If


End Sub


Thanks for any help!

 

[Stefan Hoffmann]

hi Tara,

I have a combo that uses the NotInList event to trigger a custom message box
and open up another form. I keep getting a error that says: Compile error:
Syntax error.

Hmm, your code works just fine.

I'm not sure what the issue is. Here's the code:
If MsgBox("This Agency is not currently in the list. Would you like to
add this Agency?", vbYesNo + vbQuestion) = vbYes Then

The string must be in one line...



mfG
--> stefan <--

 

[Klatuu]

The error is here:

MsgBox ("Please enter a valid Agency from the drop down box.")

Should be
MsgBox "Please enter a valid Agency from the drop down box."

You only use parenhises when you are wanting a return value.

 

[Tara]

Actually, that wasn't giving me an error. It probably should have, but it
didn't. So I changed it according to your suggestion anyway, just in case.
I did figure out the issue though. However, I'm still having a problem. The
standard error message that Access gives when something is not in the list is
still being displayed, despite my custom message. The custom message
displays, I choose yes to add the agency, frmAddAgency open and at that time,
the standard message appears. Any idea why?

 

[Klatuu]

First, when you ask about a compile or syntax error, it is helpful to
identify the line on which the error is occuring.

As to your existing problem, do you have the Limit To List property of the
combo set to Yes?

Can you post back with the code as it is now?

 

[Stefan Hoffmann]

hi Tara,

The custom message
displays, I choose yes to add the agency, frmAddAgency open and at that time,
the standard message appears. Any idea why?

You need acDataErrContinue as response value.

mfG
--> stefan <--

 

[Klatuu]

Her response values are correct.
acDataErrAdded when she wants to add the record
acDataErrContinue if not.

I haven't tested her method of just opening the form. I do it differently.
I first add the record, requery the form, then open the form to the new
record, so I don't know if that has any effect, but I don't think so/

 

[Tara]

Thanks Klatuu. From now on I'll post the problem line. As for the limit to
list property, it is set to yes. Here's the code as it is currently:

Private Sub Agency_NotInList(NewData As String, Response As Integer)

If MsgBox("This Agency is not currently in the list. Would you like to
add this Agency?", vbYesNo + vbQuestion) = vbYes Then


Response = acDataErrAdded

DoCmd.OpenForm "frmAddAgencies"


Else
Response = acDataErrContinue
MsgBox "Please enter a valid Agency from the drop down box."

End If

End Sub

 

[Tara]

I just posted a response to this. I have a meeting in 10 minutes, but I'll
check back in this afternoon. Thanks for the help!

 

[George Nicholson]

Response = acDataErrAdded

DoCmd.OpenForm "frmAddAgencies"

Cart before Horse.
acDataErrAdded tells Access that the data has been added, but it hasn't
been.

From what we see here at least, as soon as the 2nd form is open, the code
will exit the NotInList event before the user has had any chance to interact
with the 2nd form, much less add a record. This is why the 'built-in" error
message triggers.

Take a look at the 2nd method in:
http://support.microsoft.com/kb/197526/en-us
Use NotInList Event to Add a Record to Combo Box

This code opens a 2nd form in Dialog mode, so that the NotInList code will
pause until the 2nd form closes. This allows the new value to be added
before exiting the NotInList event and will allow "Response =
acDataErrAdded" to be an accurate statement.