Records become unsorted

J

Jodyk

I have a database that uses a subform and the records in the sub form will
get out of orded. This does not happen all the time. We have gone for a
couple months with no problem then all of the sudden the records get
scrambled. The database is on a network with four users using it 12hrs at a
time. We have tried to apply a sort to a unique number field but this does
not work. When you compact and repair the database the records line back up
in the correct order. This only last for a short while. The database was
built with Access 2003 and is currently running under Access 2003. Forgive me
if i have not supplied enought information, I am new at this.
 
V

Van T. Dinh

According to Relational Database Theory, Records are retrieved according to
whichever order the database engine thinks is the most efficient. I know
that Access will use some sort of default ordering to present the data
retrieved, usually the PK. However, if you want Records to be present in a
particular order, you _must_ specify the sort order in the DataSource (Query
/ SQL) of the Subform.
 
J

Jody K

Thanks, I will try this. Is it possibly for a sluggish network to cause this
kind of problem? Could that explain why it only happens some time?
 
6

'69 Camaro

Hi, Jody.
I have a database that uses a subform and the records in the sub form will
get out of orded.
Click to expand...

The records are retrieved from the tables in no particular order unless a
sort order is applied to the recordset. Most likely, at least one of the
users is applying a sort on one of the other columns, and then this sort
order is saved in the form. Other users who are sharing the same front end
will see this new sort order when they, too, open the form. Splitting the
database and placing a copy of the front end (forms, queries, modules, et
cetera) on each workstation linked to the back end (tables and relationships)
on the network server will eliminate the problem of users having to deal with
other people's arbitrary sort orders, and greatly reduce the risk of database
corruption from a shared database file.

Another thing you can do is create a query with the desired sort order and
use that as the Record Source for the subform. Whenever a user sorts the
records in a different sort order and saves that property, the only way to
remove it permanently is to have the user open the form in Design View and
open the Properties dialog window, then select the Data tab and delete the
"Order By" Property. Save the form and open it again in Form View and the
records will once again be sorted as the query in the form's Record Source is
sorted.

I remember that Access 97 allows one to programmatically (say, at the click
of a button or on the form Unload( ) event) remove the "Filter" and "Order
By" Properties that have been set via the GUI, but programmatically removing
these properties in Access 2003 only removes the application of the filter
and the sort order, but not the values in these form properties, so they need
to be removed manually.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
V

Van T. Dinh

Yes, it is possible that the network traffic may have something to do with
it.

I use ODBC-linked Tables from SQL Server 2000 Back-End and occasionally in
DatasheetView of the Table, some Records (tend to be Records I have just
recently added / edited) seem to appear first even though by the PK, they
should appear last.
 
J

Jody K

69 Camaro,

Thanks for the information! I tried your suggestions out over the weekend
and it seems to be working. I appreciate all of your help!
 
Top