467,166 Members | 1,021 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

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

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?
3 Weeks Ago #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

  • viewed: 1416
Share:
11 Replies
NeoPa
Expert Mod 16PB
Have they tried using the <Escape> key?
3 Weeks Ago #2
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.
3 Weeks Ago #3
isladogs
Expert 128KB
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
3 Weeks Ago #4
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.
3 Weeks Ago #5
isladogs
Expert 128KB
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
3 Weeks Ago #6
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.
3 Weeks Ago #7
isladogs
Expert 128KB
You're welcome.
Oops message too short. Hopefully OK now!
3 Weeks Ago #8
NeoPa
Expert Mod 16PB
Fine now ;-)
3 Weeks Ago #9
isladogs
Expert 128KB
Lol 😁
3 Weeks Ago #10
NeoPa
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
3 Weeks Ago #11
isladogs
Expert 128KB
Lol !
2 Weeks Ago #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@newsgroup.nospam | 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.