Unfortunately, a combo box is built to have only one bound column. This
does not mean that you can't do what you want, but you have to do a lot of
monkeying around to make it work. Let me use a concrete example.
Suppose you had a table CustOrder and a Customer table. The Customer table
has a unique identifier of Firstname and Lastname. You want to use a combo
box to select the customer. With a single field primary key it would be
simple, but a multi-field primary key make it hard. Here's what you do:
First, in the Record Source of your form, you MUST have a calculated field
that is the concatenation of your other two fields -- say FullName: Lastname
& ", " & Firstname
Create a combobox on your form. Don't bother with the wizard. For the
RowSource use a query like this:
SELECT [Lastname] & ", " & [Firstname] AS FullName, FirstName, Lastname
FROM Customer;
Make the Column Count: 3. Make the Column Widths: 1";0";0" Make the Bound
Column: 1 Bind the combo box FullName (the calculated field in the Record
Source query of your form).
Create two text boxes and bind one to Firstname and the other to Lastname.
(You'll make these invisible later, but for now leave them visible)
Now you need to add some code to the AfterUpdate event of the combo:
Private Sub Combo2_AfterUpdate()
Me.FirstName = Combo2.Column(1)
Me.LastName = Combo2.Column(2)
End Sub
and also in the OnCurrent event:
Private Sub Form_Current()
Combo2 = LastName & ", " & FirstName
End Sub
That should be all that is necessary. It's a little kludgy, but it works.
If you need to see a working sample, email me at the address in the sig
(modified as indicated) and I can send you a copy.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
ConnieA said:
I have a connection to an external database (Oracle) through odbc. I have
set up a form with a field that contains a drop down box. The data in the
drop down box will come from a reference table on the Oracle table. The
unique identifier of a row in the reference table is a combination of 2
columns. When I create the drop down box using the wizard it tells me to
choose a field that uniquely identifies the row. The two fields that
uniquely identify the row are available for selection, but it only allows me
to select 1 column. Does anyone know how I can select 2 rows to uniquely
identify the column?
