one-to-one relationship does not update "child" table

[pavlos]

Hi,

The CUSTOMERS table is the "parent" in the relationship and has an autonumber as its primary key. The DETAILS table is "child" and it’s primary key is defined as number [Long Integer indexed(no duplicates)]. I related both fields with a one-to-one relationship and checked both cascade update and cascade delete boxes. However, when I insert a new record (or delete) in the CUSTOMERS table the primary key of the DETAILS table is not updated! Any hint why would be appreciated!!

Regards,
pavlos

 

[boblarson]

Tables do not get updated "automatically" with the primary key of the master table. You need a FORM, with a Subform, to enter data and if you set up the Master/Child links properly then Access will insert the master table PK in to the child table as the foreign key. But just setting up relationships does NOT mean that they will automatically propagate.

 

[gemma-the-husky]

but what you are saying is that for each item in the customers table there should be (at most) only one item in the details table, NOT that there is AUTOMATICALLY one item in the detail table

so
a) why do you need a separate details table - why can the information in the details table NOT be held in the cutomers table AND
b) you still need a mechanism to create the details record for any given customer record. - thats why you arent seeing any related records.

 

[pavlos]

Tables do not get updated "automatically" with the primary key of the master table. You need a FORM, with a Subform, to enter data and if you set up the Master/Child links properly then Access will insert the master table PK in to the child table as the foreign key. But just setting up relationships does NOT mean that they will automatically propagate.

oh, yes forgot to mention that for each table I use a form to enter th edata.

 

[boblarson]

oh, yes forgot to mention that for each table I use a form to enter th edata.

What is the Primary Key of the master table and what is currently set as the Master / Child link of the subform?

 

[pavlos]

but what you are saying is that for each item in the customers table there should be (at most) only one item in the details table, NOT that there is AUTOMATICALLY one item in the detail table

so
a) why do you need a separate details table - why can the information in the details table NOT be held in the cutomers table AND
b) you still need a mechanism to create the details record for any given customer record. - thats why you arent seeing any related records.

Correct each record from CUSTOMERS table relates only one record from DETAILS table. I thought of using one table for both of them but I am a bit hesitant cause that table will have more than 30 fields! it's a dilemma...

 

[pavlos]

What is the Primary Key of the master table and what is currently set as the Master / Child link of the subform?

Bob,

i am not sure if i can undrestand you here... do yiu mean the value of the master pk?

 

[boblarson]

Bob,

i am not sure if i can undrestand you here... do yiu mean the value of the master pk?

Sort of -

What is the actual name of the field in your Master table which is the primary key.

Secondary -

When you go into design view on the main form, and then select the Subform CONTAINER (the control that houses the subform on the main form) you should be able to spot two properties in the property dialog (Master Link and Child Link). See the first screenshot here
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
for a visual on how to select the subform container and the master / child links.

 

[pavlos]

Sort of -

What is the actual name of the field in your Master table which is the primary key.

Secondary -

When you go into design view on the main form, and then select the Subform CONTAINER (the control that houses the subform on the main form) you should be able to spot two properties in the property dialog (Master Link and Child Link). See the first screenshot here
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx
for a visual on how to select the subform container and the master / child links.

sorry Bob i had to leave my office for an hour...

 

[pavlos]

sorry Bob i had to leave my office for an hour...

Bod,

The pk field name of the Master table which is CustomersId.

I use a tabbed form with a 2 pages, the 1st page displays fields of the CUSTOMERS table (master) and the second page displays fields of the DETAILS table.

Both tables feed a query (cause i need to perform calculations) and in turn the query feeds all data in the 2 pages of the form.

On the top of the form I have a combo box where I choose the customer’s name and in the 2 pages I can see all his/her data.

Now when I am in the 1st page of the form and add or delete a customer the pk of the CUSTOMERS table (master) will change respectively but no the pk of the DETAILS table thus changing the sequence between the two pk and consequently all the other problems...

 

[boblarson]

You should not be using a single query for this. If you do you will need to manually add the key to the 2nd table. Create a SUBFORM to put on the 2nd page of the tab control for the 2nd table and then link together using the CustomerID.

 

[gemma-the-husky]

Correct each record from CUSTOMERS table relates only one record from DETAILS table. I thought of using one table for both of them but I am a bit hesitant cause that table will have more than 30 fields! it's a dilemma...

but theres no problem with any number of fields (max 255 i think)

as long as the data is normalised properly, just put all the fields in 1 table - a 1 to 1 details table is completely pointless for pretty well all purposes

 

[boblarson]

a 1 to 1 details table is completely pointless for pretty well all purposes

With the exception of if all of the fields do NOT need to be filled out for everyone. Separating into two tables, even though it may be a one-to-one relationship, doesn't mean that all records in the first table may have a record in the second table (depending on the fields that are in the second table).

 

[boblarson]

But 30 fields is hardly a problem (at least as long as it won't continue to grow and grow in number of fields, but rows instead).

 

[pavlos]

You should not be using a single query for this. If you do you will need to manually add the key to the 2nd table. Create a SUBFORM to put on the 2nd page of the tab control for the 2nd table and then link together using the CustomerID.

well i deleted the 2nd page of the tab control, still the key in the second table does not update! i wonder why is this happening??!!

 

[pavlos]

But 30 fields is hardly a problem (at least as long as it won't continue to grow and grow in number of fields, but rows instead).

hm... let's say the table eventually reaches to have about 40 fields and more than 5000 rows, will it be ok?

 

[pavlos]

eventually i choose the easy way, both tables in one and it works fine!

thanks guys for helping!