Loop through Record Source for form in code?

[Judy Ward]

On frmMain I have several list boxes. When the user clicks the button, "Find
DSSFs by Criteria", I create a query "qryFindDSSF" through code and then open
"frmFindDSSF" with the query as the Record Source.

My user wants to perform an action on all the records he can see in this
form. I can just build a query the same way I did to select the records for
the form. But I was wondering, is there another way to do this? Is there a
way to work with the Record Set for the form?

Thank you for your help,
Judy

 

[Ken Snell MVP]

Use the RecordsetClone of the form:

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.NameOfField.Value = "Somevalue"
.MoveNext
Loop
End

 

[Judy Ward]

Ken,

Thank you for your help. I'm not quite there yet.

This is my exact code (copied and pasted):
Private Sub cmdTest_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
Debug.Print Me.ID
.MoveNext
Loop
End With
End Sub

There are 20 records in the RecordSet, all with a different ID. The output
of the Debug statements is 20 different lines, but all of the same ID (the ID
of the first record). I did try printing other fields (just in case, but got
the same result). For some reason I am not looping through all the records.
What am I doing wrong?

Thank you,
Judy

 

[John W. Vinson]

Ken,

Thank you for your help. I'm not quite there yet.

This is my exact code (copied and pasted):
Private Sub cmdTest_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
Debug.Print Me.ID
.MoveNext
Loop
End With
End Sub

There are 20 records in the RecordSet, all with a different ID. The output
of the Debug statements is 20 different lines, but all of the same ID (the ID
of the first record). I did try printing other fields (just in case, but got
the same result). For some reason I am not looping through all the records.
What am I doing wrong?

Referring to Me.ID - the currently displayed ID on the form - rather than just
..ID - the ID in the RecordsetClone.

 

[Ken Snell MVP]

What John says is right:

Private Sub cmdTest_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
Debug.Print .ID
.MoveNext
Loop
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[David W. Fenton]

Use the RecordsetClone of the form:

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.NameOfField.Value = "Somevalue"
.MoveNext
Loop
End

While that's how to do what was asked, I'd disagree that it's the
best way to accomplish the task. I'd run a SQL update and then
requery the form. It will be *much* more efficient.

 

[Judy Ward]

Thank you both very much for your help. This is what I needed to know to
make progress.

What John says is right:

Private Sub cmdTest_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
Debug.Print .ID
.MoveNext
Loop
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/