Mischien heb je hier wat aan:
Referring to Columns in a Combo Box (015)
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 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 tip001 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.
Bij mij werkte het, moet alleen een button toevoegen om de form data te resetten. Als je nu voor custumerID Cisco invult komt dit ook in je overall table terecht.
Succes.
