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.
"tomocb" <(E-Mail Removed)> wrote in message
news:44180EE2-A61C-44AB-9B44-(E-Mail Removed)...
> I see. How do I ensure uniqueness between these two values then if I
> can't
> enforce it through table integrity? Should I be doing this
> programatically?
>
> "Sylvain Lafontaine" wrote:
>
>> Perfectly normal. You cannot change the primary key of the current
>> record
>> that you are editing because if you change it, it's no longer constant
>> over
>> the editing process (while using a record) and therefore, it's not a
>> primary
>> key because a PK must remain constant over this process.
>>
>> Add an independant primary key and you should be OK. As a general
>> recipe,
>> stopping using composite keys not only for this but everywhere in a
>> database
>> is not a bad idea.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "tomocb" <(E-Mail Removed)> wrote in message
>> news:48D260D7-9684-485B-87A5-(E-Mail Removed)...
>> > Ok. Will try and elaborate.
>> >
>> > I have a datasheet subform in Access 2007 which has a stored procedure
>> > as
>> > the record source. The unique identifier for each row is a combination
>> > of
>> > two Int fields. However, one of these fields is also a Primary key in
>> > its
>> > foreign table.
>> >
>> > If I select a field from the foreign table for inclusion in the subform
>> > datasheet I get the aforementioned error. If I do not include it in
>> > the
>> > select statement of the store procedure I get no error.
>> >
>> > This causes me a problem, because I need to display data from the
>> > foreign
>> > table.
