Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    create new records (97)

    I have a table, t1 with a 1 to many rel. to t2 and it has a 1 to many rel. with t3. The database keeps track of a group of people. Each person has a series of immunisations and after each immunisation they have a series of tests. The problem is i need to enter data into these tables via forms f1, f2 and f3. f3 is a subform of f2. I am having trouble creating new records in t2 and t3. I go into f1 create a new record in t2 but sometimes cant save that record to create new recs in t3 without closing the form and going back into it. I was creating records by updating a recordset but I am unsure where to place the code because ppl need to edit and view the records without new records appearing. I had the code linked to the immunisation number in f2 it would work sometimes but others I would have to close the form and reenter which would, for some reason, create two records. Is there a trick to saving t2 in f2 and creating recs in t3 subform at the same time? Ideally, when the record is created in t2, the five new records in t3 should be created and appear as partialy filled records in the subform of the new f2. Has anyone done this sort of thing before? Thanks in advance for any help

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: create new records (97)

    From what you have described about tables T1, T2 and T3 you should be able to do this with a Main Form, and 2 related Subforms.
    There is a trick about doing this, but no biggy. It involves defining a text field on the Main form that is used as the joiner (for want of a better word) for Subform 2 to Subform 3.
    I'm pretty sure this technique is in the Northwind database, although I won't stake my life on it.

    Pat

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create new records (97)

    Thanks Pat but unfortunately f1 and f2 have to be seperate forms

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: create new records (97)

    I'm not sure I understand enough details to give you an explicit set of instructions, but I presume you are trying to use DAO and VBA to add the 5 records once the main record has been added. If that's the case, you should trigger the code on the AfterInsert event of the main form. You will then need to requery the subform in order to make the new records visible. You need isn't an uncommon one, so if you want to post the code and pertinent details, I'm sure one of the loungers can give you some additional pointers.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create new records (97)

    In this situation I would create a separate unbound form in which users can enter the information necessary to create a new record in T2 (an immunisation). The form would contain an OK and Cancel button. The Cancel button would just close the form, while the OK button would create a new record in T2, and then create 5 new records in T3. After that, the form would be closed and form F2 would be opened at the newly created record. I would use DAO to create the new records.

Posting Permissions

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