Results 1 to 14 of 14
  1. #1
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95

    Generate more identical records like first one

    Hello there..

    Does someone know, or have a code, for a button which OnClick of it, generates certain number of same records.. I need a code to ask me how many times I want to repeat same record (with all its previously populated fields)

    Something like on the picture below.

    Click image for larger version. 

Name:	forum3.jpg 
Views:	35 
Size:	166.4 KB 
ID:	30297

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make an append query to copy the selected record (using the key)
    then put a loop around it

    Code:
    sub btnCreate_click()
     dim vAns 
    dim i as integer
    
    vAns = InputBox("How many copies","Copy Record")
    if vAns <>"" then
      docmd.setwarnings false
      for i = 1 to vAns
         docmd.openquery "qaCopy1Rec"
      next
      docmd.setwarnings true
      msgbox "Done"
    endif
    end sub

  3. #3
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Could you explain a bit more? I do not know how to do it.. I did not mention, record I want to copy is on a subform but my copy button is on a form. Both form and subform were setup to same master and child field and I have one to many relation in between.

    I need my code/button to on-click, copy first record from a subform as many times I want and paste it to same subform, below that first record, pasting at the same time all previously populated fields from first record to all newly generated ones..

    Maybe I'm complicating here but help if you can..

    Thanks.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the query will ask for items in the subform table , using the subform ID.
    in the query criteria, use the BUILDER. (it always get the references correct)
    put cursor in the criteria under ID field,
    click builder, (icon on toolbar that looks like magic wand and says builder)
    select db, forms, openforms, subform, id field

    now the query will take the selected record, and make a copy. (append query to add back to source table)

  5. #5
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Sorry but I am still stupid about this .. I've implemented the code to my button (onClick). I have created an Append Query (selecting tbl_Welds table as append destination). Also tbl_Welds is the one shown at the subform_welds. At the subform_welds I do not have ID shown (as I do not need it there) but of course, it exists (and it is unique primary key in tbl_Welds).

    In my append query I've put together both ID fields together and as a criteria below ID field I have (see picture below)

    When I run the query by clicking on the button, It asks me to choose number of records to generate, I click e.g. 5 and get a message Done.. that is it, no new records below..

    Click image for larger version. 

Name:	Forum4.jpg 
Views:	26 
Size:	160.9 KB 
ID:	30306

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Are the records being added to the table correctly?

    After the records have been added to the table, the form needs to be refreshed to show the new records:
    Me!subformname.Requery

  7. #7
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    No.. SUbform is emty.. I have refreshed with F5.. Closed the database than open, nothing.. Hm..

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "No" to records not being added to the table? Then you append query isn't working, nothing to do with the form/subform.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post your dB with just a couple of main form records and around 4 sub form records for testing?

    Change any sensitive data, do a "Compact & Repair", then Zip it.

  10. #10
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Of Course.. Here it is.. I have deleted most of forms, queries and reports from it but main form and subform are visible..

    On the main form, there is a button "GENERATE WELDS". I must have done something wrong.. Tried several times..
    Attached Files Attached Files

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The query doesn't even work! Don't ever move on with forms etc. when this is the case. FIRST get the query working. Figure out what you are trying to do, have the form open and run the query at the same time. The subform is empty yet you are trying to use a value from there?

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Take a look at the attached dB..... haven't done extensive testing, but it seems to make copies.

    I removed the compound PK in tblWelds. One of the fields is an autonumber, so having the PK be ID and KK didn't make sense.

    There are several thing that I would like to change (fix - ) but I only did one - EVERY module should have
    Code:
    Option Compare Database
    Option Explicit
    as the first (top) two lines.

    I only duplicated the fields that was displayed in the "WELDS" per your image in Post #5.

    Oh, and I renamed the BE.......

    Hope this is close.....
    Attached Files Attached Files

  13. #13
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Thank you so much.. This works perfectly.. You saved me a lot of time with this.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're welcome.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-04-2015, 02:45 PM
  2. Replies: 3
    Last Post: 07-24-2015, 06:39 AM
  3. Generate Multiple Records at Once
    By macollins in forum Programming
    Replies: 3
    Last Post: 12-13-2013, 12:55 PM
  4. Replies: 1
    Last Post: 11-12-2013, 10:17 AM
  5. Replies: 2
    Last Post: 09-18-2012, 07:51 PM

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
  •  
Other Forums: Microsoft Office Forums