473,382 Members | 1,132 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

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

204 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 2888
NeoPa
32,556 Expert Mod 16PB
Have they tried using the <Escape> key?
Jan 1 '21 #2
Petrol
204 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
455 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
204 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
455 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
204 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
455 Expert Mod 256MB
You're welcome.
Oops message too short. Hopefully OK now!
Jan 1 '21 #8
NeoPa
32,556 Expert Mod 16PB
Fine now ;-)
Jan 2 '21 #9
isladogs
455 Expert Mod 256MB
Lol 😁
Jan 2 '21 #10
NeoPa
32,556 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
455 Expert Mod 256MB
Lol !
Jan 3 '21 #12

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

Similar topics

1
by: Paulb1us | last post by:
I can't access the rows of my dataGrid after I call a dg.Delete() on some of the rows. I populate a datagrid from a datatable like this dataGrid1.DataSource = dt; Then I loop through each...
2
by: Ken Powers | last post by:
Hello everyone, I'm getting a strange error when I try to bind a combo box to a Dataset. Here's my code! Private Sub Get_Data() Try Dim sqlcmd = New SqlCommand("Select * from Utility order...
0
by: Ken Powers | last post by:
Hello everyone, Sorry about the repost, my second VB.NET App is done with the exception of this error. I'm getting a strange error when I try to bind a combo box to a Dataset. Here's my code! ...
4
by: AH | last post by:
hi everyone, when binding a datatable or even dataview to combo box (win form), I occassionally will encounter the problem of the selectedvalue (read from the selectedindexchanged event) as of...
4
by: Shyguy | last post by:
I have a combo box that searches for records in the database. When a name is typed in that is not in the database, Access shows a message box that says, The test you entered isn't an item in...
8
by: sara | last post by:
I have a report that runs fine with data. If there is no data, I have its NO Data event sending a MsgBox and cancelling the report. Then it seems I still get the 2501 message on the Open Report...
5
by: Henry Stockbridge | last post by:
Hi, I have a combo box on a form whose purpose is to add a record to the form's underlying table. There are several (16) fields that the combo box carries with it, only a few that are visible...
4
by: oldroboman | last post by:
Every time I try to use the combobox wizard, it starts up, runs through to the end letting me put in my choices then at the end it throws up this error and then quits and does nothing else... "Data...
15
by: deanndra | last post by:
I thought I had it figured out yesterday. WRONG! I have 2 combo boxes, CBUnit and CBSub. When value is clicked within CBUnit, it should bring up corresponding sub-values in CBSub. These combo boxes...
1
by: brent78 | last post by:
I have 2 questions. 1)How can I do a setfocus on the first item in a combo box populated with a query. I figured I could somehow use the index of 0 but I can't figure out the syntax to do it....
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.