Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicating multiple records in multiple tables (Access97)

    I work in the garment industry. I have a form with a Style Number (primary key) and a sub form in it.

    The form shows records from TABLE1 and the sub form shows records from TABLE2.

    The sub form shows many records that have the same Style Number. These represent different measurements of the garment for each size (Waist, Inside leg, etc).

    Often new garment styles are very similar to previous styles, just a few changes to a collar, sleeve or pocket. So I want to be able to duplicate ALL records related to a Style Number at the click of button.

    I thought I would need to use VBA. So I

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

    Re: Duplicating multiple records in multiple tables (Access97)

    You could do this another way by using append queries.

    What I would do is to create a text control on the form and name it NewStyleNumber, and also a button with a couple of append queries behind it in the OnClick event of this new button.

    I would set up both of the append queries as permanent queries, mush along the lines of:

    1. Append query to duplicate the Table1 entry.
    INSERT INTO Table1 (StyleNumber, Fields separated by commas)
    SELECT forms![YourFormName]!NewStyleNumber as Expr1, fields separated by commas
    FROM Table1
    WHERE StyleNumber = forms![YourFormName]!StyleNumber

    2. Append query to duplicate the Table2 entry.
    INSERT INTO Table2 (StyleNumber, Fields separated by commas)
    SELECT forms![YourFormName]!NewStyleNumber as Expr1, fields separated by commas
    FROM Table2
    WHERE StyleNumber = forms![YourFormName]!StyleNumber

    Hope this helps,
    Pat

    If you have any further questions just holler.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating multiple records in multiple tables (Access97)

    Thank you Pat

    I've never used an append query and it worked a treat. It was something I'd been stuck on for awhile. I've added append queries to other sections of the data base too, making it work much quicker.

    Thanks for showing it to me.


    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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