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.|
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.
- Open the Combo Box Exercise database you downloaded.
- 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.
- 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.
- 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
- Highlight the Combo Box Control and then click the PROPERTY SHEET icon in the TOOLS GROUP of the DESIGN RIBBON.
- Select the DATA TAB of the PROPERTY SHEETwhen it opens.
- 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
- 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.
- Select tblProducts and tblCategory from the SHOW TABLE dialogue box. You can do this by double clicking each name in the dialogue box.
- Click CLOSE on the SHOW TABLE dialogue box.
- 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.
- Then select categoryId and categoryName from tblProducts.
- Enter =1 in the CRITERIA row of the categoryId column.
- Then click the CLOSE icon in the CLOSE GROUP of the DESIGN RIBBON.
- 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.|
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).
- Select the FORMAT TAB of the PROPERTY SHEET.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.