How to let access autocomplete values in a combobox that contains values from a table?

Question

How to let access autocomplete values in a combobox that contains values from a table?

Anonymous

Hey guys. What I'm a trying to do in Microsoft Access should be simple, but I can't get it done.

I have a table called "Countries". This table consists of Fieldname ID and a fieldname Country. The fieldname "Country" contains a bunch of countries. I have another table (called "Mutationtable" that contains a lot of fields, but I have a particular field called "Country". For this field I want a combobox where the list should be taken from the fieldname Country in the table "Countries". So far so good.

I have it done. It works perfectly. My only problem is, when I open the table, to fill on values for the fields, I don't want to open the dropbox to keep scrolling through the list. I should be able to type for example "United States" and Access should Autocomplete this for me. But it doesn't do it. I noticed it does autocomplete for the comboboxes where I have a "Valuelist". But for this particular Combobox, I have the Row Source Type set to "Table/Query", and the row source says the following:

SELECT [Countries].[Country] FROM Countries;

What can I do to let access autocomplete this for me? (I haven't created a form yet, I thought it should work before creating the form because all my comboboxes that as a Valuelist as Row Source Type works with autocomplete just fine.

How can I achieve this?

(PS, I did read other threads, but their solution did not work for me....which is why I decided to ask my own question)

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

First off, most of us really dislike the "Lookup Field" datatype. It sounds like you're wanting to use a Combo Box in a Table - this is very limited and often confusing. See The Access Web for a critique. Your Country field in mutationtable should NOT be a Lookup Field; instead it should be a Number, Long Integer field matching the ID of the Countries table. In a Form you can thus store the ID (keeping the computer happy) while displaying the country name (keeping the user happy).

A Combo Box will autocomplete if its autocomplete property is turned on, and the field (Country name in this case) is the first visible column. It works well if you have the combo box's Row Source query sorted by the name field (so it will find Algeria before Azerbaijan for example).

Answer 2

Setting a combo box's AutoExpand property to True (Yes) will cause the control to progressively go to the first match as each character is entered. You'll find an example in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the section on 'entering data via a form/subforms' includes, amongst other things, a combo box to select a country, which exhibits the behaviour. Try going to an empty new record in the form and entering F.

Normally a referencing table would store a numeric foreign key value referencing the numeric primary key of the referenced table, as in my demo. If you really need to store the text Country value in the referencing table, be sure that the Country column in Countries is indexed uniquely.

Share via

How to let access autocomplete values in a combobox that contains values from a table?

2 answers