paste row on continuous form (1 Viewer)

[hs_1]

I wonder if there is a way to copy selected row on continuous form and paste it right below the copied record
I wrote a statement which copies and paste selected fields from the row and it works fine for me but then I have to sort the entire form to bring up the record below the one I copied
Thanks

Private Sub btnEHT_Copy_Record_Click()
Dim strSQL As String

strSQL = "INSERT INTO Tracking( EWP, Iso) " & _
"Select'" & Me!txtEWP & "', '" & Me!txtIso & "';"

DoCmd.RunSQL strSQL
DoCmd.RunCommand acCmdRefresh



End Sub

 

[MarkK]

I don't understand the problem. From your post it sounds like your insert works, and your sort works.

 

[hs_1]

Mark
I have over a thousand records in the form, if the record I copy is somewhere in the middle of the form the pasted one appears at the bottom.
After paste I have to sort the form, find the record I am interested in and edit it and this can be annoying. If I would be able to paste the record right below the one I am trying to copy without loosing focus it would save me a lot of time.
Thanks

 

[hs_1]

Mark
Sorry for misleading info. When I copy the record it doesn't appear at the bottom and frankly I don't know where it is till I sort the form scroll the records and find them 2 together somewhere in the middle of the form.
Tx

 

[MarkK]

I would do something like this (assuming your table has an autonumber primary key field called TrackingID)...

Private Sub btnEHT_Copy_Record_Click()
    Dim newID As Long
    
    newID = CopyRecord
    Me.Requery
    GoToID newID
End Sub

Sub GoToID(TrackingID As Long)
    With Me.RecordsetClone
        .FindFirst "TrackingID = " & TrackingID
        If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With
End Sub

Function CopyRecord() As Long
    With CurrentDb.OpenRecordset("Tracking")
        .AddNew
        !EWP = Me.txtEWP
        !ISO = Me.txtISO
        CopyRecord = !TrackingID
        .Update
        .Close
    End With
End Function

See what's going on there? And notice how the sub-routines CopyRecord() and GoToID() might be useful to other consumers at a later date!!

 

[hs_1]

Thanks Mark
Works like a charm
Henry