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
11 2582
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.
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
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
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
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?
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
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
-
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
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. ;)
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into...
|
by: Jimmie H. Apsey |
last post by:
Referential Integrity on one of our production tables seems to have been
lost. I am running Postgres 7.1.3 embedded within Red Hat
kernel-2.4.9-e.49.
Within that I have a table with referential...
|
by: Geisler, Jim |
last post by:
So, as far as I know, PostgreSQL does not have any way of verifying the loss
of referential integrity.
Are there any recommended methods or utilities for checking referential
integrity in a...
|
by: heyvinay |
last post by:
I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:
Transaction Table...
|
by: Andrew R |
last post by:
Hi
I'm creating a series of forms, each with with around 15-20 text boxes.
The text boxes will show data from tables, but are unbound to make them
more flexible.
I want the form to be used...
|
by: moskie |
last post by:
Is there a way to run an alter table statement that adds a constraint
for a foreign key, but does *not* check the existing data for
refrential integrity? I'm essentially looking for the equivalent...
|
by: CPAccess |
last post by:
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation:
I have two tables:...
|
by: Wayne |
last post by:
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.
I'm building a database that consists of frontend and backend. Some
of the lookup tables...
|
by: ApexData |
last post by:
Access2000, using a continuous form.
I’m getting a message that say “you cannot add or change a record
because a related record is required in table Employee”.
This occurs in all my combobox...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |