MS Access Combobox

An MS Access combobox is a very useful feature of Access and will enhance the look and feel of your forms. It will also keep your data consistent in your tables.

Let’s take an example of countries.

Your form may require name, address and country details. If for example you wanted
to record the United Kingdom. The United Kingdom can be known by a few other
names. For example UK, England, Great Britain and Britain. Now if you entered a
different country name each time you wanted to use the United Kingdom this can be
problematic for a number of reasons.

If you wanted to search on how many customers you have in the UK you would get a
misleading result. Your query may bring up all customers in the UK if you enter this
as criteria for searching, but what about records that have been entered as
‘England’?

The same problem would occur with the USA, US, America, United States of
America.

There is also the possibility of spelling a country incorrectly. If you have one record
with Brazil and another with Brasil then when you come to search you will get the
wrong results.

So how can we get around this problem?

The answer is to use a dropdown box or what is known in Microsoft Access as an
MS Access Combobox. This will also speed up data entry because you will be selecting an item from a list and not typing it in.

An MS Access Combobox can use a table as it’s data source, but I prefer to use a query. The reason for this is you can filter out the items you need in a query. Supposing you just want a list of European countries on your form. With a query you could set criteria for this and limit to the ones you need. Also there will be a performance improvement as queries are faster than tables.

You can use any type of data for this lesson, but I will work on using a table of country names.

1) Set up a table of countries and have a primary key ID field.

Your table should contain

Field Name: ID (with a data type of Autonumber. Make this field a primary key field).

Field Name: CountryOfOrigin  (with a data type of text. The length can be around 50).

2) Save the table with a name of ‘tblCountry’

Now create a query based on the table you just created.

1) Select both fields in the table and add to the query grid.

2) Save the query with a name of qryCountry.

Create a new form or open and existing one.

I find it just as easy to create the combo box by using the Access Wizard.

Click on the controls tool box and make sure the control wizards button is pressed.
You can move your mouse over each control in the toolbox to find it’s name shown in
a small yellow box.

Click the control called ‘Combo box’ and keeping your mouse button pressed, drag
it onto the form. Click on the form at the position you wish to place it.

Access will bring up a combo box Wizard form.

As we are getting the information for our combo box from a query we created earlier
you should select the first option ‘that says ‘I want the combo box to look up the
values in a table or query”.

Click the ‘Next’ button.

Now the Wizard is asking you where your combo box information is coming from. For
instance is it coming from a table or a query and if so which one? As we created a
query earlier I am going to select the qryCountry query.

Access will now ask which fields you want to show in your combo box. It is always a
good idea to show the ID field so I am going to select that as well as the country field.
I essentially select both fields on this occasion.

Click the ‘Next’ button to continue.

Access will now show you how your combo box data is likely to look.

You can now adjust the width of the columns in your combo box. To do this move
your mouse pointer over the edge of the column headings until the mouse pointer
becomes a cross arrow shape. Now drag the column to suit your preference.

Also it is good practice to hide the ID field so drag the edge of the ID column to the
left until it cannot be seen.

Now you will be left with a single column of data for your combo box.

Click the ‘Next’ button to continue.

This next part is rather tricky.

Access is asking if you want to store the value you select from your MS Access Combobox or use the value later. As we are not dealing with relating tables at this stage I will just leave this step and click the ‘Next’ button to continue.

(NOTE: Normally the table that sits behind our form would contain a Country ID field
with a data type of ‘Long Integer’. We would then store the ID field value from our
combo box into the Country ID field in the table that sits behind our form)

We are at the final step and Access now asks what label name we want to give to our
combo box.

Type something descriptive in here and then click the ‘Finish’ button.

That’s it, we are done.

Now run the form.

As you can see there is no danger of typing in the wrong information when you have a combo box. Every record will always have the same values to select from so you cannot again type for example ‘England, Britain, UK’ etc. Just select UK from the MS Access combobox or US and your form and data will now become more consistent.

Watch the video below for more information…

Tags: