• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1575
  • 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?
0
Jamie Roberts
Asked:
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.
0
 
messen1975Commented:
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.
0
 
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?
0
 
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?
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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