Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo
box functionality. My actual tables truly need the multiple key fields, but
they would have only presented unnecessary confusion if I had described them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place the
separate values in the table fields. This is a nice idea that would halve
the effort on the user's part in doing data entry for these fields. This is
not the problem that I thought I was trying to solve, but you have changed my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box lookups
on fields that are not in the first column of the parent table.



"Jeff Boyce" wrote:

The natural vs. arbitrary key discussion can devolve into religious wars, so
let's not go there!

I'm curious why you would want to select LastName and FirstName separately
for your Job History. Aren't you actually collecting information about the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks having
the same name (and even living in the same house). You might be operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to expand
the number of fields you use to uniquely identify an individual? (this is
one of the situations in which an "unnatural" key may have fewer problems in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


"AccessMan" wrote in message
...
Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and last
names).

As an example, assume I want a table that documents a person's job history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name), but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be able
to
use a combo box that pulls down available values from the [Person] table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person Job
History], specifically, the second column position of the LastName field
in
the [Person] table. The combo box for the FirstName field uses the first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully populate
[Person Job History], but the values that appear in this field are first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the last
name in the first column, and to set this query as the Row Source for the
combo box for LastName in [Person Job History]. This seems silly to have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

"Jeff Boyce" wrote:

I'm not sure I'm following your description (but maybe I'm being too
literal
g)...

A table would only have one PK, although you could create a multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be any
duplicates.

If you are describing the use of Lookup data types in your tables, please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a few
'rows' of data as an example, so we can get some idea what domain you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"AccessMan" wrote in message
...
I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1 as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way. If I
set
Bound Column and Column Count to 2 I see both columns in the combo box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values, and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem right
that I
should have to do this, so I suspect that I am missing something quite
obvious.