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

  • Thread starter Thread starter Amp
  • Start date Start date
A

Amp

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, _
adLockOptimistic

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

Y = 0


rst.MoveFirst

Do

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

Y = Y + 1

rst.MoveNext

Loop Until X = Y

MsgBox Y & " " & "Deployed"

End Sub
 
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.Edit
rst.Fields("Deployed") = true
rst.Update

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
 
Back
Top