Access Vba - How to set the current record of a recordset to an other recordset

0
votes

I fetch a recordset 

Do while not recset.eof 
Recset.movenext
Loop

and want to set the current record of the recordset to an other recordset with vba

Set rec2 = rec1.????

I tried

Set.Rec2 = recset.bookmark

But no success! I hope there is a way to set the current record to an other recordset easely

Thanks for your help

2
ms-accessvbarecordset

2 Answers

1
votes

There are numerous options, but without knowing your exact goal it's hard to know which one is best for you.

One option is opening up a recordsetclone:

Set rec2 = rec1.RecordsetClone 'Open a clone
rec2.Bookmark = rec1.Bookmark 'Move the clone to the same record

In that case, rec2 contains all data rec1 has, but is set to the same record.

Another option is using a filter:

rec1.Filter = "ID = " & rec1!ID 'Set a filter to the current record, assumes ID = primary key
Set rec2 = rec1.OpenRecordset 'Set rec2 to the filtered result, rec1 is still unfiltered
0
votes

My goal was export the current record of a recordset to Excel file. As written by Eric, it's impossible to set the current record to another recordset.

But copyFromRecordset has very interesting properties for solve my issue as you can see below CopyFromRecordset RecordSet , MaxRows , MaxColumns Full description here

So I tested this code and It works great

    Dim oRecSet As Recordset, oRecSetClone As Recordset
    Dim varBookmark As Variant

    Set objExcelApp = New Excel.Application
    objExcelApp.Visible = True
    Set wb = objExcelApp.Workbooks.Open("G:\Access\test.xlsx")
    Set ws = wb.Sheets(1)


    sSQL = "SELECT * FROM tbl"
    Set oRecSet = CurrentDb.OpenRecordset(sSQL)
    Set oRecSetClone = oRecSet.Clone

    Do While Not oRecSet.EOF
        Debug.Print i
        oRecSetClone.Bookmark = oRecSet.Bookmark
        ws.Range("A" & i).CopyFromRecordset oRecSetClone, 1
        oRecSet.MoveNext
    Loop

End Sub

Just one comment

I use bookmark because I noticed a strange behaviour when I Apply copyFromRecordSet rec,1. After this command, rec.movenext generates an error message: Nbr 3021 - No current record