Where is the Order of this Form Set?

[TheScullster]

I have inherited a database and have one machine that regularly seems to
screw up the front end!
Developed on access2000 front end then moved to Access (part of Office 2003)
with back end moved from NT4 to Windows Server 2003.
Don't really expect that the platforms are an issue here, these problems did
not appear immediately the changes were made.

Anyway back to the story line.

The database stores drawing numbers, titles and fixed attributes in one
table and job specific issue dates etc in another table.
The form comprises main and subform showing info from the above tables
respectively.

Problem: every now and then, one PC which is used almost entirely for
drawing data input seems to screw up the front end. The main form suddenly
stops showing the drawing numbers in alpha numeric order.
The thing is I don't see where this order is being set in the first place.
The main form is based on a table rather than an ordered query and the order
by property is not set in the main form properties.

Question:

Where else could this order be set and what could be causing this PC to lose
the order on this form?
I should point out that this is the only PC that will be giving this form
serious hammer in edit mode.

TIA

Phil

 

[Joan Wild]

You haven't specifically stated this, but it sounds as though you have
multiple users using the same frontend on the server. You should give each
user a copy of the frontend on their workstation.

If would also ensure that every user is up to date with all service packs
for both Windows and Office 2003 and Jet 4.0.

As for how the order can be reset, a user can change the sort order on any
field in form view (unless you have removed that menu item/toolbar item).

 

[Baz]

I have inherited a database and have one machine that regularly seems to
screw up the front end!
Developed on access2000 front end then moved to Access (part of Office 2003)
with back end moved from NT4 to Windows Server 2003.
Don't really expect that the platforms are an issue here, these problems did
not appear immediately the changes were made.

Anyway back to the story line.

The database stores drawing numbers, titles and fixed attributes in one
table and job specific issue dates etc in another table.
The form comprises main and subform showing info from the above tables
respectively.

Problem: every now and then, one PC which is used almost entirely for
drawing data input seems to screw up the front end. The main form suddenly
stops showing the drawing numbers in alpha numeric order.
The thing is I don't see where this order is being set in the first place.
The main form is based on a table rather than an ordered query and the order
by property is not set in the main form properties.

Question:

Where else could this order be set and what could be causing this PC to lose
the order on this form?
I should point out that this is the only PC that will be giving this form
serious hammer in edit mode.

TIA

Phil

If I were you I would change the the form's recordsource to be an ordered
query. Although Access is pretty consistent about generally returning
un-ordered results in the same order, you certainly shouldn't rely on it.
The golden rule is, if you want your results in a specific order, say so in
your query!

 

[John Vinson]

The main form is based on a table rather than an ordered query and the order
by property is not set in the main form properties.

In that case, the order of records is essentially unpredictable. Since
you don't tell Access what order you want, the records will be
presented in whatever order the query optimizer finds convenient; this
may depend on the order of records on the disk, the order of primary
key, or who knows what else.

If you want records in order, base the form on a Query sorting them in
order. If you don't specify an order... don't complain when you don't
get one!

John W. Vinson[MVP]

 

[Guest]

Open the form in design view and click on menu VIEW - Properties View. Look
at the Order By: This will tell you. This can be change by anyone using
the form by clicking in a field they wish to sort by and clicking on the icon
in the tool bar A-Z or Z-A.
Like John said it is best to use a query for the order. In the query the
order can include multiple fields such as date, catalogue number, color, etc.

 

[TheScullster]

Thanks to all respondents.

As noted in the initial post, the "order by" property is not set on the main
form.
The form is based on a table not a query.
And yet this form always returns records in alphanumeric order on all PCs
(yes multiple front ends on clients and back end on server). Just this one
PC that throws a wobbler every 3 months or so. This one PC accessing this
form for editing intensively.

Surely this means that the order or records must be controlled somewhere
else, otherwise wouldn't it just show records in "last entered" order or no
order at all?

Phil

 

[Baz]

Thanks to all respondents.

As noted in the initial post, the "order by" property is not set on the main
form.
The form is based on a table not a query.
And yet this form always returns records in alphanumeric order on all PCs
(yes multiple front ends on clients and back end on server). Just this one
PC that throws a wobbler every 3 months or so. This one PC accessing this
form for editing intensively.

Surely this means that the order or records must be controlled somewhere
else, otherwise wouldn't it just show records in "last entered" order or no
order at all?

Phil

Not necessarily, no. As I said before, Access is generally quite consistent
about these things where no order is specified, but you CANNOT rely on it!
Anyway, why worry about it when you can almost certainly resolve it by
changing the form's recordsource to something like:

SELECT * FROM sometable ORDER BY somefield

 

[John Vinson]

Surely this means that the order or records must be controlled somewhere
else, otherwise wouldn't it just show records in "last entered" order or no
order at all?

They are being displayed with "no order at all" - that is to say, in
an arbitrary and uncontrollable order which differs from one machine
to the next, perhaps because the query optimizer is working with
different data.

Just use a Query with an order by term as the recordsource of the
form, rather than basing the form on the table. It'll work just fine,
it'll be just as fast (if the sort field is indexed), and it will
reliably sort the data as you want it sorted.

John W. Vinson[MVP]