Scanning records of form+subform

[il barbi]

I'd like to write in VBA a very simple operation that is usually manually
performed on a form, say frmA (built on table tblA), with an embedded
subform, say sfrmB, related to form frmB (built on table tblB). FrmA and
sfrmB are connected via IdA being the counter of tblA and a field in tblB.
Now the operation is:
for i=1 to "Number of records of frmA"
'operations on controls of frmA
for k=1 to "Number of records of frmB related to record i of frmA"
'operations on controls of sfrmB
next k
next i
Now I tried to write the code by operating 1) on form A referred to as Me
and 2) on recordset Me.RecordsetClone but in both cases I was not able to
correctly write the inner loop on the records of smskB
I apologize since this seems to be a very trivial problem
someone can help?
il barbi

 

[Allen Browne]

When you move the pointer to the RecordsetClone of the main form, it doesn't
change record in the main form. Consequently, the related records don't
change in the subform, so you don't get the desired related records
reported.

Assuming Access 2000 or later, you could use the Recordset of the main form.
Something like this:

Dim rsMain As DAO.Recordset
Dim rsSub As DAO.Recordset

If Me.Dirty Then Me.Dirty = False
Set rsMain = Me.Recordset
If rsMain.RecordCount > 0 Then
rsMain.MoveFirst
Do While Not rsMain.EOF
Debug.Print rsMain![MainID] & ": ";
Set rsSub = Me.[Sub1].Form.RecordsetClone
If rsSub.RecordCount > 0 Then
rsSub.MoveFirst
Do While Not rsSub.EOF
Debug.Print rsSub!I[SubID] & ", ";
rsSub.MoveNext
Loop
End If
Debug.Print
Set rsSub = Nothing
rsMain.MoveNext
Loop
End If
Set rsMain = Nothing