By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Referential Integrity and Tabbed forms

P: 12
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
Sep 18 '07 #1
Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Sep 18 '07 #2

P: 12
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.CustomerID (parent) to Subform.CustomerID (child) - this subform is related to a table that has only 1 field (CustomerID).
Subform.CustomerID (parent) to SubTabForm.CustomerID (children) based on a one to many relationship.

Andy
Sep 18 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.CustomerID (parent) to Subform.CustomerID (child) - this subform is related to a table that has only 1 field (CustomerID).
Subform.CustomerID (parent) to SubTabForm.CustomerID (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
Sep 18 '07 #4

P: 12
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
Sep 18 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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?
Sep 18 '07 #6

P: 12
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
Sep 18 '07 #7

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Form                   RecordSource
  2.  
  3. MainForm               tblParent
  4. IntermediateForm       tblParent
  5. SubForm                tblChild
  6.  
Sep 18 '07 #8

P: 12
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
Expand|Select|Wrap|Line Numbers
  1. Form                   RecordSource
  2.  
  3. MainForm               tblParent
  4. IntermediateForm       tblParent
  5. SubForm                tblChild
  6.  
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
Sep 18 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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. ;)
Sep 18 '07 #10

P: 12
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
Sep 19 '07 #11

FishVal
Expert 2.5K+
P: 2,653
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
Sep 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.