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
153 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

803 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