Access 2003: Loop Through Records

Hello,

I need some assistance with the following code.

Private Sub GradeAttempting()
If Nz(Forms.Members.Ready, True) = True And Nz(Forms.Members.Active, True) = 
True Then
    With Forms.Members.GradeAttempting
        .SetFocus
        .ListIndex = .ListIndex + 1
    End With
End If
End Sub

This updates a combobox, "GradeAttempting" on a main form, "Members". This 
works but only for the Member page that I have the form open to. To update 
the rest of the records I have to manually navigate through all members 
pages and 
run the code for each page. 

What I want is have the code cycle through all of the records and update 
GradeAttempting and I also need the checkbox, "Ready", cleared on each page.

What code do I need to add to accomplish this? Thanks.

-- 
Regards,
Chris
0
Utf
5/21/2010 2:29:00 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
1832 Views

Similar Articles

[PageSpeed] 53

eckert1961 wrote:
>Hello,
>
>I need some assistance with the following code.
>
>Private Sub GradeAttempting()
>If Nz(Forms.Members.Ready, True) = True And Nz(Forms.Members.Active, True) = 
>True Then
>    With Forms.Members.GradeAttempting
>        .SetFocus
>        .ListIndex = .ListIndex + 1
>    End With
>End If
>End Sub
>
>This updates a combobox, "GradeAttempting" on a main form, "Members". This 
>works but only for the Member page that I have the form open to. To update 
>the rest of the records I have to manually navigate through all members 
>pages and 
>run the code for each page. 
>
>What I want is have the code cycle through all of the records and update 
>GradeAttempting and I also need the checkbox, "Ready", cleared on each page.
>
>What code do I need to add to accomplish this? Thanks.
>
Using the listindex property of your combobox is a bad idea, because you
can't run a simple update query against your table this way.  You would have
to loop through all the records in your table in code through your form and
update each record individually.  Bad plan.  SQL is a set based language, so
look for a set-based solution.  put the controlsource of the combo/listbox in
a table in order (add an indexed numeric field to your table so that the
values your looping through essentially have a surrogate index)... e.g.

Value         IndexValue
a                1
d                2
j                 3
e                4

then you can add some constant to your IndexValue and return the right
corresponding value in your query. (just use a join).

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
5/21/2010 5:09:12 AM
Hi Pieter,

Thank you for taking the time to respond. I believe that I already have what 
you've outlined but I'm not sure if I'm implementing it correctly. Currently 
the control source for the combobox on my Members form is a field in my 
Members table, GradeAttempting. For the row source I'm using the following 
query.

SELECT GradeTypes.*, GradeTypes.GradeTypeID, GradeTypes.GradeType FROM 
GradeTypes ORDER BY GradeTypes.GradeTypeID, GradeTypes.GradeType DESC;

The table GradeTypes has a field, SortField, which is what you recommended. 
As I noted, I'm not sure if my implementation is correct. If not, what 
changes would you recommend?

Additionally, would an update query be able to select the next GradeType? If 
so, how?
-- 
Regards,
Chris


"PieterLinden via AccessMonster.com" wrote:

> eckert1961 wrote:
> >Hello,
> >
> >I need some assistance with the following code.
> >
> >Private Sub GradeAttempting()
> >If Nz(Forms.Members.Ready, True) = True And Nz(Forms.Members.Active, True) = 
> >True Then
> >    With Forms.Members.GradeAttempting
> >        .SetFocus
> >        .ListIndex = .ListIndex + 1
> >    End With
> >End If
> >End Sub
> >
> >This updates a combobox, "GradeAttempting" on a main form, "Members". This 
> >works but only for the Member page that I have the form open to. To update 
> >the rest of the records I have to manually navigate through all members 
> >pages and 
> >run the code for each page. 
> >
> >What I want is have the code cycle through all of the records and update 
> >GradeAttempting and I also need the checkbox, "Ready", cleared on each page.
> >
> >What code do I need to add to accomplish this? Thanks.
> >
> Using the listindex property of your combobox is a bad idea, because you
> can't run a simple update query against your table this way.  You would have
> to loop through all the records in your table in code through your form and
> update each record individually.  Bad plan.  SQL is a set based language, so
> look for a set-based solution.  put the controlsource of the combo/listbox in
> a table in order (add an indexed numeric field to your table so that the
> values your looping through essentially have a surrogate index)... e.g.
> 
> Value         IndexValue
> a                1
> d                2
> j                 3
> e                4
> 
> then you can add some constant to your IndexValue and return the right
> corresponding value in your query. (just use a join).
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
5/21/2010 3:21:01 PM
Reply:

Similar Artilces: