Full Version: Combo Box On A Form
UtterAccess Forums > Microsoft® Access > Access Forms
ritamarie123
Access 2007
I have two questions about combo boxes.
1 - I am having trouble with a combo box I created on a form. I used the wizard to create the box and wanted to limit it to the list I set up. Even though I have "limit to list" as yes it still lets users type in name not on list. On the form the drop down list shows correctly. Not sure what else I should be doing to get it to not allow entries not on list.
2 - On the table the field (fmlytype) is also set up as a combo box. Do I need a combo box both on the table and in on the form?
On the form:
The control source is fmlytype from the underlying table
Row source is list of items
Row source type says Value List
Limit to List is set to yes
Allow value list edits no
Thanks for any help.
RM
HairyBob
Hi RM,
1) You can cancel any keypress when the cursor is in the textbox portion of the combo box by setting the KeyCode parameter of the combo box's KeyDown() event to 0 as follows:
CODE
Private Sub Combo_KeyDown(KeyCode As Integer, Shift As Integer)
    KeyCode = 0
End Sub

You will, of course, lose the ability to 'drill down' to values in the combo box list however, I assume that you probably have only a small number of values in this particular combo box to select from, so this shouldn't be a problem.
(2) You don't need the combo box on the table as well as the form - the combo box on the form is sufficient.
theDBguy
Hi RM,
welcome2UA.gif
The normal behavior is that Access allows the user to type in any value they want on the combobox but Access will not accept the value if it's not on the list. Are you saying that you'd rather not have the user type in a value at all if it does not match any items on the list? If so, you can try Bob's solution of not letting the user type anything at all, or you could try Allen Browne's technique here and modify his code to stop the user input if they type something that does not have any match.
I would actually say that it is not recommended to have comboboxes at the table level. See this Access Web article for some reasons why:
The Evils of Lookup Fields in Tables
Just my 2 cents...
ritamarie123
Thanks for your replies.
The combo box is letting user enter data. No error message appears. I assumed a user might try to type in a name not on list but then when trying to exit the field it would come back with error msg and not allow. I do want people to be able to "drill" down and see the whole list and select an item if they choose not to type it.
RM
MikeLyons
Have you got any code in the combobox's NotInList event ?
If so, what if anything is the Response argument being set to?
Mike
theDBguy
Hi RM,
Not sure I understand your statement above. Are you still having issues? If so, can you post a zip copy of your db with test data? Thanks.
ritamarie123
No, there is nothing in the NotInList event. I actually have several combo boxes with the same problem and none have anything in the event procedure and nothing in response argument. Is that something I missed when I created the boxes with the combo box wizard?
Thanks for your help
RM
MikeLyons
I was just checking to see if there might have been something in the event that was having Access continue without raising an error message.
ike
ritamarie123
Attached is file with test data. Thanks once again for taking a look at.
attachment=46012:LTH_FP4_26.zip]
theDBguy
Hi,
Thanks for posting the db. So, which form and combobox should we be looking at?
Nevermind... I just looked at the first form that opens up and from your first post, I realize that we are talking about the "FmlyType" field.
Now, as I said ealier, having the combobox at the table level is not really recommended. And the source of your problem is actually in there. If you go to the Design View of your table (tblClientInfo) and look at the Lookup Tab for the field "FmlyType," you will see that the Limit to List property is set to "No."
You can either just change the Lookup Tab from Combobox to Textbox (effectively deleting the combobox at the table level), or change the Limit to List property to "Yes."
Hope that helps...
ritamarie123
Thanks for taking a look.
I have on my list to take out those combo boxes on the tables and use combo boxes on the form. (Thanks to your earlier suggestion and a little further research). Should have thought of that before I posted. Thanks for your time.
RM
theDBguy
Hi RM,
We are all happy to help! Good luck with your project.
ritamarie123
Still need help.
changed all the combo boxes on my table (tblClientInfo) to regular text boxes. Using only combo boxes on form (Client Info). Limit to List is set to yes on combo boxes. I still am able to enter data not on list. Also I see that I need to not allow null either. Any other suggestions for me to try? I'm sure it's something pretty simple I'm just not seeing.
Thanks,
RM
theDBguy
Hi RM,
just changed the "FmlyType" field on this one. See if you still have the problem.
Hope that helps...
ritamarie123
Changing the field to a text box on the table didn't make any difference. I can still type in items not listed in the form's combo box. Any other thoughts?
Thanks, RM
theDBguy
Hi RM,
Sorry to hear that. For some reason, the copy I posted worked on my machine. I get the warning message after entering an invalid item.
Ocan't think of anything else that might help. Hopefully, somebody else could verify it for us. Good luck!
HairyBob
Hi DBGuy and RM,

HAs expected, the file theDBGuy posted works for me as well - if you set the 'Limit To List' property of the combo box to 'Yes' at design time, then when you run the form, you type in a string that's not in the list then move the cursor out of the combo box, you get the standard Access 'Not In List' message and the list portion of the combo box opens to prompt the user to select an item from the list.
may well be wrong however, I suspect that what RM wants is for keypresses to be rejected if the character concatenated to (or inserted into) the string causes the string not to match the first characters of an item in the list (i.e. so that whilst the user can still type in the textbox portion of the control he/she can only type in the character if it's going to match an item in the list). Although anything's possible, this kind of goes against the way the combo box control is designed to work and begs the question as to what's wanted if the user presses the Backspace key or highlights a number of characters in the string and presses the Delete key.
mike60smart
Hi RM
On the updated Db the Combobox's will not now allow you to add an item that is NOT in the list
All I have done is made all Fields in the table Text type
Othen used the Combobox Wizard on the form to Lookup the relevant table(s)
Click to view attachment
ritamarie123
Thanks all,
I'm still not sure exactly what I had different but I made suggested changes and still couldn't get the combo boxes to work correctly. I ended up going to an earlier version from before I created the combo boxes. Changed lookup boxes on table to text and then created the combo boxes on the form. Works fine.
Thanks for all your help.
RM
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.