Bypass NotInList Event when Quitting

[Andy]

I have main form with just one Combo box. Data is limited to List and I have
a NotInList Event (to provide a more User friendly message).

If a User starts to type data into the Combo which is not in List but then
decides to hit the Quit Command button (simply containing Quit command), the
NotInList Event fires first so the Quit cannot be executed.

Is there a way to bypass the NotInList Event in this circumstance to allow
the Quit command to be executed?

Thanks.

 

[Douglas J Steele]

The NotInList event is actually happening before they click on the Quit
button: it would be caught in the LostFocus event of the combobox. You
really have to take care of it before you can do anything else.

 

[Andy]

Thanks Doug - The LostFocus event on the Combo Box had occurred to me so I
tried setting the value of the Combo box to Null via this event but same
problem persisted when hitting the Quit button ...

 

[Douglas J Steele]

Yeah, I just spent a little time playing with it, and nothing obvious
occurred to me.

 

[Marshall Barton]

I have main form with just one Combo box. Data is limited to List and I have
a NotInList Event (to provide a more User friendly message).

If a User starts to type data into the Combo which is not in List but then
decides to hit the Quit Command button (simply containing Quit command), the
NotInList Event fires first so the Quit cannot be executed.

Is there a way to bypass the NotInList Event in this circumstance to allow
the Quit command to be executed?

Try using:
Me.thecombo.Undo
in the NotInList event.

 

[Douglas J Steele]

But won't that take effect every time the user enters something not in the
list, not just when they go for the Quit button?

 

[Marshall Barton]

But won't that take effect every time the user enters something not in the
list, not just when they go for the Quit button?

Sure, but it's an invalid entry so what good is it?

 

[Douglas J Steele]

Sure, but it's an invalid entry so what good is it?

I thought what was desired was to have NotInList work to allow the user to
add items when they wanted to, but that they also wanted the ability to
simply hit the Quit button and have it stop what it was doing. If you're in
the midst of putting in a non-existent entry in the combobox when you do
that, it won't let you.

 

[Marshall Barton]

"Marshall Barton" wrote

I thought what was desired was to have NotInList work to allow the user to
add items when they wanted to, but that they also wanted the ability to
simply hit the Quit button and have it stop what it was doing. If you're in
the midst of putting in a non-existent entry in the combobox when you do
that, it won't let you.

I thought Andy said that the NotInList event didn't do
anything except generate a user friendly error message.

Hmmm, I guess that is the problem, he wants to prevent the
msgbox when the button is clicked. In this case, I don't
think it's possible because, as you said earlier, the
message box appears before the button even realizes it has
been clicked.

 

[Andy]

Doug/Marsh,

Thanks for you ideas on this and sorry that it took a couple of days for me
to get back.

This is a really frustrating one - I have tried various combinations of
triggered events and the best solution, albeit not perfect for my application
was to :-

On the combo :-
set Limit to List to 'No'
in the On Got Focus event, set fore color to Black
in the After Update event, check one of the invisible columns in Combo to
check if Null and if Null (incorrect data), put out User friendly message
about incorrect data and set fore color to Red (to highlight it).
On the Quit command button :-
just Quit

This deals with incorrect data, setting color to red (I can't get the
SetFocus to work as I would prefer) and lets the User quit at any time.

Why not perfect? If the User types incorrect data and then hits Quit
command, the MsgBox can't be avoided while quitting.

 

[Marshall Barton]

That sounds like it's about the best you can do in this
situation.

I do want to point out that checking for a Null in an
invisible column is the long way around to finding out that
the item is not in the list. Just check if the ListIndex
property is -1

 

[David C. Holley]

From what I can gather, when a user is in a comboBox you want them to
be able to simply close the form or quit Access minus the various
warnings about the item doesn's exist in the list? Correct?

I've done a bit of playing around with the NotInList event but not in
that specific context. The RESPONSE parameter takes one of three values
that tells Access what to do. I don't recall the values, but they are
listed in Access VBA Help under the NotInList event. My guess is that
you'll have to code the NotInList event to handle the error first and
probably include a message box to your users.

 

[jokobe]

Hi David,

I was looking for an answer too, the RESPONSE did the trick. I set it to "0"
and no message appears.

Jokobe

P.S.: All things are difficult before they are easy