I will encourage you to work in forms, not directly in the tables, and to
use queries to 'feed' your comboboxes. That way, you can create a simple
combobox on your form that is labeled, for example, "Professor". The field
in the underlying table (i.e., behind the form) to which that combobox is
bound is a foreign key field that points to the rowID of the professor
selected from the professor table.
But what the combobox displays (by setting the width of the first column,
the ID, to 0) is the "second" field in the query. If you use, from your
Professor table, something like: [LastName] & ", " & [FirstName] (assuming
those are the names you've put on the fields), the your combobox will be
displaying the LastName, FirstName of the selected professor.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hi Jeff,
Thanks for your input. Let me see if I understand you correctly.
Perhaps I should explain more.
I have Professors and Courses and Sections. Each Course can have
multiple Sections. Each Professor can teach a number of Sections (or
none).
So in the table tblSections there are fields for fk_Course and
fk_Prof. I'm trying to create a form that will allow me to review
which professor is teaching which section and perhaps assign/change
professors.
If I understand you correctly, I should create a query qryProfs that
has a field where LastName and FirstName are concatenated and where
the ProfID key is also a field. Then I should store this value (which
is the same key as the ProfID key in tblProfs in the tblSections. Have
I got it right? Easy enough. Thanks.
I was wondering if I could do it without having to construct the query
for the sole purpose of concatenating the fields.
Mike
If I'm reading between the lines correctly, you are attempting to do this
directly in a table. STOP! Step away from the keyboard!
Even though MS Access offers a "lookup" data type, it seems to cause more
confusion than it solves. The lookup data type displays the looked-up
value
(one field only), but stores the primary key of the row. Thus, for
example,
when you try to create a query and use one of the looked-up values as the
criterion, you get NOTHING! That's because the field actually holds the
IDs.
The preferred approach is to store only the ID in the table, then use a
query to do the kind of concatenation you mentioned ([FirstName] & " " &
[LastName]), then display that in a combobox on a form.
Access tables may look like a spreadsheet, but tables are merely "buckets
o'
data". Access forms have a very rich event environment, so you can take
much stronger control over what is displayed and how.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a combobox that I do a lookup in another table for.
The key is fk_Player. The combobox should display Firstname &
Lastname
What I get is that it displays two columns of first name and last name
(one each in a colums. After the selection is made, the combobox
displays only the first column (firstname). I would like to know how
to construct the combobox so it will display a person's full name.
I think I have done it in the past by creating a query where one field
combines the firstname and lastname fields. Is that the only way or
can I use the table data directly?
