Adding new records in one table & adding to other tables. (1 Viewer)

Hello there,

I'm working on sorting someone elses database out and it's in a right mess.

There are three tables "tblDetails" – this contains the main info and two tables called "tblProgress" and "tblOutcome"

Each has got a unique ID field called [ReviewID] and each table has only one record per unique review, and so are based on one-to-one relationship.

I have a form with three tabs – tab1 has the fields for "tblDetails", tab2 "tblProgress" and tab3"tblOutcome"

How do I design it so that when I enter a new record and ID number in the [ReviewID] field on tab1 (for "tblDetails"), that this then adds the new record and value to the [ReviewID] fields for "tblProgress" and "tblOutcome" on the other two tabs?

I'm going in circles here - is it to do with the table relationships, should I use subforms, or VBA?

D'oh!!

This is so simple I couldn't see the wood for the trees!!

ANSWER to "How to add new records automatically to subforms" is:

Let's assume you have two tables:

tblParent
pkParentID Primary Key as autonumber
etc

tblChild
pkChildID Primary Key as autonumber
fkParentID Foreign Key as number>long
etc

and let's assume that you have set referential integrity between pkParentID and fkParentID.

Your main form would be based on tblParent and the subform based on tblChild.

The link child/master fields of the control holding the subform would be set to fkParentID and pkParentID respectively.

If you enter a new record in the form (tblParent) it will automatically create a new record and fill the ParentID field of the subform (tblChild).

BUT only if you adhere to the above and remember that 2 Primary keys cannot be linked in the PARENT & Child relationship that is used in the form & subform. Therefore you have to set a primary key field in the tblChild that is totally unique to that tbl, i.e. pkChildID.

Thought I'd post this as it seems a common challenge to those starting out in the world of Access