Moving to next rec using a Different Order than the Primary Key

[IzmTest]

I hope some of the knowledgeable folks here can answer this question:

When moving to the next record, the DoCmd seems to utilize the table's
primary key, as in

DoCmd.GoToRecord , , acNext

So if the Primary key is on the field MemberID, for example, that will
be the order employed for all such record moves (next, previous, etc).

However, sometimes I need to move to the next record using another
ordering scheme,
say Lastname, FirstName.

I've tried setting the form's OrderBy property with
Me.OrderBy= "tblMember.LastName,tblMember.FirstName"
and then invoking DoCmd.GoToRecord , , acNext but this seems to
have no effect.
(I have an index set on tblMember.LastName,tblMember.FirstName).

Is there a way to move to the next (or previous) record using an
alternate ordering instead of the primary key,
e,g. LastName,FirstName?

Thanks in advance for any help you can offer.

IzM

 

[Douglas J. Steele]

Create a query that has the desired order, and use the query as the form's
recordset, rather than the table itself.

You should never make any assumptions about the order of data in tables.

 

[missinglinq via AccessMonster.com]

And be aware that in the Query Design Grid, sort takes place on the fields
from left to right, so first you'd want your LastName field, then your
FirstName field.