469,934 Members | 2,757 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

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

183 128KB
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?
Jan 1 '21 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_GotFocus()
  2.     MsgBox "Please enter a value from the dropdown list" 'optional message
  3.     Me.Combo0.Dropdown
  4. End Sub

11 1772
NeoPa
32,232 Expert Mod 16PB
Have they tried using the <Escape> key?
Jan 1 '21 #2
Petrol
183 128KB
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.
Jan 1 '21 #3
isladogs
317 Expert Mod 256MB
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo0_GotFocus()
  2.     MsgBox "Please enter a value from the dropdown list" 'optional message
  3.     Me.Combo0.Dropdown
  4. End Sub
Jan 1 '21 #4
Petrol
183 128KB
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.
Jan 1 '21 #5
isladogs
317 Expert Mod 256MB
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
Jan 1 '21 #6
Petrol
183 128KB
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.
Jan 1 '21 #7
isladogs
317 Expert Mod 256MB
You're welcome.
Oops message too short. Hopefully OK now!
Jan 1 '21 #8
NeoPa
32,232 Expert Mod 16PB
Fine now ;-)
Jan 2 '21 #9
isladogs
317 Expert Mod 256MB
Lol 😁
Jan 2 '21 #10
NeoPa
32,232 Expert Mod 16PB
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
Jan 2 '21 #11
isladogs
317 Expert Mod 256MB
Lol !
Jan 3 '21 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paulb1us | last post: by
2 posts views Thread by Ken Powers | last post: by
reply views Thread by Ken Powers | last post: by
4 posts views Thread by AH | last post: by
4 posts views Thread by Shyguy | last post: by
5 posts views Thread by Henry Stockbridge | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.