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


"Amp" <Amp@discussions.microsoft.com> wrote in message
news:0E89630F-2475-48CD-BF2F-5088FB96810F@microsoft.com...
> 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