Using a combo box for setting a name

[Ian Melnick]

Hello all,

I have two tables, each with primary key "id". Table1 has fields "First
Name" and "Last Name", while Table2 has field "user", which is a number.
I want to set up a combo box in a form for Table2 that will correctly
set the "user" from Table2 to the "id" from Table1. It also needs to
display the first name and last name (by referring to the stored user
field) correctly when the record is accessed.

In other words, it needs to list all first and last name info from
Table1, and when a name is selected, store it in Table2 as the id from
Table1. When the record is accessed by the form, it needs to correctly
display the name as defined by the id stored in the "user" column. How
do I do this?

Thanks

 

[tina]

your form should be bound to Table2. the combo box on the form should be
bound to the User field from Table2. set the combo box's properties as
follows:

RowSourceType: Table/Query
RowSource: SELECT ID, FirstName & " " & LastName As FullName FROM Table1
ORDER BY FullName;
ColumnCount: 2
ColumnWidths: 0"; 1.25"
BoundColumn: 1
ListWidth: 1.5"

the combo box's droplist will show first and last names. when you choose a
name, the value stored in the User field will be the ID that goes with that
name.

hth

 

[Ian Melnick]

Thanks; is there a way to have it convert the stored ID number to the
FullName when the record is returned to after input? Right now I'm
seeing the list of names in the combo box, with the set value being the
ID number that was set previously---what I'd like to be able to do is
see the name instead.

Thanks again

 

[tina]

you do want the ID value to be stored in the field, rather than the name,
because the ID value is the primary key in Table1. if you set the combo box
up the way i described, you *will* see the name displayed in the combobox
control of each record in the form, rather than stored ID value. if you look
at the table itself, you will see the ID value, which is correct. the end
user should never open a table; all data entry and review should be done via
forms and reports.

if you're seeing the ID value in the combo box control of the form, rather
than the name, then your combo box setup is incorrect. look at the list of
combo box properties i posted, and post the actual value of those properties
from your combo box.

hth

 

[Ian Melnick]

Thanks; this worked. I didn't have the object bound to the proper
control source, and once I set it up correctly, your directions worked
great.

Thanks again
ian

 

[tina]

you're welcome

Thanks; this worked. I didn't have the object bound to the proper
control source, and once I set it up correctly, your directions worked
great.

Thanks again
ian