Trying to loop through a recordsets in Access

I'm trying to get a loop going based on my VB code below in Access.
I've tested the code by selecting an individual record set and
clicking the command 5 button. It works. Now instead of clicking on
each individual record set, I want to loop through all the records in
my table.

Fields - W2KAcct, FPAcct, RCIAcct, WrkGrpEml are all Yes/No boxes.
Basically,
if a group = PQ or CS, then certain boxes like W2KAcct or FPAcct get
checked.

Private Sub Command5_Click()

If [WorkTbl]![Group] = "PQ" Or [WorkTbl]![Group] = "VPQ" Then
[WorkTbl]![W2KAcct] = True
[WorkTbl]![FPAcct] = True

ElseIf [WorkTbl]![Group] = "CS" Then
[WorkTbl]![W2KAcct] = True
[WorkTbl]![FPAcct] = True
[WorkTbl]![WrkGrpEml] = True
[WorkTbl]![RCIAcct] = True
End If
End Sub

re: Trying to loop through a recordsets in Access

"Mike Wilson" <mike.wilson8@comcast.net> wrote in message
news:e283d477.0311242110.5a2d0b44@posting.google.c om...[color=blue]
> I'm trying to get a loop going based on my VB code below in Access.
> I've tested the code by selecting an individual record set and
> clicking the command 5 button. It works. Now instead of clicking on
> each individual record set, I want to loop through all the records in
> my table.
>
> Fields - W2KAcct, FPAcct, RCIAcct, WrkGrpEml are all Yes/No boxes.
> Basically,
> if a group = PQ or CS, then certain boxes like W2KAcct or FPAcct get
> checked.[/color]

Two ways off the top of my head.
One is by using DAO and the form's recordsetclone.

The other follows and is an extension of what you have started.

<code>
Private Sub Command5_Click()

DoCmd.GoToRecord acDataForm, Me.Name, acFirst

Do
If Not Me.NewRecord Then
If [WorkTbl]![Group] = "PQ" Or [WorkTbl]![Group] = "VPQ" Then
[WorkTbl]![W2KAcct] = True
[WorkTbl]![FPAcct] = True

ElseIf [WorkTbl]![Group] = "CS" Then
[WorkTbl]![W2KAcct] = True
[WorkTbl]![FPAcct] = True
[WorkTbl]![WrkGrpEml] = True
[WorkTbl]![RCIAcct] = True
End If
Else Exit Do

DoCmd.GoToRecord acDataForm, Me.Name, acNext

Loop

End Sub
</code>

I'd probably use recordsetclone.

re: Trying to loop through a recordsets in Access

I believe it should be something like this:

Set rs = db.OpenRecordset(WorkTbl)
rs.MoveLast 'RecordCount is updated after moving to the last record
iRecCount = Rs.RecordCount
Rs.MoveFirst ' start at the top
For x = 1 to iRecCount
With Rs
If .Group = "PQ" Or .Group = "VPQ" Then
.W2KAcct = True
.FPAcct = True

ElseIf .Group = "CS" Then
.W2KAcct = True
.FPAcct = True
.WrkGrpEml = True
.RCIAcct = True
End If
.MoveNext 'Move to next record and loop
End With

Next x


mike.wilson8@comcast.net (Mike Wilson) wrote in message news:<e283d477.0311242110.5a2d0b44@posting.google. com>...[color=blue]
> I'm trying to get a loop going based on my VB code below in Access.
> I've tested the code by selecting an individual record set and
> clicking the command 5 button. It works. Now instead of clicking on
> each individual record set, I want to loop through all the records in
> my table.
>
> Fields - W2KAcct, FPAcct, RCIAcct, WrkGrpEml are all Yes/No boxes.
> Basically,
> if a group = PQ or CS, then certain boxes like W2KAcct or FPAcct get
> checked.
>
> Private Sub Command5_Click()
>
> If [WorkTbl]![Group] = "PQ" Or [WorkTbl]![Group] = "VPQ" Then
> [WorkTbl]![W2KAcct] = True
> [WorkTbl]![FPAcct] = True
>
> ElseIf [WorkTbl]![Group] = "CS" Then
> [WorkTbl]![W2KAcct] = True
> [WorkTbl]![FPAcct] = True
> [WorkTbl]![WrkGrpEml] = True
> [WorkTbl]![RCIAcct] = True
> End If
> End Sub[/color]

re: Trying to loop through a recordsets in Access

An update query based on each condition will also do the trick.

--
Bruce M. Thompson, Microsoft Access MVP
bthmpson@mvps.org (See the Access FAQ at http://www.mvps.org/access)[color=blue][color=green]
>> NO Email Please. Keep all communications[/color][/color]
within the newsgroups so that all might benefit.<<

"Mike Wilson" <mike.wilson8@comcast.net> wrote in message
news:e283d477.0311242110.5a2d0b44@posting.google.c om...[color=blue]
> I'm trying to get a loop going based on my VB code below in Access.
> I've tested the code by selecting an individual record set and
> clicking the command 5 button. It works. Now instead of clicking on
> each individual record set, I want to loop through all the records in
> my table.
>
> Fields - W2KAcct, FPAcct, RCIAcct, WrkGrpEml are all Yes/No boxes.
> Basically,
> if a group = PQ or CS, then certain boxes like W2KAcct or FPAcct get
> checked.
>
> Private Sub Command5_Click()
>
> If [WorkTbl]![Group] = "PQ" Or [WorkTbl]![Group] = "VPQ" Then
> [WorkTbl]![W2KAcct] = True
> [WorkTbl]![FPAcct] = True
>
> ElseIf [WorkTbl]![Group] = "CS" Then
> [WorkTbl]![W2KAcct] = True
> [WorkTbl]![FPAcct] = True
> [WorkTbl]![WrkGrpEml] = True
> [WorkTbl]![RCIAcct] = True
> End If
> End Sub[/color]