Link to home
Start Free TrialLog in
Avatar of Chi Is Current
Chi Is CurrentFlag for United States of America

asked on

Change Sort Field on Form When Filtering is Toggled OFF / ON

Hello -
Attempting to change the field being sorted when I turn filtering off / on.
Tried variations on:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

If Me.FilterOn = True Then

Me.OrderBy = "lcpStockSym"
Me.OrderByOn = True

    Else
   
Me.OrderBy = "lcpTrans"
Me.OrderByOn = True

    End If
   
End Sub

lcpTrans is an auto number field
lcpStockSym is a different field

I'd appreciate your illumination.

Thank you,  Jacob
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

How are you applying and removing the filter?
Avatar of Chi Is Current

ASKER

Thank you for your reply, peter57r

Setting it w/ Advanced -> Filter by Form
Then -> Toggle Filter
Your code looks fine.
When do you want to set filter ON/OFF?
I toggle the filter on/off using the "toggle Filter" button on the ribbon (access 2010).
That is fine: Code to set filter, and code in Form_ApplyFilter event.

Private Sub SetFilter_Click()
    Me.Filter = "aid > '" & 4 & "'"
End Sub

Open in new window

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
   If Me.FilterOn Then
        Me.OrderBy = "aid"
        Me.OrderByOn = True
    Else
        Me.OrderBy = "f1"
        Me.OrderByOn = True
    End If
End Sub

Open in new window

Jacob

I am having the same problem - I can't get the Order By statement to take effect when the filter is cleared, although the Order By is working when I set the filter.

I have moved the code to the current event procedure, because the applyfilter event does not seem to fire on if you clear the filter on the right-click menu.

Edit:  No it's not even as consistent as I am suggesting above. Sometimes something happens and sometimes nothing happens.  I' baffled at the moment.
If your filter is always the same, you can add a Toggle button to your form. Here's the code you'd use if you name your control tglFilter.

    If Me.tglFilter Then
        Me.Filter = "aid > '" & 4 & "'"
        Me.FilterOn = True
        Me.OrderBy = "aid"
        Me.OrderByOn = True
    Else
        Me.FilterOn = False
        Me.OrderBy = "f1"
        Me.OrderByOn = True
    End If

Open in new window

Ron

Have you tested your code?
I have tried lots of examples here using code I think should work, but none of them do.

The only success I have had is moving the code into the current event and then you have to change records to force the code to run effectively.    In other attempts I can make the code run down either branch of the if..then..else but it even though it steps through the sort commands the form does not change to refelect the sort order.

This area appears to be quite unreliable and certainly doesn't work as described in Help/msdn.
Peter, I just tested it and it works fine with my data.  I'm not sure if the OP has something else with the database that may be preventing this from working.  

Ron
Which event are you using?
Well the code I gave was intended for a toggle button so I use its OnClick event.
Ah - i see- I can make it work with buttons as well.
I can'r make it work properly just by applying a run-time filter though.

Using the form_current event,  I can succesfully detect whether a filter is On or not, and I have code to sort using two different fields, depending on the result..

But what I'm finding is that although the code runs, the visible sort sequence does not change unless I then move to a different record.  If I do move to a different record then the correct sequence is applied.  I think I might try a refresh after the sort setting, see if that does anything.

Edit-- yes that does it.  I'll post again with full code.
So Jacob , in the end all you have to do is this...
Use the Form Current event and this code..

Private Sub Form_Current()
If Me.FilterOn = True Then
    Me.OrderBy = "lcpStockSym"
    Me.OrderByOn = True
Else
    Me.OrderBy = "lcpTrans"
    Me.OrderByOn = True
End If
Me.Refresh
End Sub
THANK YOU FOR YOUR REPLIES!!!  I NEED TO HAVE DOWN TIME.  WCB TOMORROW.

MANY THANKS!!!

JACOB
Thank you for your suggestions.

I appreciate hearing that you also experienced the problem using this w/ the On Apply Filter event - I was beginning to loose it!

When I use the code you recommend immediately above (peter57r) in the On Current event, the record cannot be navigated away from, using record navigation at the bottom of the form. (!)  When filter is either on or off.

BTW, filters are always different, created w/ filter by form.

Hmmmmmm.  I imagine there may be some property setting I have overlooked that is creating this, though the form is mostly defaulted.... (?)  I'd be glad to email you a copy of the db.  It's small.  I'd prefer not to post it here.

Many Thanks, Jacob
You're right.  It's the same here.

I was so tied up in the sort squence I didn't look further.  Back to the drawing board.
OK I am really back to square one with this.  Solving the 'stuck on the current record' problem puts me back into the situation where the sorts don't work.

You can operate the sorts independently of the filter state by using either a toggle button or two separate buttons and you will get correct working results.   But at present I can't find a solution based on detection of the filter state and I don't have very much time available today.
Obviously, others might join in and provide a solution.
SOLUTION
Avatar of Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK. Of course, THIS is valuable information, even though it doesn't work as expected.

Thank you for your time and thoughts.

Best Regards, Jacob