DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Referring to Columns in a Combo Box

First, some background. What follows is a standard use of a combo box on an Order Form form.

Table A - Customers
- customer ID
- customer name
- customer address
- etc, etc

Table B - Orders
- order ID
- customer ID
- order date
- etc, etc

There is, of course, a one-to-many relationship from the Customers table to the Orders table.

To allow our staff to choose the correct customer for the order, on the Orders form we put a combo box bound to the customer ID in the Orders table. The row source for the combo box, though, comes from the Customers table. Here are some typical properties for the combo box.

Name cboCustomer
Control Source CustomerID
Row Source SELECT [Customers].CustomerID, [Customers].CustomerName FROM [Customers]  ORDER BY [Customers].CustomerID;
Column Count 2
Column Widths 0";2"
Bound Column 1

Remember, the combo box is bound to the CustomerID in the Orders table. That is a big key to having this work.

Now, when the person doing the data entry clicks the drop down of the combo box, they will see the customer names, but not their codes. If you want to see the codes, change the column widths to 0.5"; 2" or similar. Also, a benefit of combo boxes is that if you know the customer's correct name, just start typing and it will auto expand to show the whole name. This is a keyboard alternative to using the drop down.

Back to the original topic of this tip. If you have read Query Parameters then you know that you can use the value in this combo box as a parameter in a query. Since the query normally uses the CustomerID to do the criteria, we use  [Forms]![frmOrders]![cboCustomer]  to get the data for the chosen customer. The value passed back is the Bound Column, listed as 1 (first column) in the properties. This is the CustomerID.

What if you want to refer to the customer's name? Then, use this style.
            [Forms]![frmOrders]![cboCustomer].column(1)

Whoa. Hey Manxman, I thought we determined that the first column is the CustomerID! I want the name this time.

Well, that's correct. When you use .column(n), Access starts with (0). So the CustomerID is the bound column, which in the properties box is "1". But Access considers that .column(0), and therefore, the CustomerName is the 2nd column, or, .column(1).

I know, clear as mud.

This is a typical use of a combo box. You will have many of these basic one-to-many relationships in your use of Access, and each one lends itself nicely to this method.

(article continues after sponsor spot)



 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy