Loop through ADO recordset, update records, and count updated reco



Is the following possible:
1) I want to my code to start at the first record in a ADO recordset.

2) For each record determine if two fields called "Gender" and "Position"
meet specific criteria, if so update a field called "Deploy" in that record
to true.

3) Then I would like for the code to count that record it just updated the
"Deploy" field to True. When the count of field "Deploy" equals a specific
number (X) I then want the code to exit the loop, but if the count is not
equal to X I want it to not move to the next record.

Overall, I need to does the same code for 6 different combinations of Gender
and Position so I am going to have to nest several loops within each other.
Also if the variable X is equal to 0 I want the code to exit the Do Loop go
start executing the next Do Loop. The code is being run from a form.

Any suggestions?

What I have so far I can't figure out how to count the records that were
just updated and to properly format the If statement to exit the loop if X =
0 or continue updating records;

Dim rst As ADODB.Recordset
Dim X As Integer
Dim Y As Integer

Set rst = New ADODB.Recordset
rst.Open "DEPLOYMENT_RANKING", CurrentProject.Connection, adOpenDynamic, _

X = Nz(Me.maleplayers.Value, "0")

Y = 0



If rst.Fields("Gender").Value = "M" And rst.Fields("Position").Value =
"Guard" Then
rst.Update "Deploy", True and Y = 1

Y = Y + 1


Loop Until X = Y

MsgBox Y & " " & "Deployed"

End Sub



Michel Walsh

Dim counter AS long
if x <> 0 then ' skip if x == 0
counter = 0 ' reset the counter
rst.moveFirst ' position to first record
Do While ((counter<10) AND (NOT rst.EOF))
If rst.Fields("Gender").Value = "M" And _
rst.Fields("Position").Value = "Guard" Then

rst.Fields("Deployed") = true

counter = counter + 1

end if ' if rst.Fields("Gender") ...
Loop ' Do until (counter < 10 ) AND ...

end if ' if x <> 0 then

could do.

Vanderghast, Access MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question