multi-table select stored procedure subform ole eb error

As soon as I add a field to the select statement of my subform's stored
procedure record source from a foreign table in the join the subform presents
"multiple-step ole db operation generated errors..." when I try to insert a
new record in the subform.

I have specified the unique table, I have the input parameters specified and
the resync command works perfectly.

If I only include fields from the unique table in the select of the stored
procedure there isn't a problem. I'm using access 2007, adp, sp2, WinXP.

I cannot for the life of me work out what is wrong with this. Please help.

Found the problem. It is because the foreign key that I'm updating in the
subform is part of a concatenated key in its foreign table. The reason it is
part of concat key is because I need to make it unique against the primary
key of the main form.

I wasn't previously setting the foreign key value programattically, but now
I'm doing so in the BeforeInsert event and it has fixed the problem, even
though Access automatically populates this value because it recognises as
part of the link child/master records.

It must be that it was populating it later than my new BeforeInsert sub
routine and consequently produced the error, which makes sense as this error
commonly relates to NULL values being parsed to tables when they should not.


"tomocb" wrote:

As soon as I add a field to the select statement of my subform's stored
procedure record source from a foreign table in the join the subform presents
"multiple-step ole db operation generated errors..." when I try to insert a
new record in the subform.

I have specified the unique table, I have the input parameters specified and
the resync command works perfectly.

If I only include fields from the unique table in the select of the stored
procedure there isn't a problem. I'm using access 2007, adp, sp2, WinXP.

I cannot for the life of me work out what is wrong with this. Please help.

Now that I have more time to test this, I'm wondering if this is actually a
bug in Access?

The error message will always appear if you select a columns from multiple
tables in a stored procedure where the join is a foreign key linked to a
concatenated primary key.

Could someone confirm, please?

"tomocb" wrote:

Found the problem. It is because the foreign key that I'm updating in the
subform is part of a concatenated key in its foreign table. The reason it is
part of concat key is because I need to make it unique against the primary
key of the main form.

I wasn't previously setting the foreign key value programattically, but now
I'm doing so in the BeforeInsert event and it has fixed the problem, even
though Access automatically populates this value because it recognises as
part of the link child/master records.

It must be that it was populating it later than my new BeforeInsert sub
routine and consequently produced the error, which makes sense as this error
commonly relates to NULL values being parsed to tables when they should not.


"tomocb" wrote:

As soon as I add a field to the select statement of my subform's stored
procedure record source from a foreign table in the join the subform presents
"multiple-step ole db operation generated errors..." when I try to insert a
new record in the subform.

I have specified the unique table, I have the input parameters specified and
the resync command works perfectly.

If I only include fields from the unique table in the select of the stored
procedure there isn't a problem. I'm using access 2007, adp, sp2, WinXP.

I cannot for the life of me work out what is wrong with this. Please help.