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 an Integer. The 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 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 not visible to the user.

For example, to create the drop-down menu of customer names and e-mail addresses shown back in the Orders form (refer to Figure 10-12), I first created a query based on the Customers table. In that query, I 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: IIf(IsNull([LastName]),[Company],[LastName] & ", " & [FirstName])

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.

Figure 10-14:

Customer LookupQry query in Design and Datasheet

Figure 10-14:

Customer LookupQry query in Design and Datasheet

The second column in the query contains the calculated field:

EmailText: HyperLinkPart([EmailAddress],0)

In that example, EmailAddress refers to a Hyperlink field in the underlying Customers table. Hyperlink fields can look kind of weird in drop-down menus. 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 contains represents the CustID control from the Customers table, which is defined as a AutoNumber field in the table's design. The lower-right image in Figure 10-14 is the same Customer LookupQry in Datasheet view. Notice how the names are presented in the first column; the e-mail address in the third column; and the CustID value — an Integer — in the third column. Later, when you use that query as the dropdown menu for a combo box, you can make that third column the Bound Column while still showing the fancy SortName value in the control.

Say you create, close, and save a query like CustomerLookupQry. Now you want to use that query's columns as a drop-down menu for a combo box that allows the user to choose a customer by name and/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 Control Wizards button in the Toolbox.

Next, create the combo box as you normally would. For example, to create the CustID combo box on the Orders form, you'd click the Combo Box tool in the toolbox 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'll 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 actually be stored in the ComboBox control. In this case, you'd 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 and suggests CustID. I click Next, and the last wizard page asks for a label. I type Customers and then click Finish.

Figure 10-15:

Column-sizing page of the Combo Box Wizard.

Combo Box Wizard

How wide would you like the columns in your combo box?

To adjust the width of a column drag its right edge to the width you want; or double-dick the right edge of the column heading to get the best Fit.

SortName

EmailText

CustID

Abalasterj Igor

[email protected]

39

ABC Productions

[email protected]

7

Angstrom, Margare

[email protected]

5

Bebop, Stacey

[email protected] .con

34

Biasinij Carlos

[email protected] com

25

CitruSj Michael

[email protected])citrus.corr

24

Costello, Karen

[email protected]

23

Before sizing columns

After sizing columns

Combo Box Wizard

How wide would you like the columns in your combo box?

To adjust the width of a column, drag its right edge to the width you want, or double right edge of the column heading to get the best fit,

SortName

EmailText

Abalaster, Igor

[email protected]

ABC Productions

[email protected]

Angstrom, Margaret

[email protected]

Bebop, Stacey

[email protected] .com

Biasinij Carlos

[email protected] com

CitruSj Michael

[email protected])citrus.com

Costello, Karen

[email protected]

The ComboBox control is now on the form. Figure 10-16 shows the results with the Combo Box drop-down menu visible. You can also see the Properties 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 menu actually contains three columns. The Column Widths are 1.4", 1.6", and 0", meaning the third column is invisible (zero inches wide). The Bound Column property (3) tells you that whatever is in that third column is what actually gets stored in the CustID control that the drop-down menu is attached to.

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

The most important thing to glean from all of this is that what you see in a ComboBox control isn't always what Access and VBA see. What's actually stored in the combo box will be whatever is defined as the Combo Box's Bound column. What you'll see in the control is whatever is in the first column of the drop-down menu.

CustID control

Figure 10-16:

CustID control using Customer Lookup Qry's columns.

eI MOM ;:003 Ordere

Orders Form

Method: |pl

Credit Card

-jpJB

Costello, Karen Creative Designs Crushes Kimerbly Doerr, Monica Escovedo, George Gadgets Inc. HarkinSj Tiffany [email protected] lo.co m [email protected] eativedesigns.com | i I [email protected] [email protected] geor [email protected] vedo, co m [email protected] inc. co i [email protected]

Higglebottorrij Hortiensf frank [email protected], com

CustID properties jj Combo Box: CustID_

Format Data Event Other All

"Name ICustlD

Control 5ource ¡CustID

Format

Decimal Places Auto_

Input Mask P_

Row Source Type Table/Query

Row Source CustomerLookupQry

Column Count 3

Column Heads ¡No

Column Widths 1.4"jl.6";0"

Bound Column ¡3

List Rows 18

List Width ¡3"

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

0 0

Post a comment