Combo Box

[Don]

I'm trying to use the Combo Box function in a form so that data entry can be
done by selecting from a list of optional entries. When I use the form to
enter data, rather than inserting the option that I select, it enters the
value of the corresponding ID field. Is this function really that limited,
or is there a trick to it that I'm missing?

 

[Armen Stein]

I'm trying to use the Combo Box function in a form so that data entry can be
done by selecting from a list of optional entries. When I use the form to
enter data, rather than inserting the option that I select, it enters the
value of the corresponding ID field. Is this function really that limited,
or is there a trick to it that I'm missing?

The Combobox will display the first visible (non zero-width) in the
rowsource. But you can bind the value (usually the ID) to a field in
your main table for your form.

Try making the ID field have a width of zero, or if you need to see
it, make it a subsequent field instead of the first one.

The combobox wizard can help set this up for you.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[KenSheridan via AccessMonster.com]

It is the value of the ID field you want to insert into relevant foreign key
column in the table, not the corresponding text value, but on the form you
want to see the text value. Here's an example of how you'd set up a combo
box to do this for selecting an employee, so you'd see their name (a
concatenation of first and last name in this case), but the value inserted
into the column to which the combo box is bound is the hidden EmployeeID
value:

ControlSource: EmployeeID

RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM
tblEmployees ORDER BY LastName, FirstName;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

is there a trick to it that I'm missing?
Set the Column Widths property like this --- 0"; 1.5"
This sets the ID display to zero inches wide and the next column to 1.5
inches.

 

[Jerry Whittle]

Well technically you actually want the ID field. Then you link on it in a
query to return the values that you actually want to see.

However if you don't have it set to Limit to List, it doesn't work right
anyway.

Instead of basing the combo box on a table, create a query with the field
that you want first and use it. Another advantage is that you can sort the
values now.