• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1579
  • Last Modified:

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?
Jamie Roberts
Jamie Roberts
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
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.
Jamie RobertsAuthor Commented:
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?
Jamie RobertsAuthor Commented:
Actually, after much digging and researching the answer was quite simple.  I had specified an form.OrderBy = "WorkOrderID" for the form, but form.OrderByOn was set to false.  Once I set it to true, it started working as I had expected.  

Not sure how to close this question since I solved it myself...do I just delete?
Closed, 500 points refunded.
Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now