Relationship not working

[HGCanada]

RESOLVED: Relationship not working

Hi everyone. I have a database with 2 tables. I set a one-to-many relationship enforcing referential integrity, cascade updates.


Table A is the primary table, with "ReferralID" entered as a numbered field (the primary key).


Table B is populated via a subform, and "ReferralID" is not the primary key.


The way I have the relationship set up, it should automatically populate "referral ID" into table B when I create a table B record, and it should delete table B records when I delete a record from table A. Neither of these things happen. Any idea why?

Thanks.

 

[Beetle]

When working with a main form/sub form scenario, the foreign key value is inserted into the child table via the Master/Child link property of the Subform Control (this is the "window" that holds the sub form). Open your form in design view, open the properties sheet for the Subform Control, go to the Data tab and check the Link Master Fields and Link Child Fields properties. ReferralID should be present in both of these.

 

[MarkK]

it should automatically populate "referral ID" into table B when I create a table B record,

No. Cascade Update does not inform the child record, upon creation, what the ParentID is. On creation in table B, the ParentID still needs to be specifically provided at creation time. Once the valid relationship already exists and you edit the ParentID, this is when a Cascade Update occurs, and in that case there is no ambiguity about how the records are related.

...and it should delete table B records when I delete a record from table A.

No. For this to happen to must specify Cascade Delete on the relationship, and you have not said that you have done so. Cascade Update is not enough for the child to be deleted when the parent is.

hth
Mark

 

[HGCanada]

Thank you both! It is set to cascade add and delete.


The subform was set with the wrong "Link Master Fields and Link Child Fields" properties. I fixed them. Unfortuantely, I now have to go and manually enter referralID for all the records entered so far, for which it was not accurately gathering the data. Thanks again!

 

[Pat Hartman]

To clarify,
Cascade Update propagates changes in parent PK values to ALL related child records. Since autonumbers CANNOT be changed, this setting would never do anything if the PK of the parent table is an autonumber. However, if you are using custom strings or numbers as the PK, those would be propagated.

So, you have customer Great Lakes, LLC and a PK of GL. If the company changes its name to Northern Great Lakes, LLC and so you want the PK to now be NGL, when you change the PK from GL to NGL, ALL child records in ALL child tables will be changed. So, the order records and the contact records and the address records, and whatever other child tables you have that are related to tblCompany get updated.

One of the reasons we use autonumber primary keys is so that we NEVER actually have to propate key changes like this.

 

[HGCanada]

Thanks. Good advice for future. In this case, we already had a set of IDs created, but I will try to set up with autonumber in the future. Thanks.