Forms - Select a Name but Write ID Number

[MarleneC]

Sorry if this answer already exists in the forum somewhere but I can't even
figure out what to search for. I have a table that links adult mentors to
kids by storing their ID numbers in KidID and AdultID fields. I need to
create a form to go with it, so that we can update these realtionships. How
do I let someone select the Name of a child or adult on the form and then
write their ID number to the table? Thanks for your help!

 

[Douglas J Steele]

Easiest approach would be to use a combobox.

Create a combobox that uses a row source that returns, say, AdultID and
AdultName. Set the Column Count to 2, and the Column Widths to 0" (or, if
you prefer, 0";2", or whatever width you want for the AdultName). Set the
Bound Column to 1 (to correspond to the AdultID field). Set the Control
Source for the combobox to the field in the form's recordset that contains
the AdultID.

Do the same for a second combobox that gives the Kid information.

 

[MarleneC]

THanks for your help Doug! That sort of works. What if AdultName is in
another table? (The relationship table is just AdultID and KidID) How do I
use multiple columns in a combo box? Do I need to create the form from a
query?

 

[Douglas J Steele]

The RowSource of your combobox should be the table that lists all of the
Adults, not the table that has AdultID and KidID. It would be bound to the
form's recordset, though, and that would be based on the table that only has
AdultID and KidID.

 

[MarleneC]

Almost there! So I did a query in rowsource that finds AdultID,
AdultFirstname and AdultLastName. Bound column is 1. It displays the
AdultFirstname and AdultLastName in the drop down list, but it only shows the
first name in the form field. How do I make it display firstname and
Lastname?

 

[Douglas J. Steele]

You can't.

What you can do, though, is create a query that concatenates the first and
last names into a single field for display purposes, and use that query as
the rowsource for your combobox:

SELECT AdultID, AdultLastName & ", " & AdultFirstName FROM MyTable ORDER BY
AdultFirstName, AdultLastName