Loop through Record Source for form in code?

  • Thread starter Thread starter Judy Ward
  • Start date Start date
J

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
 
Use the RecordsetClone of the form:

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.NameOfField.Value = "Somevalue"
.MoveNext
Loop
End
 
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
 
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?
Click to expand...

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

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

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.
 
Thank you both very much for your help. This is what I needed to know to
make progress.

Ken Snell MVP said:
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/
Click to expand...
 
Back
Top