Access 2003: Loop Through Records

[eckert1961]

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.

 

[PieterLinden via AccessMonster.com]

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).

 

[eckert1961]

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?