Bug?

  • Thread starter Thread starter tomocb
  • Start date Start date
T

tomocb

Ole DB error when selecting from multiple tables in stored procedure for
subform record source where foreign key in unique table is part of
concatenated key in foreign table.

I think this is a bug because as soon as you only have fields from the
unique table in the select stament of the stored procedure the subform works
fine.

What do you think?
 
Maybe it's a bug but at this moment, you didn't provide enough information
to reproduce or understand what you are doing exactly at this moment.

--
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)
 
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.
 
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)
 
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?
 
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.
 
Back
Top