Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Red face Unanswered: Adding new records in one table & adding to other tables.

    Hello there,

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

    Anyhoo 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

  2. #2
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •