By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Reset filter, keep selection

P: 40
I have a form which, depending from where it is opened, is filtered. I wish to have a way to remove filter so one has access to all records, but it is important that once filter is removed it does not jump to first record (It is single form). Is there a way to reset filter and keep the current record in form?
I hope this makes sense..
Oct 10 '07 #1
Share this Question
Share on Google+
9 Replies

Expert 2.5K+
P: 2,653
Hi, HyBry.

The following solution will work if you have visible control containing linked table primary key or other unique field.

form module
Expand|Select|Wrap|Line Numbers
  1. Private blnFilterRemoved As Boolean
  2. Private varID As Variant   'linked table unique field
  4. Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  6.     If ApplyType = 0 Then   'filter is being removed
  7.         blnFilterRemoved = True
  8.         varID = Me.varID
  9.         'varID assumed to be a name of visible form control
  10.         'linked to varID table field
  11.     End If
  13. End Sub
  15. Private Sub Form_Current()
  17.     If blnFilterRemoved Then
  18.         blnFilterRemoved = False
  19.         Me.varID.SetFocus
  20.         DoCmd.FindRecord varID
  21.     End If
  23. End Sub
Oct 10 '07 #2

P: 40
Thanks for the replay.. and sorry that mine is so late.
I did not fully understand the code you gave me and I did not get it to work, but it lead me to a different way to get it sorted:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If Me.FilterOn = True Then
  3.         Dim valFN As String
  4.         valFN = Me.FullName
  5.         Me.FilterOn = False
  6.         DoCmd.GoToControl "FullName"
  7.         DoCmd.FindRecord valFN
  8.     End If
  9. End Sub
Does this seem OK or it can be problems in long run?
Oct 11 '07 #3

Expert 2.5K+
P: 2,653
Hi, HyBry.

Did you test the code? It looks like it will remove filter immediately after setting but will move form focus to the first filtered record, provides just some kind of record search. Is that what you've actually wanted?
Oct 11 '07 #4

P: 40
Hi FishVal,

Yes that is what I wanted, but only way I could find to do it was to use filter as it I am opening a new form.
Oct 11 '07 #5

Expert 2.5K+
P: 2,653
The code is Ok but I'm not sure it is a best idea to put it in Form_Current.
Did you try to put the code to Form_Open or Form_Load event handler?
Oct 11 '07 #6

P: 40
khmm... good idea... I did not try that..
Thanks... I am sure that will be much better... and maybe faster..
As I am still quite new to this it takes me some time to figure out where what should go. :D
Oct 11 '07 #7

P: 40
I need a bit more help on this please....
So I use this
Expand|Select|Wrap|Line Numbers
  1.     If Me.FilterOn = True Then
  2.     Dim valFN As String
  3.     valFN = Me.FullName
  4.     Me.FilterOn = False
  5.     DoCmd.GoToControl "FullName"
  6.     DoCmd.FindRecord valFN
  7.     End If
in order to remove filter on the form in On_Load or On_Open events, and it works fine when the form is opened.
But if I click the button on the first form again, without closing target form first, it sets the filter again and as the form was already opened filter is not removed. I tried putting this in On_Current and that had different effect - it will work at first, when reloaded next time - no effect, reload again - filter removed, reload again - no effect and so on...
Any ideas how to solve this or the best bet is to make this modal (not preferred solution) so user is forced to close it?
Oct 12 '07 #8

Expert 2.5K+
P: 2,653
Hi, HyBry.

The simplest answer is: close the form before open.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acForm, "[Your form name]"
Oct 12 '07 #9

P: 40
I wish I was as smart as You... it is so simple yet never entered my mind..
Thanks again :)
Oct 12 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.