Chi Is Current
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
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
How are you applying and removing the filter?
ASKER
Thank you for your reply, peter57r
Setting it w/ Advanced -> Filter by Form
Then -> Toggle Filter
Setting it w/ Advanced -> Filter by Form
Then -> Toggle Filter
Your code looks fine.
When do you want to set filter ON/OFF?
When do you want to set filter ON/OFF?
ASKER
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
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
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.
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
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.
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
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.
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
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
ASKER
THANK YOU FOR YOUR REPLIES!!! I NEED TO HAVE DOWN TIME. WCB TOMORROW.
MANY THANKS!!!
JACOB
MANY THANKS!!!
JACOB
ASKER
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you for your time and thoughts.
Best Regards, Jacob