Go to next Record via certain field

[waq963]

Hi, Basically i have a set of records for student data and i use a NExt command button to show the next student using this: rstStudent.MoveNext. Which is fine but is it possible to MoveNExt by a Certain field(E.g. Age) as it always goes to the next student using the Primary Key Field? Thanks.

 

[John Big Booty]

Firstly you will need to resort the data.

So for example if you want to step through the students by age use the following;

    Me.OrderBy = "StudAge" [COLOR="Green"]'Replace with the field name that contains the student age. By default this will sort in ascending order to sort by descending order use [B]"StudAge, DESC"[/B][/COLOR]
    Me.OrderByOn = True
    Me.Refresh

Now when you use;

DoCmd.GoToRecord , , acNext

The command will step through the records by age rather than the order of the ID, which it is currently doing.

 

[John Big Booty]

Here's a sample DB

Also in my first piece of code the line Me.Refresh is redundant.

 

[missinglinq]

Don't you hate when that happens?

 

[John Big Booty]

Absolutely

 

[waq963]

I cant seem to adapr your code to mine. This is what i have it goes to the next record just not via the criteria i want? Thanks.
Code:
Private Sub cmdNext_Click()
If Not rstQ.EOF Then
Me.OrderBy = rstQ("Q No")
Me.OrderByOn = True
rstQ.MoveNext
End If
If Not rstQ.EOF Then
Call showrecords
'this procedure decalres a recordset to poulate textboxes on the form
Else
MsgBox "End of recordset"
rstQ.MoveLast
End If
End Sub

 

[John Big Booty]

Have you tested the OrderBy portion of your code in isolation to ensure that it is having the desired effect?

 

[waq963]

Yes, it didn't have the desired effect?? I'm stumped.

 

[John Big Booty]

Are you able to post a copy of your DB?

 

[waq963]

John, i figured out how to get the code working with what i have already and your code. So i do now achive the desired result. Thanks alot for your help mate, it is uch appreciated.m

 

[John Big Booty]

Glad you got it sorted