Customizing an Access Combo Box

Combo Box's are a familiar control used in most modern software packages and the web.  They offer the user a  choice of values from a drop down list, thereby improving the user friendliness of the application in question.  Access has a great Combo Box Wizard which easily allows us to create a combo box for our forms.  There are, however, some restrictions on how far we are able to customize the Combo Box using the Wizard alone.  In this post we are going to look at how we can create a Combo Box manually (from scratch), thereby gaining complete control over how our Combo Box looks, acts and what information it can store and display.

The process of creating a combo box will be illustrated by going through the procedure in relation to an Order Details form.  We shall do this by creating an empty combo box control (which will be used to enter the product item ordered), and then customize it by modifying the relevant control properties involved.  As we shall see, this allows us to determine things like how many list columns the combo box is going to have, where the data comes from to fill the list, and which field (if any) will store the selected value from the list.

Figure 1: A Customized Combo Box Control.

We will go through this process in four stages.

Stage One: Creating an Empty Combo Box Control
The first stage will involve the creation of an empty Combo Box Control.  We could well do this using the Combo Box wizard, but we are going to do this manually for learning purposes.  You can download the Combo Box Exercise Database we are going to use by clicking the link.

1. Open the Combo Box Exercise database you downloaded.
2. Open the frmOrderDetails form in DESIGN VIEW.  The easiest way of doing this is to right click the form name and select DESIGN VIEW from the menu.  There is more information about Design View and Form customization here.  When the form opens in Design View you will see there is just one text box for the OrderDetailsId field.
3. Make sure the USE CONTROL WIZARDS icon (in the CONTROLS GROUP of the DESIGN RIBBON) is not highlighted.  If it is, just click it once.  This prevents the Combo Box Wizard from starting when you do step 4 below.
   
4. Click the COMBO BOX CONTROL icon (from the CONTROLS GROUP of the DESIGN RIBBON).  When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.  You should now see an unbound Combo Box Control on the Form Design Grid. 

Figure 2: An empty Combo Box Control.

Stage Two: Setting the Combo Box's CONTROL SOURCE PROPERTY

We are now going to set the CONTROL SOURCE property so the control becomes bound to the ItemId field (NB The forms RECORD SOURCE property is already set to tblOrderDetails). This means that any item selected from the Combo Box will be stored in the ItemId field.

1. Highlight the Combo Box Control and then click the PROPERTY SHEET icon in the TOOLS GROUP of the DESIGN RIBBON.
2. Select the DATA TAB  of the PROPERTY SHEETwhen it opens.
3. The CONTROL SOURCE property is located on the top row.   Click the drop down list and select the ItemId field.

Figure 3: The DATA TAB of the PROPERTY SHEET.

Stage Three: Setting the ROW SOURCE property

The ROW SOURCE property is located just below the CONTROL SOURCE.  As we have seen the latter property relates to the Combo Box's binding to a particular field.  By contrast the ROW SOURCE property relates to the data contained in the list itself - that is to say the source of the data contained in the list.  This ROW SOURCE can be from another table or query.  In this exercise, we are going to create a new query based on tblProducts  and tblCategory using the QUERY BUILDER.  This is opened from within the ROW SOURCE property cell of the PROPERTY SHEET.  The purpose of the query is to produce a list of all products in category 1 (Stationery). Here is the procedure:

1. Click in the ROW SOURCE cell of the PROPERTY SHEET.  It is located below the CONTROL SOURCE property of the DATA TAB.  There is a Three Dots symbol at the right edge of the cell.  Click this to open the Query Builder.
   
   
2. Select tblProducts and tblCategory from the SHOW TABLE dialogue box.  You can do this by double clicking each name in the dialogue box.  
3. Click CLOSE on the SHOW TABLE dialogue box. 
4. Select itemId and itemName from tblProducts.  The quickest way of doing this is to double click each of those field names in the tblProducts Table Diagram.
5. Then select categoryId and categoryName from tblProducts.
6. Enter =1 in the CRITERIA row of the categoryId column.
7. Then click the CLOSE icon in the CLOSE GROUP of the DESIGN RIBBON.  
8. You are then prompted to save the query as an SQL Statement in the ROW SOURCE property.  Click YES to the message DO YOU WANT TO SAVE THE CHANGES MADE TO THE SQL STATMENT AND UPDATE THE PROPERTY?

Figure 4: The Query Builder.

It is also worth explaining at this point that you are able to select which query column (ie field) is bound to the the Combo Box control. This is the mechanism whereby, after being selected, the value from the Combo Box list is stored in the relevant field of the Forms underlying RECORD SOURCE. In our exercise this is going to be the first column (ie the itemId field).  We do this by setting the BOUND COLUMN property to 1.  Since this is the default value for this property we do not need to change it. It is, however, useful to understand how this binding process works.

Stage Four: Formatting the Combo Box List
This is the stage where we work on the list which is displayed by the Combo Box.  We are going to set the properties which determine how many columns the combo box is going to have, whether each column has a heading,  how wide the columns are going to be, and the overall width of the list (which can be wider than the actual Combo Box Control itself).

1. Select the FORMAT TAB of the PROPERTY SHEET.
2. Set the COLUMN COUNT property to 4.  This tells Access that there is going to be four columns involved in the list.  However, we shall see next that not all of these columns need to be displayed.
3. Set the COLUMN WIDTH property as follows: 0cm;3cm;0cm;3cm.  Each number represents each column's width.  As you can see, columns 1 and 3 (ItemId and CategoryId) have been set to 0, thereby hiding them from the list.  This leaves the ItemName and CategoryName width set at 3cm's each. It is interesting to note that because ItemName is the first visible column, it is the value of this field which is ultimately displayed in the text section of the control (even though the Combo Box is actually bound to the ItemId field). This means the displayed data is more meaningful to the user whilst ensuring the control is bound to a unique field value. 
4. If you want the list to display column headings, set the COLUMN HEAD property to YES.  If you do, you may want to adjust the underlying query so that the column headings are displayed as Item Name and Category Name rather than ItemName and CategoryName.  You do this by altering the field row on the Query Builder as follows: Item Name: ItemName.  This substitutes the actual field name (appearing after the colon) for an alias (appearing before the colon).  You will of course have to go back and re-open the Query Builder from the ROW SOURCE property cell to do this.
5. Change the LIST WIDTH property to 6cm.  This is the total of the width of the Item Name (3cm and Category Name (3cm) that we set in step 3.  Our list will now be wider than the width of the actual Combo Box Control itself.
6. Now let's give our combo box a more meaningful name.  Select the OTHER TAB of the PROPERTIES SHEET and set the name property to ctlItem.
7. Finally let's set the CAPTION property of the Combo Box label.  Click the label in the FORM DESIGN GRID to select it.  Then click the FORMAT TAB of the PROPERTY SHEET, and change the CAPTION property to Item.

Our Customized Combo Box is now complete.  If you open your form it should look like the Combo Box in Figure 1 above.  

There is more to Combo Boxes than what we have covered in this exercise.  You can, for example, use an unbound Combo Box to Search for a Record, something I have covered in a previous post.  There are also different ways of dealing with values not already stored in the combo box list.  This is something I hope to cover in a future post.