Adding new records to related tables

[viscounthamworthy]

My database is to keep track of trust funds. Each trust can have one tax return, one billing entry and one set of accounts. So, the master table is TBL_TrustsExtant. This table has a one to one relationship with the three minor tables, TBL_TaxReturns, TBL_Billing and TBL_Accounts. Referntial Integrity is set to cascase upwards.

Trust_ID is the PK in the main table and is an autonumber. Trust_ID is the FK in the minor tables. The PKs of the minor tables are autonumbers, such as Tax_ID,Billing ID etc.

All tables have 245 records in them. Why is it that when I add a new record in table datasheet mode (ie entry 246), the three minor tables still only have 245 records in them? I want the new PK entry in the main table to become a new FK entry (and thereby trigger the autonumber PK) in the minor tables.

Similarly, when I try this via form and subform, I cannot proceed, as the subform is looking for a FK equivalent to the PK for the record that has just been created?

I really am lost on this. Any help would be much appreciated.

 

[MajP]

1) Why have multiple tables. There is really no such thing as a 1 to 1 relationship? If they are unique to the trust then they define the entity and could be in the main table. Sometimes there is a good reason to do this (security, speed, size limitations, etc), but why complicate it if you do not have to.

2) The datasheet would work, but it would require you to manually put in three seperate FKs

3) A subform should definately work. My guess you did not set up the Master/Child link correctly.

 

[MajP]

Similarly, when I try this via form and subform, I cannot proceed, as the subform is looking for a FK equivalent to the PK for the record that has just been created?

It sounds like you are trying to create a tax return, billing, or account prior to creating a trust.

My design would probably be a main form based on Trust. On the main form I would have a tab control with three tabs and each with a subform:
billing, taxreturn, and accounts.

Each subform is linked to the main form by trust_ID

 

[PHV]

I'm with MajP.
Anyway in a 1:1 relationship I prfer having the FK the same column as the PK, eg:[tt]
TBL_TaxReturns
Trust_ID (Long) PK and FK [/tt]referencing TBL_TrustsExtant(Trust_ID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886