How to show fields from a second table

[Guest]

I have created 2 tables, one for music titles and one for composers. The
composer primary key appears in the titles table, and I have created the
1-many relationship between the tables.

I wish to create a form for entering new titles that will allow me to select
from a list of first-last names derived from the composers table.

When I select one of the names it will place the composer ID in the field in
the titles table, and show the first-last name in a box on the form.

 

[Al Campagna]

Tom,
Create a 2 column combo (ex. cboComposer), and make the ControlSource the ComposerID of
your form table.
Concatenate the Last/First Name fields from tblComposers to create the second column.
Col 1 Col2
ComposerID Last/FirstName

Set up the combo as...
No of Columns =2
ColWidths = 0"; 1.75"
ListWidth = 1.75"

The combo will display just the Composer Names, the user selects a Composer Name, and
after selection, that same name will stay displayed... but really... the ComposerID will
be stored in the ComposerID field of your table.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Thank you very much Al! I get what you mean, except for how/where to express
the concatenation of the name fields.

As it stands right now, the Row Source property says:
SELECT Composers.ComposerID, Composers.ComposerFirstName,
Composers.ComposerLastName FROM Composers ORDER BY [ComposerLastName];

The combo box has 3 columns, showing the composer first and last names.

It works as desired, but does not display the concatenated names after
selection. Is the concatentation specified in the Row Source property, or
somewhere else?

 

[John W. Vinson]

It works as desired, but does not display the concatenated names after
selection. Is the concatentation specified in the Row Source property, or
somewhere else?

Yes, as a calculated field:

SELECT Composers.ComposerID,
Composers.ComposerFirstName & " " & Composers.ComposerLastName
AS ComposerName
FROM Composers ORDER BY [ComposerLastName], [ComposerFirstName];

This will show "Frank Bridge" and "Franz Liszt"; if you wish you can
put the last name first and use ", " instead of the blank in between.

John W. Vinson [MVP]