Move to Last Record after duplicating record (1 Viewer)

kevnaff

Member
Local time
Today, 13:23
Joined
Mar 25, 2021
Messages
121
Hello All.

Our database allows a user to duplicate a record using the following code:

Code:
Private Sub CommandDuplicate_Click()
On Error GoTo Err_CommandDuplicate_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
[Code No] = "NONE"
Me.CreatedBy = [TempVars]![tvarUser]

Exit_CommandDuplicate_Click:
    Exit Sub

Err_CommandDuplicate_Click:
    MsgBox Err.Description
    Resume Exit_CommandDuplicate_Click
    
End Sub

Once the command button is clicked. A duplicate record is clicked and the toolbar at the bottom of the form now shows record 2 of 2, see below.

1649759286681.png


However if the user clicks anywhere on the form, the record jumps back to the record that has been duplicated. In this case record 1 of 2. The user could just hit the Last Record button on the toolbar, but I was hoping for it to remain on the last record.

Does anybody know how to do this?

I have tried adding the following code at the end of the command button, but this doesn't work.

Code:
DoCmd.RunCommand acCmdRecordsGoToLast


Thanks All.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
16,394
this is equivalent command (after duplicating):

docmd.GoToRecord,,acLast
 

kevnaff

Member
Local time
Today, 13:23
Joined
Mar 25, 2021
Messages
121
this is equivalent command (after duplicating):

docmd.GoToRecord,,acLast

Hi arnelgp,

I have added this code to the end of the command button and it still switches back to the first record.

I can't figure out why this is happening, very strange.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
16,394
do you have any Sorting in your Form or if you are using Query does it has any Sort Order?
 

moke123

AWF VIP
Local time
Today, 08:23
Joined
Jan 11, 2013
Messages
2,844
What version of access are you using?
In Microsoft Access 97 and later, the DoMenuItem method was replaced by the RunCommand method. The DoMenuItem method is included in this version of Access only for compatibility with previous versions.

I usually use a DAO addnew procedure and capture the new primary key. Then refresh the form and do a find first.
 

kevnaff

Member
Local time
Today, 13:23
Joined
Mar 25, 2021
Messages
121
What version of access are you using?


I usually use a DAO addnew procedure and capture the new primary key. Then refresh the form and do a find first.

Hi moke,

We are currently using Access365. This code probably hasn't been updated since it was first created in Access 97.

I will look in to this method as I'm not familiar with how to add a record by doing this.

Thanks
 

kevnaff

Member
Local time
Today, 13:23
Joined
Mar 25, 2021
Messages
121
do you have any Sorting in your Form or if you are using Query does it has any Sort Order?

Hi arnel

There is an order by, which orders it by a primary key. This means that the duplicated record will always have a higher autonumber primary key. I have tried removing the order by but it is still the same.

Maybe I'll look to what moke suggested and change the way the record is duplicated.

Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 28, 2001
Messages
22,799
Usually, you do better by using the method suggested by Moke123. "Last" in a shared DB can be incredibly ambiguous.
 

moke123

AWF VIP
Local time
Today, 08:23
Joined
Jan 11, 2013
Messages
2,844
Here's a quick example.
I usually return the new PKey value as a function so I can use that to write to more than one related table.
 

Attachments

  • Dups.accdb
    536 KB · Views: 113

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
16,394
you can use this code to clone your record:
Code:
' https://access-programmers.co.uk/forums/showthread.php?t=284992
'
' agp
' modified version
'
Public Function cloneRecord(ByRef Frm As Form, ByVal pkFieldName, ByVal pkValue As Variant) As Long ' returns the ID of the new record
    Dim clone As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim crit As String: crit = "[" & pkFieldName & "] = "
    Dim f As DAO.Field ' iterate through Fields
    Set rst = Frm.RecordsetClone
    Select Case True
    Case IsNumeric(pkValue)
        crit = crit & pkValue
    Case IsDate(pkValue)
        crit = crit & Format$(pkValue, "\#mm\/dd\/yyyy\#")
    Case Else
        crit = crit & "'" & Replace$(pkValue, "'", "''") & "'"
    End Select
    Set clone = rst.clone
    clone.FindFirst crit
    rst.AddNew
    With clone
        For Each f In .Fields
            If (f.Attributes And dbAutoIncrField) Then ' always skip the AutoNumber Primary Key
            Else
                rst.Fields(f.Name) = f
            End If
        Next f
    End With
    rst.Update ' update the main table with the new record
    Frm.Bookmark = rst.LastModified
    Set clone = Nothing
    Set rst = Nothing
End Function

to Use, on Click of Duplicate button:

private sub DupBtn_Click()
Call cloneRecord(Me, "pkFieldName", [pkValue])
end sub


Demo below:
 

Attachments

  • CloneMyRecord.accdb
    440 KB · Views: 106
Last edited:

MarkK

bit cruncher
Local time
Today, 05:23
Joined
Mar 17, 2004
Messages
7,913
Here's a little bit simpler clone row code. It just uses the recordset you pass in, copies the current row values to a collection, adds a row, copies the saved collection values, and sets the cloned row as current.
Code:
Sub CloneRow(rst As dao.Recordset)
    Dim c As New VBA.Collection
    Dim fld As dao.field
    
    For Each fld In rst.Fields      ' save current row data to collection
        c.Add fld.Value, fld.Name
    Next
        
    With rst
        .AddNew
        For Each fld In .Fields     ' retrieve data from collection
            If Not (fld.Attributes And dbAutoIncrField) = dbAutoIncrField Then ' skip key
                fld.Value = c(fld.Name)
            End If
        Next
        .Update
        .Bookmark = .LastModified
    End With
End Sub
Usage would be ...
Code:
Private Sub cmdDuplicateCurrentRow_Click()
    CloneRow Me.Recordset
End Sub
And as previously mentioned, cloned row is current.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
16,394
yet another variation:
Code:
Sub CloneRec(ByRef rs As dao.Recordset)
    Dim clone As dao.Recordset
    Dim fld As dao.Field
    
    Set clone = rs.clone
    clone.Bookmark = rs.Bookmark
    
    With rs
        .AddNew
        For Each fld In .Fields     ' retrieve data from collection
            If Not (fld.Attributes And dbAutoIncrField) = dbAutoIncrField Then ' skip key
                fld.Value = clone(fld.Name)
            End If
        Next
        .Update
        .Bookmark = .LastModified
    End With
    Set clone = Nothing
End Sub
 

Users who are viewing this thread

Top Bottom