Copy Previous Row - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Copy Previous Row

Tony Roberts

2 years ago

I wanted to copy the previous row in a coninuous form and went to AI for help, but the code fails at Me.Controls(rs.Fields(i).Name).Value = rs.Fields(i).Value. Any ideas?

Details

Private Sub btnCopyPrevious_Click()

Dim rs As DAO.Recordset
    Dim i As Integer

    ' Set the recordset to the form's recordset
    Set rs = Me.RecordsetClone

    ' Move to the previous record
    rs.Bookmark = Me.Bookmark
    rs.MovePrevious

    ' Check if it's not the first record
    If Not rs.BOF Then
        ' Loop through each field and copy the value
        For i = 0 To rs.Fields.Count - 1
            Me.Controls(rs.Fields(i).Name).Value = rs.Fields(i).Value
        Next i
    End If

    ' Clean up
    Set rs = Nothing

End Sub

Kevin Robertson @Reply

2 years ago

Give this code a go.

Details

    Dim rs As Recordset, F As Field



    Set rs = Me.RecordsetClone



    ' Check if there's more than one record (current and previous)

    If rs.RecordCount > 1 Then

        rs.Bookmark = Me.Bookmark ' Sync clone with current record

        

        ' Check if the current record is not the first one

        If rs.AbsolutePosition > 0 Then

            ' Move to the previous record

            rs.MovePrevious

    

            DoCmd.GoToRecord , , acNewRec

            

            ' Copy values from the previous record into the current form fields

            For Each F In rs.Fields

                ' Skip AutoNumber or ID fields

                If Not F.Name Like "*ID" And Not F.Attributes And dbAutoIncrField Then

                    Me(F.Name).Value = rs(F.Name)

                End If

            Next

        End If

    End If

    

    ' Clean up

    rs.Close

    Set rs = Nothing

	Tony Roberts OP @Reply 2 years ago
Thanks Kevin, produces error message, can't assign a value to this object Me(F.Name).Value = rs(F.Name)

	Kevin Robertson @Reply 2 years ago
Is your recordset updateable?

	John Campbell @Reply 2 years ago
Believe it or not, but there is a wizard that at least starts this process. Open Form>Select cmd button>wizard>duplicate record

	Tony Roberts OP @Reply 2 years ago
Yes, it is updateable. Thanks John, I will also have a look at the wizard.

	Tony Roberts OP @Reply 2 years ago
John, cmd wizard works fine, thanks! I would still like to see if I can get the VBA code to work as not a fan of macros.

	Alex Hedley @Reply 2 years ago
Just use the convert from macro to VBA in the ribbon

	John Campbell @Reply 2 years ago
Yes, what Alex said. In design of form >See upper right hand option "Convert Form's Macro to Visual Basic"

	Tony Roberts OP @Reply 2 years ago
That was my first thought, but for this macro, the option is not there! How very strange? Really apprciate your support.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View