Referential Integrity and Forms

[Musa]

I have a one to one relationship with 6 tables .
Tbl_1
Survey_ID PK
Var1
Var2 etc...
Tbl_2
Survey_ID PK
Var etc..
Tbl_3
Survey_ID PK
Var etc.
.....Tbl6

I know this is not a preferred design but this data is a flat file used for
survey records. Also, there are too many variables to combine into one form.
Each table has a Unique identifier "Survey_ID". There are no other fields to
relate to the other tables besides this "Survey_ID". My problem is enforcing
referential integrity. I designed the forms with a one-to-one relationship
based on Tbl1. When I try to enter data, I get "You cannot add or change a
record because a related record is required in the first tbl", when I attempt
to move from one form to another. The Survey_ID should equal the input from
the first tbl (which it does) but that is where the connection ends. Could
you help me figure out how to enforce refential integrity, while carrying the
value from the first table over to the other 5 ( and have the records save
on entry ) ?
Thanks

 

[John W. Vinson]

I have a one to one relationship with 6 tables .
Tbl_1
Survey_ID PK
Var1
Var2 etc...
Tbl_2
Survey_ID PK
Var etc..
Tbl_3
Survey_ID PK
Var etc.
....Tbl6

I know this is not a preferred design but this data is a flat file used for
survey records. Also, there are too many variables to combine into one form.
Each table has a Unique identifier "Survey_ID". There are no other fields to
relate to the other tables besides this "Survey_ID". My problem is enforcing
referential integrity. I designed the forms with a one-to-one relationship
based on Tbl1. When I try to enter data, I get "You cannot add or change a
record because a related record is required in the first tbl", when I attempt
to move from one form to another. The Survey_ID should equal the input from
the first tbl (which it does) but that is where the connection ends. Could
you help me figure out how to enforce refential integrity, while carrying the
value from the first table over to the other 5 ( and have the records save
on entry ) ?
Thanks

Have you investigated the normalized survey structure in Duane Hookum's At
Your Survey sample database?

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

A one to one relationship has directionality: there is still a "parent" table
and a "child" table, and a record must be added to the parent table before one
will be allowed in the child table. Therefore the order of operations is
critical! You must save the record into Tbl_1 before attempting to add a
record in Tbl_2, either by closing Tbl_1's form or moving off the record; or
you can use a Form based on tbl_1 with Subforms based on the other five
tables, using Survey_ID as the Master/Child Link Field.

Patrick is mistaken about the fieldnames: they can be the same or different as
you prefer; I'm sure it's the directionality that is the problem.

John W. Vinson [MVP]

 

[Musa]

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.
It's after I put data into that form and click on the next macro to move to
the next from that I get the error "You can't add or change a record because
a record is required in the first table".. It's looking for the record but
cant' find it. I've tried saving the record on various events On Close, etc...
It didn't work. I'm not sure how to make the record in the first table move
to another row and save BEFORE the second form opens to refer to it...

I have a one to one relationship with 6 tables .
Tbl_1

[quoted text clipped - 22 lines]

on entry ) ?
Thanks

Have you investigated the normalized survey structure in Duane Hookum's At
Your Survey sample database?

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

A one to one relationship has directionality: there is still a "parent" table
and a "child" table, and a record must be added to the parent table before one
will be allowed in the child table. Therefore the order of operations is
critical! You must save the record into Tbl_1 before attempting to add a
record in Tbl_2, either by closing Tbl_1's form or moving off the record; or
you can use a Form based on tbl_1 with Subforms based on the other five
tables, using Survey_ID as the Master/Child Link Field.

Patrick is mistaken about the fieldnames: they can be the same or different as
you prefer; I'm sure it's the directionality that is the problem.

John W. Vinson [MVP]

 

[Hunter57]

Hi John,

I aplologize for not making myself clear. What I meant was:

1. You cannot have two fields with the same name in a table so he could not
add Survey_ID as a new field to his other tables as a FK to link to Tbl_1.

2. If all of this tables already contain different data he could not change
his Survey_ID PK fields to FK's, then link those tables to Tbl_1 and have
referential integrity.

Of course you can have the same name as Pk and FK. I was not saying that
you could not do that.

Best Regards,
Pat Wood

 

[Hunter57]

Musa,

Are you saving the record in the First form before you enter data in the
second form? Or are you trying to save the record in the 2nd form first?

As John pointed out, the record in the first form should be saved before you
try to use the second form.

I get the feeling that you may have several problems with your databse
design and this is causing the problems you are having with the forms.

If you are allowed, you can zip it up and email it to me I will take a look
at it for you. You can send it as an email attachment to:
contact @ advan cin gso ftwa re.c om.
Just put the letters together to get the email address.

Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.
It's after I put data into that form and click on the next macro to move to
the next from that I get the error "You can't add or change a record because
a record is required in the first table".. It's looking for the record but
cant' find it. I've tried saving the record on various events On Close, etc...
It didn't work. I'm not sure how to make the record in the first table move
to another row and save BEFORE the second form opens to refer to it...

I have a one to one relationship with 6 tables .
Tbl_1

[quoted text clipped - 22 lines]

on entry ) ?
Thanks

Have you investigated the normalized survey structure in Duane Hookum's At
Your Survey sample database?

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

A one to one relationship has directionality: there is still a "parent" table
and a "child" table, and a record must be added to the parent table before one
will be allowed in the child table. Therefore the order of operations is
critical! You must save the record into Tbl_1 before attempting to add a
record in Tbl_2, either by closing Tbl_1's form or moving off the record; or
you can use a Form based on tbl_1 with Subforms based on the other five
tables, using Survey_ID as the Master/Child Link Field.

Patrick is mistaken about the fieldnames: they can be the same or different as
you prefer; I'm sure it's the directionality that is the problem.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

I aplologize for not making myself clear. What I meant was:

1. You cannot have two fields with the same name in a table so he could not
add Survey_ID as a new field to his other tables as a FK to link to Tbl_1.

Well... Tbl_1 could have a PK named Survey_ID, and Tbl_2 could have a PK named
Survey_ID; and you could define a relationship (necessarily 1 to 1) from
Tbl_1.SurveyID to Tbl_2.SurveyID. It did look like he might have two
SurveyID's in the same table (on looking at it again) but that's not how I
read it!

John W. Vinson [MVP]

 

[John W. Vinson]

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.
It's after I put data into that form and click on the next macro to move to
the next from that I get the error "You can't add or change a record because
a record is required in the first table".. It's looking for the record but
cant' find it. I've tried saving the record on various events On Close, etc...
It didn't work. I'm not sure how to make the record in the first table move
to another row and save BEFORE the second form opens to refer to it...

Put a line in the macro to save the record prior to opening the second form. I
don't use macros so I'm not certain, but it would be a Command "Save Record".
The VBA code would be either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty = True Then
Me.Dirty = False
End If

John W. Vinson [MVP]

 

[Hunter57]

Hi John,

Yes you are right. However I was not suggesting that he had 2 Survey_ID
fields in the same table. His problem, I think, is that all of these Pk's
will not allow for referential integrity when he tries to enter records.
Entering data in one table that is not in another violates the 1 to 1
relationship so he is not allowed to update his records. Because both linked
fields are PK's one cannot update the other as in a PK to FK link with
Cascading Updates. I think he needs to set up a normal Pk linked to FK in
order to enable him to enter his data. That is what I recommended in my post.

Best Regards,
Pat Wood

 

[Musa via AccessMonster.com]

Yes, I'm saving the First form before I enter data into the second form. The
data entry works fine when I don't try to enforce refential integrity. It's
when I check the cascading boxes and enforce referential integrity that I
seem to be getting error messages "You can not add or change a record without
a corresponding record in the first table" and the inability to save any
input other than the first table.

Musa,

Are you saving the record in the First form before you enter data in the
second form? Or are you trying to save the record in the 2nd form first?

As John pointed out, the record in the first form should be saved before you
try to use the second form.

I get the feeling that you may have several problems with your databse
design and this is causing the problems you are having with the forms.

If you are allowed, you can zip it up and email it to me I will take a look
at it for you. You can send it as an email attachment to:
contact @ advan cin gso ftwa re.c om.
Just put the letters together to get the email address.

Regards,
Pat Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.

[quoted text clipped - 28 lines]

 

[Musa via AccessMonster.com]

Thanks. I finally figured out what I was doing wrong. I know the one-to-one
is a horrible way to design but given the the number of variables and their
lack of relatability, I had no choice.

I discovered that the SAVE command was not in the correct place in the macro.
I moved the Run Command and now it works (with referential integrity enforced)
.. Thank you for your help..much apprecitated.

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.

[quoted text clipped - 4 lines]

It didn't work. I'm not sure how to make the record in the first table move
to another row and save BEFORE the second form opens to refer to it...

Put a line in the macro to save the record prior to opening the second form. I
don't use macros so I'm not certain, but it would be a Command "Save Record".
The VBA code would be either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty = True Then
Me.Dirty = False
End If

John W. Vinson [MVP]

 

[Musa via AccessMonster.com]

Thanks. I finally figured out what I was doing wrong. I know the one-to-one
is a horrible way to design but given the the number of variables and their
lack of relatability, I had no choice.

I discovered that the SAVE command was not in the correct place in the macro.
I moved the Run Command and now it works (with referential integrity enforced)
.. Thank you for your help..much apprecitated.

I have a macro that leaves the first table open and then opens the next table
with the same Survey_ID.

[quoted text clipped - 4 lines]

It didn't work. I'm not sure how to make the record in the first table move
to another row and save BEFORE the second form opens to refer to it...

Put a line in the macro to save the record prior to opening the second form. I
don't use macros so I'm not certain, but it would be a Command "Save Record".
The VBA code would be either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty = True Then
Me.Dirty = False
End If

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

Yes you are right. However I was not suggesting that he had 2 Survey_ID
fields in the same table. His problem, I think, is that all of these Pk's
will not allow for referential integrity when he tries to enter records.
Entering data in one table that is not in another violates the 1 to 1
relationship so he is not allowed to update his records. Because both linked
fields are PK's one cannot update the other as in a PK to FK link with
Cascading Updates. I think he needs to set up a normal Pk linked to FK in
order to enable him to enter his data. That is what I recommended in my post.

Well, again... a one to one relationship still has directionality. It IS in
fact possible to enter data in one table (if it's the "parent" table) prior to
entering data into the second (child) table. In that way it's no different
than a one to many relationship.

It is in fact possible to have a table with a field which is both that table's
PK and also a FK to another (parent, one to one) table. You can even set
cascade updates on the field, though there's no sense to doing so if the
parent PK is an (uneditable) autonumber.

John W. Vinson [MVP]