Referential Integrity and Tabbed forms

[AndyM]

Hi,
I have a curious problem that is causing me large amounts of grief and is steadily turning me grey. Hopefully you guys can help.

I have a Master table that contains a CustomerID (as well as customer name etc), this is used as the Primary key to all the tables relating to that customer. I've been using 1 to Many relationships for most of the tables (e.g A customer can have many addresses) with Referential Integrity and cascading turned on. Mostly this is working fine, with the CustomerID being forced into each table's CustomerID field. One instance, however, doesn't seem to do as it's told.

Data is entered in a multi-tabbed form with subforms for each product embedded in each of the tab pages. The instance that is causing me problems is a complex product that needs another tabbed subform to cover all the required fields. I've achieved this by using a plain subform (a table that has 1 field - CustomerID - in it) that contains another subform embedded with the tabs in it and the plain subform is embedded in the main tabbed form. It's done this way because I believe you cant put tabbed controls into tabbed controls. Hope that makes sense :-)
I've tried different sorts of relationships to get this working but none of them seem to update the CustomerID field's in the sub-sub-form's tables.

e.g
1 to 1 to Many - CustomerID table to subform table to sub-sub-form's tables
1 to Many - CustomerID to sub-sub-form's tables

Can anyone see the obvious mistake I'm making please

Cheers

Andy

[FishVal]

Hi,
I have a curious problem that is causing me large amounts of grief and is steadily turning me grey. Hopefully you guys can help.

I have a Master table that contains a CustomerID (as well as customer name etc), this is used as the Primary key to all the tables relating to that customer. I've been using 1 to Many relationships for most of the tables (e.g A customer can have many addresses) with Referential Integrity and cascading turned on. Mostly this is working fine, with the CustomerID being forced into each table's CustomerID field. One instance, however, doesn't seem to do as it's told.

Data is entered in a multi-tabbed form with subforms for each product embedded in each of the tab pages. The instance that is causing me problems is a complex product that needs another tabbed subform to cover all the required fields. I've achieved this by using a plain subform (a table that has 1 field - CustomerID - in it) that contains another subform embedded with the tabs in it and the plain subform is embedded in the main tabbed form. It's done this way because I believe you cant put tabbed controls into tabbed controls. Hope that makes sense :-)
I've tried different sorts of relationships to get this working but none of them seem to update the CustomerID field's in the sub-sub-form's tables.

e.g
1 to 1 to Many - CustomerID table to subform table to sub-sub-form's tables
1 to Many - CustomerID to sub-sub-form's tables

Can anyone see the obvious mistake I'm making please

Cheers

Andy

Hi, Andy.

As far as I've understood your situation this is not related to referential integrity.
You should properly set master/child relationships of main/background forms and background/subform.
I use this trick to open tabbed subform for datasheet mainform.

[AndyM]

Hi, Andy.

As far as I've understood your situation this is not related to referential integrity.
You should properly set master/child relationships of main/background forms and background/subform.
I use this trick to open tabbed subform for datasheet mainform.

Thanks FishVal,

I've gone through the master/child stuff and it all looks OK to me - still not working though.
I've wired up the relationships as follows:

Customer.Custom erID (parent) to Subform.Custome rID (child) - this subform is related to a table that has only 1 field (CustomerID).
Subform.Custome rID (parent) to SubTabForm.Cust omerID (children) based on a one to many relationship.

Andy

[FishVal]

Thanks FishVal,

I've gone through the master/child stuff and it all looks OK to me - still not working though.
I've wired up the relationships as follows:

Customer.Custom erID (parent) to Subform.Custome rID (child) - this subform is related to a table that has only 1 field (CustomerID).
Subform.Custome rID (parent) to SubTabForm.Cust omerID (children) based on a one to many relationship.

Andy

Hi, Andy.

Question: Is your intermediate subform linked to the same table/query as the main form?

Suggestion: If not made so far. Ensure [CustomerID] field on the intermediate form be visible and browse through the records. Check whether content of the field changes appropriately. It seems that one of master/child link does not work, so lets determine which.

Keep posting.

Fish

[AndyM]

Hi, Andy.

Question: Is your intermediate subform linked to the same table/query as the main form?

Suggestion: If not made so far. Ensure [CustomerID] field on the intermediate form be visible and browse through the records. Check whether content of the field changes appropriately. It seems that one of master/child link does not work, so lets determine which.

Keep posting.

Fish

Hi Fish,
A top idea - I stuck the CustomerID field in the intermediate form and it does indeed update with the previously entered CustomerID.
I get an error now 'You cannot add or change a record because a related record is required in tblVoice (the intermediate table)

Andy

[FishVal]

Hi Fish,
A top idea - I stuck the CustomerID field in the intermediate form and it does indeed update with the previously entered CustomerID.
I get an error now 'You cannot add or change a record because a related record is required in tblVoice (the intermediate table)

Andy

Hi, Andy.

Not sure what do you mean.
The main form and the intermediate form has to have the same RecordSource or at least must be linked to the same table.
Did you set up them this way?

[AndyM]

Hi, Andy.

Not sure what do you mean.
The main form and the intermediate form has to have the same RecordSource or at least must be linked to the same table.
Did you set up them this way?

I linked the intermediate form to it's own table, a 1 field table. This table is related to the main form/table through a 1:1 relationship. Is this a bad way of doing things? I didn't realise that I could use the same record source to link the intermediate table. Architecture not one of my strong points :-)

Andy

[FishVal]

I linked the intermediate form to it's own table, a 1 field table. This table is related to the main form/table through a 1:1 relationship. Is this a bad way of doing things? I didn't realise that I could use the same record source to link the intermediate table. Architecture not one of my strong points :-)

Andy

The only purpose of intermediate form is to link main form to subform as soon as direct link is impossible. Am I right?
Sure the linking cannot be achieved if intermediate form is bound to a recordsource other than that for main form.
Table relationships will not help.

So the configuration should be something like the following

Form                   RecordSource

MainForm               tblParent
IntermediateForm       tblParent
SubForm                tblChild

[AndyM]

The only purpose of intermediate form is to link main form to subform as soon as direct link is impossible. Am I right?
Sure the linking cannot be achieved if intermediate form is bound to a recordsource other than that for main form.
Table relationships will not help.

So the configuration should be something like the following

Form                   RecordSource

MainForm               tblParent
IntermediateForm       tblParent
SubForm                tblChild

I was using the intermediate form because I cant put tabbed controls into another tabbed control.
I take your point on the parent/child stuff and I'll give it a try tomorrow. The pub beckons tonight :)

Thanks for your help - I'll let you know how I get on

Andy

[FishVal]

I was using the intermediate form because I cant put tabbed controls into another tabbed control.
I take your point on the parent/child stuff and I'll give it a try tomorrow. The pub beckons tonight :)

Thanks for your help - I'll let you know how I get on

Andy

Ok.
I use it to have a multiple subforms on main form in datasheet view.
Have a good time. ;)

[AndyM]

Ok.
I use it to have a multiple subforms on main form in datasheet view.
Have a good time. ;)

Hi FishVal,
good news, your theory worked. I now have a master form that controls subforms through an intermediate form. The only weird thing is that the join source has to be a control (with a recordsource of the master CustomerID) that is present in the intermediate form - if it's not then the subform CustomerID doesn't update.
I can live with this though - by turning the Visible property off on the control.

Many thanks for your help - much appreciated

Andy

[FishVal]

Hi FishVal,
good news, your theory worked. I now have a master form that controls subforms through an intermediate form.
Andy

Glad it was helpful.

The only weird thing is that the join source has to be a control (with a recordsource of the master CustomerID) that is present in the intermediate form - if it's not then the subform CustomerID doesn't update.
I can live with this though - by turning the Visible property off on the control.
Andy

I think there are no options here but to use invisible control on intermediate form

Many thanks for your help - much appreciated
Andy

You are welcome.

Best regards,
Fish