How to retrieve actual recordset from a subform

[Mihail]

Hello !
I have a form: frmMain. In this form a subform: frmSubform.
When I navigate in frmMain the records in frmSubform are changed adequately.
Well done !

Now, I wish to create a string by concatenate the records (from a certain field) from this subform.
Can I do that by using directly what I have (what I see) in the frmSubform for a certain record in frmMain ?
That means to access only the filtered records from the subform.

Thank you !

[limweizhong]

Try using the child form's recordset, e.g. Child0.Form.RecordSet, but it might actually affect the record selector's position in the child form. Best way might be to do a separate query.

Edit: Yes it is really the displayed recordset in the form, so you might want to call Child0.Form.MoveFirst before and after your cycling through all the records.

[Mihail]

I have trying that and work OK (I can print in Immediate Window what I need) but, indeed, the record selector is affected in the child form.
So I think that I need to use a clone for this recordset. But I don't know how to obtain this clone.

Here is the current code I use:

Private Sub cmdPreviewSketch_Click()
Dim Rst As DAO.Recordset
Set Rst = Form_frmStatments_subform.Recordset
    Rst.MoveFirst
    Do While Not Rst.EOF()
        With Rst
            Debug.Print !Statment 'Statment is the field name
            .MoveNext
        End With
    Loop
Exit Sub

[Mihail]

Solved somehow.
The subform must be requery after using it's recordset.
So, one more statement after line #10:

Form_frmStatments_subform.Requery

But I still think that using a clone is a better solution. Can this be achieved ?

[limweizhong]

I think you might have the RecordsetClone property if you are using Access 2007. You can try that in place of the Recordset property...

Or you can Set Rst=Form_frmStatments_subform.Recordset.Clone().

Edit: OK, RecordsetClone is also available in previous versions of Access.

[Mihail]

Yes !!!!!! That is. Thank you limweizhong !
As you I am looking for Subform.Recordset.Clone too but it is Subform.RecordsetClone. Uffff !

Dim Rst As DAO.Recordset
Set Rst = Form_SubformName.RecordsetClone
    Rst.MoveFirst
    Do While Not Rst.EOF()
        With Rst
            'Do something
            .MoveNext
        End With
    Loop