As you increase your skills in using Microsoft Access you may need to create Combos on forms. Combo controls are a popular way to allow data entry because the user can either enter their own data or make a selection from the pop down - hence the name Combo. Did you know that you can create the pop down list in different ways? This article describes how to do this effectively using different method, depending on whether the lookup data changes or not.

Once you create a form in Access based on a table or a query, you can then create a Combo control for one or more of the fields on the form. Once created you can change the properties of the Combo to create the lookup list in three different ways; adding the lookup data in the Combo properties, adding the lookup data via a table or adding the lookup data via a query. To show how this is done, suppose you've created an Access database, with a table called TBLStaff. The table contains the headings Firstname, Surname, Post and gender. We then create a form based on this table, and call the form FRMSTAFF.

Creating lookup data in the Combo properties

If the combo lookup values are going to always be the same, for example the options for gender Male or Female, then we can add the lookup data to the Combo properties directly. With the form in design view, we decide to create a Combo for the gender field. So we first change the type of the GENDER field box to a Combo. Then we open the Combo property sheet. In the property sheet select the Data tab. Click into the white box to the right of "Row Source Type" and select Value List. Then click into the box right of "Row Source" and type in "Male;Female" without the quotes, but with the semi colon. Save the form and then switch to regular form view and test the combo. You'll now see the two options available in the pop down.

Creating Combo lookup data in a table

If the combo lookup values might change on occasions, for example for staff posts, then a better method to create the lookup data is using a separate table. So still working in your Access database containing the table TBLSTAFF, create a new table, called TBLPOSTS with a single field, POSTS which can also be the primary key. Then enter several staff posts in the table, and close the table.

Then open the form FRMSTAFF in design view, and change the type of the POST field to a Combo and open the Combo property sheet. This time, in the Row Source Type property select Table/Query. In the Row Source property click the pop down and select the Posts table you created earlier TBLPOSTS. The save the form and switch to data entry view and test the Post Combo. You'll see the options available from the Posts table. If you later change data in the Posts table, for example by adding a new post, and reopen the form and click the Post Combo pop down, you'll see the changed list available.

Creating Combo lookup data using a query

You can also create the POST lookup data directly from a query if you feel you don't need a separate table for the values, but you will then only see a list of current posts, not new ones. However if you add a new member of staff with a new post, or change one of the existing posts to a new one in the table, then the next time you open the form the Combo will show in the pop down. To do this you first need to ensure you have some records in the staff table, including posts. You might like to include more than one member of staff with the same post to illustrate how this works.

We then create a brand new query based on the table TBLSTAFF, and add only the POST field to the query. If you run the query, you'll see a list of all the posts for the current staff, including more than one of the same post. To only show each post once in the query we need to use the Totals command. So keep the query open and switch back to query design view. On the upper toolbar click the Totals Sigma button (same symbol as Autosum in Excel). Using Totals in a query will group the data so only one value of Post is shown per line when the query is run. Run the query to check this, then save the query as QryPosts and close the query.

Then open the form FRMSTAFF in design view, and open the POST Combo Property sheet. Leave Row Source Type as Table/Query but change the Row Source to the query you just created, QRYPOSTS. Save the form, switch to regular view and test the revised POST Combo. You'll a list of all the currently used Post values.

This article has described three ways to create lookup data for a Combo by adding lookup values to the Combo properties, to a separate data, or by extracting values using a query. Hopefully this will have given you a brief insight into the power and versatility of Combo controls in Access forms. If you're interested in learning more about Access you might like to consider attending a training course and really boost your Access skills.