Sort Order on Form

[Aklfishboy]

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]

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]

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]

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]

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]

use Query to sort your Combo.
on its Row Source:

select field1, field2 from table1 Order by field1

 

[The_Doc_Man]

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]

Right, thanks for the help, got it working! Appreciate the explanation Doc, understand now. Cheers

 

[theDBguy]

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]

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.