RecordsetClone

[Guest]

I have a form with a subform "qMainCodeInYearSubform". A user selects a YEAR
in the ComboBox causing the AfterUpdate Event to run. This event causes the
subform to be requeried with the YEAR selected. Then with RECORDSETCLONE, I
loop through the records putting each record in a messagebox. It works for
the first time around but not the second. The EOF the second time around
stays TRUE. When another YEAR is selected I would like to loop again through
the new set of record results.

Thanks for any help.


Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Recordset, intI As Integer
Dim fld As Field

Set rst = Form_qMainCodeInYearSubform.RecordsetClone

Do While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Loop

rst.Close

End Sub

 

[Allen Browne]

The pointer may not automatically reset to the start, to MoveFirst before
your loop:

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do While Not.EOF
MsgBox !Maint_Code
.MoveNext
Loop
End If
End With

 

[Guest]

Thanks! I had to change it a little to get it to work.

With Form_qMainCodeInYearSubform.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
MsgBox .MAINT_CODE
.MoveNext
Loop
End If
End With

The pointer may not automatically reset to the start, to MoveFirst before
your loop:

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do While Not.EOF
MsgBox !Maint_Code
.MoveNext
Loop
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a subform "qMainCodeInYearSubform". A user selects a
YEAR
in the ComboBox causing the AfterUpdate Event to run. This event causes
the
subform to be requeried with the YEAR selected. Then with RECORDSETCLONE,
I
loop through the records putting each record in a messagebox. It works for
the first time around but not the second. The EOF the second time around
stays TRUE. When another YEAR is selected I would like to loop again
through
the new set of record results.

Thanks for any help.


Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Recordset, intI As Integer
Dim fld As Field

Set rst = Form_qMainCodeInYearSubform.RecordsetClone

Do While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Loop

rst.Close

End Sub