More Combo Box Tricks

In this section, we show you a few more combo box tricks, starting with an explanation of why what you see in a combo box isn't always what you get in VBA. For example, the CustID control on the Orders form shown in Figure 10-12 is bound to a Long Integer field in its underlying table, yet its combo box shows a bunch of names and addresses. How can that be?

Using hidden values in combo and list boxes

A combo box or list box can show any data from a table or query even though the control contains some simple value like a Long Integer. The long integer, which is usually a primary key value, can be hidden in the control as the control's actual value while some more meaningful (to humans) text is shown to the user. This disappearing-value act works thanks to multicolumn lists and the Bound Column property. Here, in a nutshell, is how it works:

1 Whatever is in the first visible column of the list is what shows (visibly) in the control.

1 Whatever value is defined as the Bound Column is the value that's actually stored in the control, although it might not be visible to the user.

For example, to create the drop-down list of customer names and e-mail addresses shown back in the Orders form (refer to Figure 10-12), we first created a query based on the Customers table. In that query, we used some fancy expressions to display the name and e-mail address of each customer in the list. The first column in the query, as shown in Figure 10-14, contains the calculated field (which is too wide to show completely in the figure):

SortName: llf(lsNull([LastName]),[Company],[LastName] & ", " & [FirstName])

íoitNama nm;Nulli[La;tNaina]HCMEipany],[La;tNama] a

Figure 10-14:

Customer Lookup Qry query in Design and Datasheet views.

íoitNama nm;Nulli[La;tNaina]HCMEipany],[La;tNama] a

Hp CustomerLookupQry

. n



t EmailText

t | CustID t



[email protected]


Angstrom, Margaret


Bebop, Stacey

[email protected]


Biasini, Carlos

[email protected]


Citrus, Michael

[email protected]


Costello, Karen

[email protected]


Creative Designs

[email protected]


Crusher, Kimerbly

[email protected]


Do err, Monica

[email protected]


Escovedo, George

[email protected]


Gadgets Inc.

[email protected]


Hark ins. Tiffany

[email protected]


Higglebottom, Hortense

[email protected]


| Record! H 1 of 35 ► M *

ter Search


The preceding expression says, "If the Last Name field in this record is null (empty), just show the company name. Otherwise, show the person's LastName followed by a comma and a space and then the FirstName."

The second column in the query contains the calculated field:

EmailText: HyperLinkPart([EmailAddress],0)

In this example, EmailAddress refers to a Hyperlink field in the underlying Customers table. Hyperlink fields can look weird in drop-down lists. The HyperLinkPart() function there isolates just the display portion of the field. That basically ensures that the e-mail address looks like an e-mail address in the query results.

The third column in the CustomerLookupQry represents the CustID control from the Customers table, which is defined as an AutoNumber field in the table's design. The lower-right window in Figure 10-14 is the same CustomerLookupQry in Datasheet view. Notice how the names are presented in the first column; the e-mail address in the second column; and the CustID value — a Long Integer — in the third column. Later, when you use that query as the drop-down list for a combo box, you can make that third column the Bound Column while still showing the fancy SortName value in the control.

If you create, close, and save a query like CustomerLookupQry, you might want to use that query's columns as a drop-down list for a combo box that allows the user to choose a customer by name or e-mail address. To get started, you need a form open in Design view. Optionally, you can turn on the Control Wizards by clicking the Use Control Wizards button in the Controls group on the (Form Design Tools) Design tab.

Next, create the combo box as you normally would. For example, to create the CustID combo box on the Orders form, you click the Combo Box tool in the Controls group and then drag the CustID control from the Orders table's Field List onto the form. (Dragging the CustID control to the form after you click the Combo Box tool binds the new combo box to the CustID control.)

When the Combo Box Wizard starts, just follow its instructions to design the combo box. For example, tell it to get its values from the CustomerLookupQry described earlier. When it asks which fields from that query to display, choose all three field names. When you get to the wizard page where you set column widths, you initially see all the columns from the query, as in the top-left side of Figure 10-15. To hide the CustID number from the user, narrow its column to the point that it's not visible. Set the widths of the other two columns to whatever fits best, as in the lower-right portion of that same figure.

The next page of the wizard asks which field from the query should be stored in the ComboBox control. In this case, you choose CustID because you want to store the selected customer's CustID value (not the name or e-mail address) in the CustID field of the Orders form. The last wizard page asks which field should store that value; choose CustID. We clicked Next, and the last wizard page asked for a label. We typed Customers and then clicked Finish.

Figure 10-15:

Before sizing columns (top) and after sizing columns (bottom) in the Combo Box Wizard.

Figure 10-15:

Before sizing columns (top) and after sizing columns (bottom) in the Combo Box Wizard.

The ComboBox control is now on the form. Figure 10-16 shows the results with the combo box drop-down list visible. You can also see the property sheet there, and that's where you can see what's really going on. For example, the Column Count property shows that the drop-down list contains three columns. The column widths are 1.5", 2.5", and 0", which makes the third column invisible (zero inches wide). The Bound Column property (3) tells you that whatever is in that third column is what gets stored in the CustID control that the drop-down list is attached to.

Figure 10-16:

CustID control using

Customer-Lookup-Qry's columns.

Figure 10-16:

CustID control using

Customer-Lookup-Qry's columns.

Because a combo box always shows whatever is in the first visible column of the drop-down list, only the selected person's name appears in the combo box after the user makes a selection because that SortName control is the first visible column in the CustomerLookupQry query. The only purpose of the e-mail column in that query is to act as a tiebreaker. For example, if two customers happen to have the same first and last names, the user can tell which is which by the e-mail address.

The most important thing to glean from all this is that what you see in a ComboBox control isn't always what Access and VBA see. What's stored in the combo box is whatever is defined as the combo box's Bound Column property. What you see in the control is whatever is in the first visible column of the drop-down list.

If you add an unbound text box control to your form that contains an expression like = fieldname .Value as its control source (where fieldname is the name of a ComboBox or ListBox control), that control shows you the true value of the fieldname control as opposed to what appears in the control.

Was this article helpful?

0 -1


  • niko
    Can we create combi box with 2 column in access with vba?
    8 years ago

Post a comment