Displayed value in combo box

[Guest]

I am putting together a database of student information. Each student plays
a particular type of instrument. The different types of instruments are
stored in a table like so:

Instrument Code Instrument Name
01 Trumpet
02 Bassoon
03 Piano
04 Flute

There is also a Student Information table where I have the student's name,
age, etc. These two tables are related by the Instrument Code. On a form, I
would like a combo box to display the Instrument Name, then store the
Instrument Code in the Student Information table. Is there an easy way to do
this?

 

[Penguin]

Try using the combo box wizard.

 

[Nikos Yannacopoulos]

Yes, there is. Get to the combo's properties in form design view, select
tab Data, put the cursor next to RowSource and click on the little
button on the right to invoke the query builder; in the query design
view, select the instruments table and drag down to the grid the code
and name fields (in this order!), the close / confirm to save the SQL
statement and get back to the form design. The Bound Column property
should read 1, if not set it to 1. Now select the Format tab, and set
the Column Count Property to 2 if different, then set the Column Widths
property to 0;2 (or 0;whatever, as long as the first number is 0). This
is it; what you have done is make the combo retrieve its rows from a two
column query, return the value of the first one (Bound Column = 1) and
display only the second (Width for first column = 0).

HTH,
Nikos