Solved

Records on Microsoft Access form not sorted correctly

Posted on 2007-12-06
7
1,526 Views
Last Modified: 2013-11-28
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
Comment
Question by:Jamie Roberts
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20423497
>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
 
LVL 6

Expert Comment

by:messen1975
ID: 20423941
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
 

Author Comment

by:Jamie Roberts
ID: 20444506
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
 

Accepted Solution

by:
Jamie Roberts earned 0 total points
ID: 20568116
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
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20644274
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now