Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Records on Microsoft Access form not sorted correctly

Posted on 2007-12-06
7
1,539 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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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