Sort Order on Form (1 Viewer)

Aklfishboy

New member
Local time
Tomorrow, 12:23
Joined
Sep 13, 2021
Messages
4
Hi. I have read many answers to my problem.

I have a form based on a table, which I use to update said table. I have a combo box to find the right records to update. My issue is the data is not sorted by the order I want to see.

I understand the table is just large data repository. I have tried on the form to Order By on Load - doesn't work. I have tried setting up via query through the Query Builder to sort - doesn't work. I have tried to make/append a new table in the order I want and then adding an autonumber - doesn't work.

Now just about to throw my laptop out the window in frustration..... :).

Obviously doing something wrong.

Hoping some one can help.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
17,137
Hi. Welcome to AWF!

What is in the Record Source property of your form? Can you please show it to us?

If you create a query based on your table and add an ORDER BY clause to it, does the record appear in the order you want when you open the query?
 

Aklfishboy

New member
Local time
Tomorrow, 12:23
Joined
Sep 13, 2021
Messages
4
Hi, thanks for your reply. Excuse my ignorance, how do you wish me to show you the table? And second yes the query sorts to the order I want, but the combo box still shows in a random order. I've then deleted the combo box and reinserted a new one and then the form doesn't show the records I'm selecting in the new combo box....
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
17,137
Hi. I may have misunderstood your question. Were you trying to sort the records shown on the form? Or, were you trying to sort the items shown in the dropdown list of a combobox? Also, I was asking you to show us what is in the Record Source property of the Form. I wasn't asking you to show us your table. In any case, please clarify first what the actual question is by answering the question I asked at the beginning of this post. Thank you.
 

Aklfishboy

New member
Local time
Tomorrow, 12:23
Joined
Sep 13, 2021
Messages
4
1631511222246.png
Hi is this what you mean....this is my record source, in answer to your second question its the sorting in the combo box I wish to sort. Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:23
Joined
May 7, 2009
Messages
14,607
use Query to sort your Combo.
on its Row Source:

select field1, field2 from table1 Order by field1
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 28, 2001
Messages
21,175
Arnel's answer is correct. I'll amplify by explaining that the form has a record source. Even though the combo appears to have the same record source, it actually does not. The form's data comes from whatever is specified as .RecordSource but the combo box data comes from .RowSource, which is technically a different entity even if you filled in identical queries for both sources. They are like two channels that COULD be tuned to the same program - but they ARE different. So changing the order shown on the form doesn't affect the order shown in the combo, and vice versa.
 

Aklfishboy

New member
Local time
Tomorrow, 12:23
Joined
Sep 13, 2021
Messages
4
Right, thanks for the help, got it working! Appreciate the explanation Doc, understand now. Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:23
Joined
Oct 29, 2018
Messages
17,137
Right, thanks for the help, got it working! Appreciate the explanation Doc, understand now. Cheers
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:23
Joined
Feb 19, 2002
Messages
33,711
Also, tables and queries are unordered sets so if you want them to be in a specific order, you need to sort them so a query needs an Order By clause to maintain a specific order. Opening a table in DS view essentially runs a query. If you don't specifically sort the recordset, you gent what you get. Access fools people into thinking that their tables are actually sorted. Well that's true but it is not. When you compact a database, one of the tasks is that Access sorts EVERY table into PK order before rewriting it in the compacted db. So it always looks like your data is sorted. However, once you update/delete/add records, the order will be altered and unless you modify one of the early rows, you may never notice that row 103 got moved to the end of the table and 100 rows got added after that.
 

Users who are viewing this thread

Top Bottom