Link to home
Start Free TrialLog in
Avatar of Jamie Roberts
Jamie RobertsFlag for United States of America

asked on

Records on Microsoft Access form not sorted correctly

I have an Access form and the datasource is set to a linked SQL server table.  Most of the time, the data is correctly sorted (form is for work orders, work order id is a primary key, int 4, identity, and also a clustered index).  Every now and again, we'll have a customer call in because their work orders are not sorting correctly.  We've looked at the form...done a compact/repair, replaced with a different front-end (mdb) but still not sorted right.  You'd expect to be able to scroll incrementally through a recordset (work order 1, 2, 3, etc) but then it may place work order 150 at the end of the recordset and 155, 156, 157, etc somewhere in the middle.   I'm not calling the data via a query - so the linked table is the datasource, not sorted in any way.  I have a sort order on the form set to WorkOrderID.  But still not in sequential order.

Any thoughts?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>I have an Access form and the datasource is set to a linked SQL server table.
If you want records from a table sorted in a specified fashion, then create a query which grabs data from that table, sorted however you wish, and use that as the form's RecordSource.

Tables are meent to hold data in specified formats, not in a specified order.
Avatar of messen1975
messen1975

Create a query into the table .. and the put the data source for the form to the query.  Ensure the query is sorted the way  you want it to be sorted.
Avatar of Jamie Roberts

ASKER

Can you help me understand why though?  A clustered index with no padding should keep records physically stored in the order of the clustered index, right?  Additionally, if I link in the table and specify an 'order by' on the form, shouldn't that act the same as a select query to sort the data?
ASKER CERTIFIED SOLUTION
Avatar of Jamie Roberts
Jamie Roberts
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator