Error 3201 - Referential Error Problem

[Miles]

I am getting a referential error problem that is confounding me. There is
something obvious here that I am missing. I am using Access 2003. The form
that I developed is based on a query with 3 tables, as shown (in simplified
form):

1. tblPersonMain
PersonID - PK (autonumber)
Gender - text

2 tblPersonFamily
PersonFamilyID - PK (autonumber)
PersonID - FK (required, no duplicates)
Notes on Family - memo

3. tblFirstNames
FirstNameID - PK (autonumber)
PersonFamilyID - FK (required, duplicates ok)
PersonFirstName - text

The link is 1 to 1 between tblPersonMain and tblPersonFamily
The link is 1 to many between tblPersonFamily and tblFirstNames

If I set the form's RecordsetType property to Dynaset I can't update the
data in the fields from tables 2 and 3, so I set the RecordsetType property
to Dynaset (inconsistent updates).

On the joins between tables 1&2 and between 2&3 I have ticked referential
integrity with Cascade Updates and Cascade Deletes.

The database is empty. When I try to save the record, I get error 3201 "You
cannot add or change a record because a related record is required in table
'tblPersonMain' ".

It seems that no PK is being populated in tblPersonMain and then being
picked up by tblePersonFamily as a FK.

What can I do to make this work? I don't want to redesign my form to
include subforms (assuming that would even work) and I don't want to untick
referential integrity on the joins between the tables (which seems to work,
but will it mess up my data later?).

Any help would be appreciated.


Miles.

 

[NetworkTrade]

un-tick referential integrity on both links for now....

 

[NetworkTrade]

un-tick the referential integrity for both relationship links

difficult to explain - but you have a catch-22 box with no data in the
tables....

 

[Tim Ferguson]

It seems that no PK is being populated in tblPersonMain and then being
picked up by tblePersonFamily as a FK.

What can I do to make this work?

You need to make sure that tblPersonMain.PersonID is _in_ the query, in
order to allow creation of the TPM record.

You probably also need to have to tblPersonFamilyID.PersonID in it too.
Access _might_ be intelligent enough to insert the FK value, but I doubt
it.

Similar logic applies to the other PK/FKs.

In any case, ISTMT the whole design seems hopelessly flawed. This
description:

tblPersonFamily.PersonID - FK (required, no duplicates)

is a waste of time. Since PersonID is guaranteed unique across the entire
table, you may as well make it the PK and save a whole load of trouble.
While you are there, you may as well put the only information-containing
field into the TPM table and have done with it.

Etc.


Hope that helps


Tim F

 

[Miles]

NTC and Tim F:

Thank you for comments. First a note on the structure of the tables. I
know they look funny, but I have my reasons for doing it this way (a couple
of reasons, for example, to password protect the information stored in
tblPersonFamily and to have the ability to list multiple first names, like
nicknames in tblFirstNames. Also, I have a lot of tables so I have to reduce
the number of connections to tblPersonMain).

Anyways, I tried both methods and I still seem to be in a catch-22. When I
unticked referential integrity it worked temporarily, but when I closed the
form and went back into the form, the records were not showing. I manually
updated the FKs in the tables and was then able to retick referential
integrity between the tables. However, once again, I could not add new
records.

I also tried adding all the PK-FKs into the query. Same problem. It would
not allow me to save new records.

One thing I did was to add text boxes to track the value of the two PKs
tblPersonMain.PersonID and tblPersonFamily.PersonFamilyID I noticed
something unusual. When the form became dirty on a new record, the
tblPersonFamily.PersonFamilyID autonumber incremented to the next digit but
the tblPersonMain.PersonID field went blank.

Two thoughts occurred to me:
1. Do not use an autonumber for the main table PK (tblPersonMain.PersonID),
but create my own incremental value where I can control the timing of the
updating of the PK value.

2. Somehow force the tblPersonMain.PersonID autonumber to update (ie
increment by one) when I want it to.

Any thoughts?

(by the way Tim, what does ISTMT mean?)


Many thanks in advance.
Miles.

 

[Miles]

I should also mention that I have no idea how to force autonumber PK to
increment at the start of a new record....


Miles.

 

[Miles]

This problem has been bugging me all night, but I think I found my problem.
I put the Form Recordset Type back to Dynaset (instead of dynaset
inconsistent updates) and it works!

So, to summarize:
- in query include all PK and FK combinations
- enforce referential integrity
- Form RecordsetType to Dynaset

Thanks a lot for your help!!

 

[Tim Ferguson]

So, to summarize:
- in query include all PK and FK combinations
- enforce referential integrity
- Form RecordsetType to Dynaset

Glad it worked for you.

B Wishes


Tim F