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
151 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:
SimonAdept 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
 
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:SimonAdept
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
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.

 
LVL 18

Expert Comment

by:SimonAdept
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:SimonAdept
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 34

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:SimonAdept
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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Viewers will learn how the fundamental information of how to create a table.

747 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

9 Experts available now in Live!

Get 1:1 Help Now