Move to Last Record after duplicating record

[kevnaff]

Hello All.

Our database allows a user to duplicate a record using the following 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.

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.

DoCmd.RunCommand acCmdRecordsGoToLast

Thanks All.

 

[arnelgp]

this is equivalent command (after duplicating):

docmd.GoToRecord,,acLast

 

[kevnaff]

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]

do you have any Sorting in your Form or if you are using Query does it has any Sort Order?

 

[moke123]

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]

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]

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]

Usually, you do better by using the method suggested by Moke123. "Last" in a shared DB can be incredibly ambiguous.

 

[moke123]

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.

 

[arnelgp]

you can use this code to clone your record:

' 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:

 

[MarkK]

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.

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 ...

Private Sub cmdDuplicateCurrentRow_Click()
    CloneRow Me.Recordset
End Sub

And as previously mentioned, cloned row is current.

 

[arnelgp]

yet another variation:

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