Solved

How can I change the sort order of an Access list box without losing the record source query?

Posted on 2015-01-19
14
156 Views
Last Modified: 2015-02-11
I have a search box with an after update event that gives a list box its record source. After the list box has been populated, I want to be able to sort the data by a particular field without losing the results of the search? I want it to sort the data that's now in the box instead of starting over with all data. How do I accomplish this?
0
Comment
Question by:Kevin Smith
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40558125
One way is to send the search results to a table and use that as the listbox datasource.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40558128
Replace the source of the listbox with pure SQL:

Select * From NameOfCurrentSourceQuery Order By FirstFieldToOrderBy, SecondFieldToOrderBy

/gustav
0
 

Author Comment

by:Kevin Smith
ID: 40558133
I have the source as sql...I actually have a bout 4 different search selections (guess I shoulda mentioned that, ha). So if my list starts with all colors and they select BLUE from a drop down, then click a button to order by what box the blue's are in, I want the list to remember that it's still just looking at the blue's.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40558138
Then all you need is to modify the SQL.

/gustav
0
 
LVL 18

Expert Comment

by:Simon
ID: 40558144
I thought the idea was just to sort the pre-filtered results. e.g. if you initial search returns 5 out of 500,000 records, you don't want to re-run the entire search. That is why I was suggesting a table to store the initial result set. You can then sort that much more quickly than re-running the SQL aginst the 500,0000 row table with different ORDER BY clause.
0
 

Author Comment

by:Kevin Smith
ID: 40558147
But how does it modify the sql after the query?

default list, everything
search1, find all of this color
search2, find everything in this box
search3, keyword search

Then my listbox populates. After (for example) I do search 2, I want to sort it by color but keep the search2 sql intact instead of just calling everything back up.
0
 

Author Comment

by:Kevin Smith
ID: 40558150
Sounds perfect Simon...I've never done it like that before, lemme look into creating a table like that.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40558167
Run your initial query ONCE as a make table query, into say tblLocalList

Then , in your searchbox After_update code, insert a line to clear the table first
delete * from tblLocalList
and then use an APPEND (INSERT INTO) query to re-populate it.

Your listbox would still use dynamic SQL, as in select * from tblLocalList ORDER BY whatever
0
 

Author Comment

by:Kevin Smith
ID: 40558212
What's the append syntax on the query in the form?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40558385
Ahh, I'm in a location without Access in front of me...

If the destination table has the same column order as the query, you can do

INSERT INTO tblLocalList
SELECT * from Tablename

(where the second line above is your original SELECT query)

Easiest way is to load your original SELECT query into the query builder and then change the query type to append, and choose your tblLocalList table as the destination, then look at the SQL view of the query.

NB the full append (INSERT INTO) syntax is to provide the column list in parenthesis after the name of the table

INSERT INTO TABLENAME (col1,col2,colx)
SELECT col1,col2,colx from ANOTHERTABLE

But you can shorten the syntax if your SELECT clause contains columns whose datatype and length all fit into the destination table. I only suggest the short form syntax where you have just created the destination table. Even better would be to check that your local table structure mirrors the column definitions from the original source table.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40559620
The SQL for your SECOND combo should be something like:

Select fld1, fld2, fld3, From yourtableorquery
WHERE fld2 = Forms!yourform!Combo!
Order By fld3;

Combo2 will then be sorted by fld3.

You  don't have to make a temporary table and you don't have to build SQL on the fly.  You can create stored querydefs.  Then in the AfterUpdate event of combo1, you requery combo2 to force the query to run again.
Me.combo2.Requery
0
 
LVL 18

Expert Comment

by:Simon
ID: 40559654
@PatHartman: I agree, they don't have to use a temporary table. I suggested that to avoid re-running the original query (potentially across the LAN) to make the dynamic sort on the 2nd control (the listbox) snappier. Whether or not it's a good approach for the asker will mainly depend on the cost of the initial query and how much re-sorting of the listbox is likely to be done.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40559730
You sort the combo's RowSource in the same query you use to populate it.  Every time combo1 has a new item selected, you need to rerun the query to populate combo2 and this is the query where you sort the combo2 RowSource.  You don't do it after the fact.  Also, type - ahead doesn't work if the combo isn't sorted by the first visible field so sorting combos "on the fly" on different columns doesn't work well.  Because of that, I sometimes use two combos in search situations.  One by client name and one by client ID for example.
0
 

Author Closing Comment

by:Kevin Smith
ID: 40603326
thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

808 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