On Not In List Error

[Tara]

Repost from last week.

I have a combo that uses the NotInList event to trigger a custom message box
and open up another form. 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 opens and at that time, the standard message appears.

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

DoCmd.OpenForm "frmAddAgencies", acNormal, , , acFormAdd, acDialog

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

End If

Response = acDataErrAdded

End Sub



Any idea why the standard message is still being displayed?

Any help is appreciated!

 

[Dirk Goldgar]

Repost from last week.

I have a combo that uses the NotInList event to trigger a custom message
box
and open up another form. 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 opens and at that time, the standard message appears.

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

DoCmd.OpenForm "frmAddAgencies", acNormal, , , acFormAdd, acDialog

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

End If

Response = acDataErrAdded

End Sub


Any idea why the standard message is still being displayed?

Any help is appreciated!

I think your line ...

Response = acDataErrAdded

.... belongs after the DoCmd.OpenForm line (since it's currently forcing the
"added" response no matter how the user replies to the MsgBox), but I don't
see how its current position would be causing the behavior you describe.

Are you using frmAddAgencies to add the new agency before closing it? When
you exit the NotInList procedure with the response acDataErrAdded, it will
requery the combo box and see if NewData is now in the list. If it isn't,
the NotInList event should be raised again. I would have thought, though,
that you'd get your own message at that point, not the standard one. I
could be wrong about that.

Are you getting the standard not-in-list message before you close
frmAddAgencies? If that's so, something odd is happening, since you are
opening that form in dialog mode. Do you have some code in frmAddAgencies
that would close and reopen it?

 

[Tara]

Thanks for the reply Dirk. I'm getting the standard message as soon as
frmAddAgencies is opened and I don't have any code in frmAddAgencies at all.
I think you may be on to something though. But I'm still pretty new to code
so I'm not sure I completely understand you. I think you're saying I might
be exiting the routine before the agency is added, so therefore the message
is appearing as soon as I exit the routine, which would be when
frmAddAgencies opens. Did I understand what you were trying to say? If
that's the case, how do I suspend the code until frmAddAgencies is opened,
data added and then closed again?

Thanks!

 

[Dirk Goldgar]

Thanks for the reply Dirk. I'm getting the standard message as soon as
frmAddAgencies is opened and I don't have any code in frmAddAgencies at
all.

That's hard to explain. I see that you are opening frmAddAgencies in dialog
mode, according to this statement:

DoCmd.OpenForm "frmAddAgencies", acNormal, , , acFormAdd, acDialog

So it should not be possible for any code to run in the calling form until
that form is closed.

Does the calling form have any code running in the Timer event?

I think you're saying I might
be exiting the routine before the agency is added, so therefore the
message
is appearing as soon as I exit the routine, which would be when
frmAddAgencies opens. Did I understand what you were trying to say?

Yes, that's what it looks like, but as I said above, it shouldn't be
happening, based on the code you posted.

If that's the case, how do I suspend the code until frmAddAgencies is
opened, data added and then closed again?

Opening the form in dialog mode, as you're doing, is supposed to do exactly
that. We need to figure out why it isn't working. Is the code you posted a
true copy/paste of the code in the form? You didn't transcribe it by
hand -- and possibly introduce a discrepancy -- did you?

 

[Tara]

Nope, no code at all in the Timer Event.

It is a true copy/paste. I tried this same thing in another database today
and I have the same issue in that db too.

I sure do appreciate your help with this. I'm just stumped...

 

[Dirk Goldgar]

Nope, no code at all in the Timer Event.

It is a true copy/paste. I tried this same thing in another database
today
and I have the same issue in that db too.

I sure do appreciate your help with this. I'm just stumped...

This is puzzling. If you'd like to send me a cut-down copy of your
database, containing only the elements necessary to demonstrate the problem,
compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it to
the address derived by removing NO SPAM and ".invalid" from the reply
address of this message. If that address isn't visible to you, you can get
my address from my web site, which is listed in my sig. Do *not* post my
real address in the newsgroup -- I don't want to be buried in spam and
viruses.

 

[Tara]

Thank you so much Dirk!! Would tomorrow be okay? I'm getting ready to leave
for a meeting in 5 minutes, but I plan on being in the office tomorrow.

There's no deadline on my end to get this fixed. The process works, so
getting the error message is just an annoyance at this point. I can forsee
this potentially causing issues down the road though.

I really appreciate this.

 

[Dirk Goldgar]

Thank you so much Dirk!! Would tomorrow be okay? I'm getting ready to
leave
for a meeting in 5 minutes, but I plan on being in the office tomorrow.

Sure. As I said, I can only look at it as I find the time, anyway.

There's no deadline on my end to get this fixed. The process works, so
getting the error message is just an annoyance at this point. I can
forsee
this potentially causing issues down the road though.

I'm quite puzzled as to what's going on, and would like to figure it out.

 

[Graham Mandeno]

Hi Tara

As Dirk says, the code *should* not continue to the next line after
DoCmd.OpenForm until the form you are opening is either closed or is made
invisible.

A couple of things to check though:

1. Did you copy and paste your code *exactly* into your posted message?
Sometimes it's possible to mis-count the number of commas. Personally, I
prefer to used named arguments like this:
DoCmd.OpenForm "frmAddAgencies", DataMode:=acFormAdd,
WindowMode:=acDialog

2. Are you sure that acDialog has not been redefined somewhere? I suggest
you add this line after DoCmd.OpenForm to check:
MsgBox "acDialog is " & acDialog
You should get a message box saying "acDialog is 3".

Also, two tips:

1. As Dirk has already said, you should move Response = acDataErrAdded to
before the "Else". Otherwise, you will get the standard "not in list"
message even if the user clicks "No" to your question.

2. I suggest you pass NewData to your form via the OpenArgs parameter:

DoCmd.OpenForm "frmAddAgencies", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData

Then, in the Load event of your form, you can set the DefaultValue
property of the AgencyName field to the value that was entered in the combo
box:

If Me.DataEntry and not IsNull(Me.OpenArgs) Then
AgencyName.DefaultValue = """" & Me.OpenArgs & """"
End If