Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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

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
Kevin Smith
Asked:
Kevin Smith
  • 5
  • 5
  • 2
  • +1
1 Solution
 
SimonCommented:
One way is to send the search results to a table and use that as the listbox datasource.
0
 
Gustav BrockCIOCommented:
Replace the source of the listbox with pure SQL:

Select * From NameOfCurrentSourceQuery Order By FirstFieldToOrderBy, SecondFieldToOrderBy

/gustav
0
 
Kevin SmithAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Gustav BrockCIOCommented:
Then all you need is to modify the SQL.

/gustav
0
 
SimonCommented:
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
 
Kevin SmithAuthor Commented:
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
 
Kevin SmithAuthor Commented:
Sounds perfect Simon...I've never done it like that before, lemme look into creating a table like that.
0
 
SimonCommented:
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
 
Kevin SmithAuthor Commented:
What's the append syntax on the query in the form?
0
 
SimonCommented:
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
 
PatHartmanCommented:
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
 
SimonCommented:
@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
 
PatHartmanCommented:
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
 
Kevin SmithAuthor Commented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now