How to cancel a combo box "Not in List" error

[Petrol]

Good morning, and Happy New Year, everybody!

My New Year will be a little happier if I can solve the following problem.

I have a form with a combo box to select, from the underlying dataset, a record on which to work. I don't want the user to have an option to enter new list entries, so "Limit to List" is set on. The form also contains a Cancel button to allow the user to reverse any wrong changes and get out. However, if the user enters in the combo box a name that is not in the dataset, Access immediately produces the message "The text you entered isn't an item in the list". There is no way Access will allow me to proceed while the invalid text remains in the box. This occurs even if he clicks the Cancel button, because the "Not in List" event fires before even the MouseDown event of the Cancel button. In fact the order of events includes
combo.NotInList
combo.BeforeUpdate
combo.AfterUpdate
combo.LostFocus
CancelButton.MouseDown
CancelButton.OnClick
meaning that I can find no way of honouring the user's desire to cancel his mistake and get out. He has to think of backspacing over his entry (or using Ctrl-Z, if he is aware of this) and then using the Cancel button. This is rather non-intuitive and seems to defeat the purpose of providing a Cancel button.

The only way I can think of solving the problem is to

• In a combo box "Not in List" event procedure programmatically clear the entered text, set a public flag to remind me that the error occurred, and then tell Access to ignore it (Response=acDataErrContinue);
• In the Before or AfterUpdate event, check the flag and if set, clear it and produce the "Not in List" error message - after temporarily restoring the erroneous text so the user can see what he's done;
• Then return to the combo box to force him to clear or correct it.

This seems an incredibly clumsy way of achieving my purpose. Is there a simpler way?

[NeoPa]

Have they tried using the <Escape> key?

[Petrol]

Well I don't know, NeoPa, but I'm not sure that that is very intuitive either. I am really hoping for a solution where they can use the button they see on screen in front of them, rather than thinking of escape keys, ctrl-Z sequences, backspace keys etc.

[isladogs]

Hmm. My tests show the user doesn't need to backspace over the invalid entry. As soon as the Access not in list message box is cleared, the combo dropdown values are displayed and selecting a value clears the original entry automatically.

You could also add a default value to the combo e.g. Please enter a value from the list.

OR perhaps better still add code so it automatically drops down when clicked ... with or without a message

Private Sub Combo0_GotFocus()
    MsgBox "Please enter a value from the dropdown list" 'optional message
    Me.Combo0.Dropdown
End Sub

[Petrol]

Yes, this is true. The problem is that the user thinks the person he wants is in the recordset, but they aren't. So now he realises that there is no entry in the dropdown list that suits his purpose, and all he want to do is get out quickly and cleanly.

As you suggest, I may have to force the dropdown list - I hadn't thought of that - but everywhere else in the application I let him enter the first few characters first so that he doesn't need to scroll through the entire dropdown list of hundreds of names.

[isladogs]

That's precisely why I suggested the use of both a default value / dropdown.
However, if there are hundreds/thousands of names, I would suggest using cascading combos to limit what is shown in each one

[Petrol]

I guess the takeaway from all this is that what I was hoping to achieve - a nice obvious Cancel button to cancel the erroneous entry - is simply not doable. However, forcing the dropdown is a reasonable alternative, so I'll do that. Thanks for your help - I wasn't aware of the combo.dropdown method.

[isladogs]

You're welcome.
Oops message too short. Hopefully OK now!

[NeoPa]

Fine now ;-)

[isladogs]

Lol 😁

[NeoPa]

Multiple spaces are always parsed as a single space so put as many as you like between words as they all count towards your minimum ;-)

Unfortunately I could hardly give this explanation AND illustrate it in the same post of course :-D

[isladogs]

Lol !