How To Create A Combo Control On An Access Form

Once you can comfortably build tables, queries and forms in Access, you may want to focus further on your forms design to ensure your database is really user friendly. One way to do this is to use one or more Combo controls for data entry. Combos let the user select data from a list rather than having to type it in from scratch, so it can be a quicker and easier way to add data and avoids the problems of users typing incorrect spellings or abbreviations. This article shows you how to create a Combo on an existing form.

So what actually is a Combo? Combo stands for "Combination" and once the control is added to a form a user can click on the down arrow to reveal a list of choices. Clicking on any of the choices adds that data to the table. Alternatively the user can type in their own data in the actual box. So the Combo allows either data entry via the pop down or via direct entry, hence the name Combination.

Suppose you've built an Access database and then created a table listing all your books. This Books table has these headings (fields); Book ID, Title, Author, DateObtained and Type. You then use the Form Wizard and build a columnar form based on this table using all its fields. Columnar forms show each record in a single column and makes it easy to view existing records or add new ones.

As you're going to get some help inputting details for your books you want to change the "Type" box on the form to a Combo and offer only four choices, Fiction, Non Fiction, Travel or Science. This way when you or your helper adds a new book record, the data for "Type" is added via the pop down which shows the four choices.

With your Books Table open, switch to design view. You'll see a grid pattern containing all your form headings. Each heading has a white box, which is used for data entry and a label which identifies the heading on the form. Click once on the "Type" white box to select it. Then from the upper Toolbar choose Format, Change to, Combo Box. You'll now see a Combo for the "Type" heading.

Next we'll tell Access which choices you want on the Combo pop down. So with the "Type" Combo still selected, click on the Properties Toolbar button, (the button has a hand pointing to a page). The Combo properties sheet is now displayed. In the properties sheet ensure the "All" tab is selected. Then look for the heading "Row Source Type" which should say by default "Table/Query." Change this by using the pop down to "Value List." Value List means that we'll tell Access which values to offer. Then look one heading below to "Row Source" and click into the right hand white box.

Type in, including the quotes and semi colon separators, "Fiction"; "Non Fiction"; " Travel"; "Science" then close the properties sheet, save the form design change and switch back to regular form view. Your "Type" Combo should be alive and well so we'll test it. Use the lower navigation bar to select any record, then try changing "Type" using the Combo and select one of your four choices.

If the resulting box isn't wide enough on the form to show the data properly then the box can be easily resized. To do so, switch back to form design view and again select the white "Type" Combo. You'll see small black sizing handles around the box. Hover over the handle on the right hand vertical edge until the cursor shows a two way arrow, then drag to the right to make the white box bigger. The save the form design change and switch back to regular form view and check that the box size is now the correct size.

If you use the combo in this way, you will find that a user can ignore the pop down choices and type in any text they like in the box. It's quite common in Access database design to stop users being able to do this, so only data via the pop down can be entered. To do this switch back to form design view, select the Combo control and turn on its property sheet. Further down the properties under the "All" tab you'll see "Limit to List". Change this from "No" to "Yes". Save your form design change, switch back to form view and test "Type" once again.

Now you'll find that Access only allows data chosen from the pop down list. Try typing in some other text and then click off the box. You'll get an Access error message "The text you entered isn't an item on the list". Click OK and you're taken back to the Combo, so you've got to choose one of the options to continue.

Once you start using Combos you may need to be able to change the pop down options. For example you may decide to add a new book type "Comics" (for the books in the attic perhaps). You could edit the Combo properties in form design view, but it can be a pain to do this more than once.

If your data for the Combo pop down is likely to change from time to time you can create a table listing all your book types. Then in the Combo property sheet, in form design view, change "Row Source Type" back to "Table/Query", and change "Row Source" by clicking its pop down to select your table which lists all the types. Then save the form design, change back to form view and try out the amended Combo. If you subsequently add a new type to the Types table, then using the "Types" Combo on the form will include the new type in the pop down.

You can use Combos in other ways too, for example to set search criteria on a search form. To learn much more about the power of Access consider attending one of the many training courses available and fast track your Access skills.