Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: populate sister files

    I have a volunteer file, and associated with it and with the same primary key value, are AdditionalInterests, Education, and other files. When I write out a record to the Volunteers file, I want to write records with the same primary key value out to the sister files, so that when the user has time to enter the extra information, the placeholder (primary key) is already there, and enforces the relational nature of the system.

    what's the best way to write to the other few files when I write out a volunteers record?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, this question sounds familiar

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You're talking about one-to-many relationships, here, i.e. one volunteer/many (possible) additional interests, one volunteer/many (possible) educational degrees, and this is usually done using a Main Form/Subform scenario. The Main Form ((the one side) is based on a Volunteer Table, the Subforms (the many side) based on the Interests and Education Tables. The Primary Key of the Volunteer Table will be a Foreign Key of each of the other Tables. When you add the Subforms to the Main Form, the Subform Wizard will automatically use the Primary Key/Foreign Key to link the two, assuming they have the same name in each Table, and the Primary Key will automatically be inserted into the Foreign Key Field on the Subform Records.

    And please note that an Access Database only consists of one file! You're referring to Tables, here, and you really should take a few minutes to at least learn the Object names, in Access; it will make it much easier for you to ask questions/get help, here!

    Linq ;0)>
    Last edited by Missinglinq; 11-30-12 at 19:53.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2012
    Posts
    5

    populate sister files

    so, i've done exactly as you've said: created a TABLE file a primary key, another TABLE with that first TABLE's primary key set as a foreign key (using the Relationships tool) in a one-to-many relationship, and created 2 forms, one on each TABLE. Then, i opened the form on the "one" TABLE, and dragged the "many" table onto it as a subform. seems good.

    Then, i run the form on the "one" TABLE. All the fields appear for both forms, and all appears nice. Then, I enter "NEW" for the primary key, and start creating a new record on the "one" TABLE, get to the last field, then "tab" to the first field of the "many" TABLE, which is the primary key of the "many" TABLE. I get "NEW" for the primary key of the "many" TABLE, since I set that fields type to "Autonumber". It proceed to the foreign key field to the "one" TABLE. It lets me type any number in! isn't this a subform? why doesn't it default to the key of the "one" TABLE?!

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    When you created the subform on the form for your main table, did you set the parent/child fields? This is required in order for the form to correctly process data.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I suspect that weejas has identified the problem. I never do this kind of thing by dragging the Form the Subform is based on onto the Main Form, but rather add a Subform Control to the Main Form. When you take this route, the Subform Wizard pops up and walks you through the proper steps. I assume the 'drag and drop' approach bypasses the Wizard. When using the Wizard it asks for the Form you're basing the Subform on, and if their are Fields in both Main Form and Subform with identical names assumes that this is the link to be used. If no such Fields exists, I believe it asks you to decide which Fields to link on. Can't remember exactly, because I've done this for so many years, and I always name the Linking Fields the same in both related Tables.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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