The question of composite primary keys has been debated at length, and I
think the only reasonable conclusion is that some people like them and some
don't. There is nothing right or wrong about them. A composite PK is a good
way to implement a uniqueness requirement. As the number of attributes
making up the PK increases, the structure may become increasingly awkward
and a surrogate PK (identity integer, e.g.) may become a better choice.
You can change the primary key of an edited record, with or without a
composite PK. How else could you ever make changes? If I have a Book table
(bookID), a Person table (personID), and an Author table (bookID, authorID),
I can edit either the book or the person in the author table. For an update,
Access would use the original data values for the where clause of the SQL
update statement.
I think the issue is in the original poster's statement: "If I select a
field from the foreign table for inclusion in the subform datasheet I get
the aforementioned error."
There are two simple alternatives for displaying data from the foreign key's
parent table without including the attributes in the form's record source:
a) Use a combo box for the FK attribute and display whatever you want in the
combo box, while binding to the FK attribute. This is what I usually do. You
can set the displayed column to include any data you want. So if you were
selecting a personID from a Person table, you can use a combobox rowsource
like:
Select personID, lastName + ', ' + firstName as Display From Person
Order By lastname, firstname;
Set the column count to 2, the column widths to 0";2" and the bound column
to 1. The user only sees the name, not the id, but the id is the stored
data.
or b) Use a subform, which can be readonly if you don't want to allow parent
table edits. If this is happening in a subform, the subform would have to be
in continuous forms mode instead of datasheet mode to see the parent table
subform.
Either of those approaches should solve your problem.
