RecordsetClone

[Guest]

I have the following code but when the it rusn again the EOF is true. It
should start over again from the first record.

Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Object
Set rst = Form_qMainCodeInYearSubform.RecordsetClone

While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Wend


rst.Close
Set rst = Nothing

End Sub


Thanks.

 

[Guest]

Your recordset declaration should be
Dim rst as DAO.Recordset

Not certain this will solve your problem but it might.

TomU

 

[Marshall Barton]

I have the following code but when the it rusn again the EOF is true. It
should start over again from the first record.

Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Object
Set rst = Form_qMainCodeInYearSubform.RecordsetClone

While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Wend


rst.Close
Set rst = Nothing

End Sub

What makes you think it should automatically reposition the
current record?

The RecordsetClone is a "behind-the-scenes" recordset, you
do not open it, you do not own it and you better not close
it.

Just add a rst.MoveFirst before the While statement.

(Note that even if the While/Wend construct is still allowed
for backward compatibility, you should use Do While/Loop
instead)

 

[Guest]

Notice this is run AFTERUPDATE of a ComboBox. Also notice the requery
"Me.qMainCodeInYear_subform.Requery". This updates the recordset for a
different YEAR. It works initially but the value for EOF stays TRUE.

I have the following code but when the it rusn again the EOF is true. It
should start over again from the first record.

Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Object
Set rst = Form_qMainCodeInYearSubform.RecordsetClone

While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Wend


rst.Close
Set rst = Nothing

End Sub

What makes you think it should automatically reposition the
current record?

The RecordsetClone is a "behind-the-scenes" recordset, you
do not open it, you do not own it and you better not close
it.

Just add a rst.MoveFirst before the While statement.

(Note that even if the While/Wend construct is still allowed
for backward compatibility, you should use Do While/Loop
instead)

 

[Marshall Barton]

You seem to be operating under a misunderstanding. The
RecordsetClone object is created for you the first time you
reference it, after that it is an independent recordset
object from the form's recordset. Well, that's not quite
right because requerying the form can invalidate the
RecordsetClone's current record. The CurrentRecord of the
two recordset objects are independent of each other (a major
purpose of the Clone method). Since the solution to your
problem is simple, why not just do it and move on to more
significant issues?

Add the MoveFirst before the loop and get rid of the Close.
--
Marsh
MVP [MS Access]

Notice this is run AFTERUPDATE of a ComboBox. Also notice the requery
"Me.qMainCodeInYear_subform.Requery". This updates the recordset for a
different YEAR. It works initially but the value for EOF stays TRUE.

I have the following code but when the it rusn again the EOF is true. It
should start over again from the first record.

Private Sub cmbYear_AfterUpdate()

Me.qMainCodeInYear_subform.Requery

Dim rst As Object
Set rst = Form_qMainCodeInYearSubform.RecordsetClone

While (Not (rst.EOF))

MsgBox rst![MAINT_CODE]
rst.MoveNext
Wend


rst.Close
Set rst = Nothing

End Sub

What makes you think it should automatically reposition the
current record?

The RecordsetClone is a "behind-the-scenes" recordset, you
do not open it, you do not own it and you better not close
it.

Just add a rst.MoveFirst before the While statement.

(Note that even if the While/Wend construct is still allowed
for backward compatibility, you should use Do While/Loop
instead)