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

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