Using a Combo Box to find a record in one table when the searched field resides in another table.

[Carmine]

Example: Searching for book records in a small library.
Master Record Table contains book title, ISBN, author name, publisher. A Book Title Combo Box works fine if the book title resides in the Master Record Table, as expected the title appears in the combo box and when a selection is made the appropriate book record is displayed. Note that the combo box is built on a form which is tied to the Master Record.
HOWEVER, the above does not work if the book titles are NOT stored in the Master Record, but in a separate Book Title Table. Hence, when I open the combo box it displays the Book Title Table primary key (auto number) for the title and not the actual title.



Given that the actual title is stored in the Book Title Table, how do I get the combo box to display the title and not the primary key for that title?

[June7]

Why is there a second table for book titles? Book title should be in only one table. Not understanding data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[Carmine]

Ok, let me explain how I set this up. The user will input data by opening the Master Record Form containing the following fields: Book Title, ISBN, Author Name, and Publisher. The Book Title, Author Name, and Publisher fields are combo boxes each with the “edit list” option and each is attached to its respective table, Book Title, Author Name, and Publisher. So the actual Book Title, Author Name, and Publisher Name only appear the Book Title, Author Name, and Publisher Name tables.
The Master Record Form Table contains fields for Book Title, ISBN, Author Name, and Publisher Name. With the exception of ISBN the fields contain only primary key values related to each entry in each table.

MastrRec	BookTitle	ISBN	AuthrNam	PublNam
03	01	23465789	02	03
04	03	12765543	14	05
05	06	90845532	11	04

Book Title only appears once and only in the Book Title Table.

[June7]

If you want a list of all titles then use BookTitle table as source for combobox. This would be a bound combobox for creating new record in Master table.

If you want a list of only titles in Master table to use as a search tool, this would be an UNBOUND combobox with a RowSource that is a query joining tables.