?
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
Medium Priority
?
159 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
[X]
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
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 18

Accepted Solution

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

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

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 38

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 38

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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