Reset filter, keep selection

[HyBry]

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..

[FishVal]

Hi, HyBry.

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

form module

Private blnFilterRemoved As Boolean
Private varID As Variant   'linked table unique field
 
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
 
    If ApplyType = 0 Then   'filter is being removed
        blnFilterRemoved = True
        varID = Me.varID
        'varID assumed to be a name of visible form control
        'linked to varID table field
    End If
 
End Sub
 
Private Sub Form_Current()
 
    If blnFilterRemoved Then
        blnFilterRemoved = False
        Me.varID.SetFocus
        DoCmd.FindRecord varID
    End If
 
End Sub
 

[HyBry]

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:

Private Sub Form_Current()
    If Me.FilterOn = True Then
        Dim valFN As String
        valFN = Me.FullName
        Me.FilterOn = False
        DoCmd.GoToControl "FullName"
        DoCmd.FindRecord valFN
    End If
End Sub
 

Does this seem OK or it can be problems in long run?

[FishVal]

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?

[HyBry]

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.

[FishVal]

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?

[HyBry]

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

[HyBry]

I need a bit more help on this please....
So I use this

    If Me.FilterOn = True Then
    Dim valFN As String
    valFN = Me.FullName
    Me.FilterOn = False
    DoCmd.GoToControl "FullName"
    DoCmd.FindRecord valFN
    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?

[FishVal]

Hi, HyBry.

The simplest answer is: close the form before open.

DoCmd.Close acForm, "[Your form name]"
 

[HyBry]

I wish I was as smart as You... it is so simple yet never entered my mind..
Thanks again :)