Button Click Sort Listbox ASC/DESC on Form

[pwag]

Good Morning Pro's,

I'm using recordset behind an unbound button click event. I am looking to sort a list in a listbox First, Ascending, THEN, without loosing focus I would like to sort Descending on the very next click. How would I achieve this? What I have thusfar.

Thanks,

I realize the code isn't correct. It's demonstrating what I'm trying to achieve.

	Set db = CurrentDb
    If (1st click) Then
        mySql = " SELECT MyField From MyTable " _
                & " ORDER BY MyField ASC  "

    Else (2nd click) Then
        mySql = " SELECT MyField From MyTable " _
                & " ORDER BY MyField DESC  "
    End If
    
    Set rs = db.OpenRecordset(mySql, dbOpenSnapshot)

[NeoPa]

Make mySQL static or module level and set up the SQL code first in the default way.

On the click event use code similar to :

mySQL = Replace(Replace(Replace(mySQL, " ASC", " %%%%") _
                                     , " DESC", " ASC" _
                                     , " %%%%", " DESC")

[pwag]

Thanks NeoPa!

I'm getting close. I keep getting an "argument not optional" on my 2nd Replace Function. I'm unsure what the " %%%%" signifies.

 MySQL = " SELECT MyField FROM MyTable " _
                & " ORDER BY MyField ASC"
        
        mySqlSort = Replace(Replace(MySQL, " ASC", " DESC"), " ASC")

[NeoPa]

Possibly not so close :-(

1. The code setting MySQL originally must only be run once.
2. The variable MySQL must be set outside by, and included within, the Replace() calls.
3. There are three (3) stacked calls to Replace(). The idea is to switch from A to B OR from B to A. If it starts as A then switches to B in the first step then the next step, if it's to switch from B to A, will catch that one and switch it back again. " %%%%" is used as a place marker. It's only switched to B after any existing Bs have been switched to As.

[pwag]

Hi,

Thanks. I get the following code to switch from A to B one time but doesn't continue to the next step from B to A. Thanks for your breakdown. I tried without brackets as your example but it won't compile.

MySQL = Replace(Replace(Replace(MySQL, " ASC", " %%%%"), " DESC", " ASC"), " %%%%", " DESC")

[NeoPa]

Darn it. The closing parenthesis was missing from my example.

Otherwise, the code should work. Is it running as VBA or as SQL?

[pwag]

Hi,

The code is written in VBA. Yes, I meant to say "parenthesi s." I said "brackets," in the previous post. This code works for on sort change but gettting it to switch back and forth between ASC and DESC is still on the horizon.

[NeoPa]

OK. Why don't you show us all the relevant code together. We've had unrelated snippets across various posts, but not what you have now. It should work fine. Something's got lost in translation I expect.

[pwag]

I have developed a different solution. I positioned a check box behind the button. Alternating logic with this check box True/False, it fires ASC/Desc.

Thanks!
P