Results 1 to 4 of 4
  1. #1
    Join Date
    May 2017
    Posts
    17

    Need a Command button on the parent form which duplicates the last-added record in a subform

    Hi,



    I have a form (the parent form) which has a subform that is in datasheet view populated with records according to whatever is selected in an unbound combo box that has been placed on the parent form.

    The subform can potentially list thousands of records as a result of the user's selection, which is as desired.

    The records shown in the subform need to be listed in ascending order (i.e. the oldest record is at the top, the most recent at the bottom in the subform).

    To duplicate the last-added record the user can either click the tiny hard-to-see 'New (blank) record' button at bottom-left of the subform causing the subform to jump to the bottom of the list of records and adds a new blank record ready for data entry, then the user selects and copies the last-added record and pastes it on top of the new blank record, or, the user scrolls all the way down through the thousands of records listed in the subform to get to the last-added record, and select copy and paste as above. Either way is messy.

    I would like to the user to be able to duplicate of the last-added record in the subform using a Duplicate Record command button placed on the parent form. Thus not placed on the subform, as apparently the only way to do so is to have the subform display its records in Continuous mode and place the Duplicate Record button either in the subform's header or footer - but that is not desired.

    Therefore, if possible, I'd like, on opening the parent form containing the subform which is in Datasheet mode, and upon the user populating that subform with records the subform then automatically jumps down to and displays the last few recently-added records, and adds a new blank record ready for data entry, so that if the user desires, clicks a Duplicate Record that is on the parent form, and the most recently-added record shown in the subform is duplicated into that new blank record (and another new blank recorded added ready for the user to do either again.

    Is this possible? Any help regarding the vba code (vba code much preferred) that needs to be in the Duplicate Record button placed on the parent form would be very much appreciated. Cheers, Allen.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. Q1 to get the last entry for that key: select Max(ID). From tSubTable where [masterKey]=forms!myForm!txtKey

    2. Then Q2 to get all the data you need based on Q1: select table.* from Q1,table where table.key = Q1.id

    3. Make an append qry using Q2 to add the 'copy'

  3. #3
    Join Date
    May 2017
    Posts
    17
    Many kind thanks ranman256. I have a friend to whom I have since referred my prob to and who knows vba code pretty well, and he says your Q1 and Q2 response means 'create a query that grabs the last-created record, then create another query based on the ID in the first query, then make an append query based on the 2nd query. That would work he said. However as an interim measure he came up with a simple solution which pretty much does the job, just add code to a field I wish the user (i.e. myself) to double-click on (so I use the ID field) to duplicate whatever record I am clicking on, and that record will be duplicated at the bottom of the list of records in the subform, viz...

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

    Works a treat

    Cheers, and many kind thanks again,
    Allen

  4. #4
    Join Date
    May 2017
    Posts
    17
    Hi all again,

    As mentioned, that bit of code did the trick. However, it worked only if Google Earth was not open at the same time, else the Clipboard would not respond in which case Access could not copy the record. So my friend came up with the code below that works regardless of whatever else is open on the computer...

    Cheers and thanks again,

    Allen


    If Me.NewRecord Then
    MsgBox "Select the record to duplicate.", , "Duplicate Record"
    Exit Sub
    Else

    'Duplicate the main record: add to form's clone.

    With Me.RecordsetClone

    .AddNew

    ![AUDIOVISUALIDLINK] = Me.[AUDIOVISUALIDLINK]

    ![PRIMARYREFERENCENUMBER] = Me.[PRIMARYREFERENCENUMBER]

    ![SPECIMENID] = Me.[SPECIMENID]

    ' etc etc according to the fields I wanted to copy data from


    .Update

    'Display the new duplicate.
    Me.Bookmark = .LastModified
    End With

    End If

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

Similar Threads

  1. Replies: 2
    Last Post: 04-05-2019, 12:42 PM
  2. Replies: 4
    Last Post: 08-10-2018, 12:00 AM
  3. Replies: 3
    Last Post: 09-08-2017, 07:18 PM
  4. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  5. Replies: 4
    Last Post: 05-17-2011, 06:56 AM

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