Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Memphis, TN

    Question Unanswered: Controlling adding new rows to a CHILD

    Hi all...

    I have something new I'm working on.

    My situation is a bit more complex than I'm about to describe, but I think if I simplify it and solve THIS one problem I'll be able to answer the same problem as it relates to the bigger picture:


    (again if this sounds silly its because I made it simpler)

    I have two tables

    1) UNITENTRY - two columns - 1) KEY (autonumber) & 2) MONTHLY_UNITS
    2) UNITS - three columns - 1) UNITS_KEY 2) MONTH_SEQUENCE & 3) UNITS

    I have a MAIN form... its control source is a SELECT * FROM CLIENTS. On it are several subforms (they're contained on different 'tabs' of a tab control, but I'm not sure that is too important)

    One of the subforms (sfrmUNITS) has a control source of:
    SELECT *
    WHERE a.CLIENT_ID=[Client ID displayed on MAIN FORM]

    ON this subform is another subform (sfrmMUNIT) and its control source is:
    SELECT *
    WHERE a.KEY=[the KEY value from the UNITENTRY row displayed on the first subform] AND a.MONTH_SEQUENCE)=1

    The "Link Child Fields" for sfrmMUNIT is UNITS_KEY on the UNITS table
    The "Link Master Fields" for sfrmMUNIT is KEY on the UNITSENTRY table.


    Ok.. IF a row is already in the UNITENTRY table for the client... and ALREADY has a KEY value of.. oh.. say 12.. THEN this works fine.. the sfrmUNITS subform displays (and creates a new row for the UNITS table (placing the key "12" from the UNITENTRY row into the UNITS_KEY column and the the month sequence "1" into the MONTH_SEQUENCE column on the new UNITS row) and allows me to fill in the UNITS value on it.

    BUT.. if a row has NOT been created in UNITENTRY table for the client, then obviously no rows could (or should) be allowed to be stored in the UNITS table..

    In other words for example, if I bring up on the main form the CLIENT row for client ID 123... where client 123 does NOT have a row in the UNITENTRY table... Then, just by virtue of mainform displaying the subforms (and the child/master links?), the sfrmMUNIT is going to try to create a new row for that nonexistant UNITENTRY row.

    Not only will it TRY to do it... it WILL and will fill the 'key' on the UNITS row with a ZERO which is the value displayed in the fields of sfrmUNITS.

    If I ( or the user of this contraption) were to just "flip through" the clients on the MAIN form (without ever even looking or paying attention to the subforms), a whole bunch of rows are gonna get created in UNITS with a key of ZERO (unless - as it should be - KEY and MONTH_SEQUENCE are unique, then it will only do it once).

    What I'm trying to figure is wether there is a way to control the 'adding' of new rows to UNITS strictly based on wether a row has been added to UNITENTRY.. and if a row has NOT been added to UNITENTRY, (then do not store or attempt to store) any new UNITS rows.

    Perhaps I'm asking too much of Access to try to use its interface to control this?

    Does this sort of thing need to be handled COMPLETELY by code?

    I sure hope not. I would rather hope it CAN be done and learn how to do it.

    Thanks for putting up with reading this.. I hope yall understand what I'm getting at.

    Memphis Bill

  2. #2
    Join Date
    Jul 2003
    What type of relationship do you have between UnitEntry and Units?
    If you establish a one-to-many relationship w/UnitEntry on the "one" side, then new records cannot be added to the Units table until they are first in the UnitEntry table.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jun 2003
    Memphis, TN
    I have none defined.

    When I define one I get all sorts of un-friendly error messages.

    I guess I'll read up on it in my Developers Handbook...

    But to be honest, the system error messages are not very easy to work with.

    Thanks for your reply.


Posting Permissions

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