Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1299
  • Last Modified:

Remove filter from Access form but stay on current record

I have an Access form that I would like to allow people to filter using the built in filter by selection.  My form is set up so that the grants table is the subform of the applicant form.  The built in filter works fine when I apply it but when the filter is removed the form jumps to the first record in the applicants table.  I would like it to stay on the current record.  I have already set the properties of all forms to current record but for some reason, removing the filter seems to bypass this.
Thanks,
Karen
0
ksilvoso
Asked:
ksilvoso
  • 4
  • 3
1 Solution
 
ksilvosoAuthor Commented:
I forgot to mention I am applying the filter to a field in the subform.
0
 
mbizupCommented:
Hi ksilvoso,

You can't prevent Access from jumping back to the first record when the filter is removed.  That is how Access works.

You can however *return* to the selection that the user made while setting the filter.

The ApplyFilter event is triggered when the user applies a filter.  You can save the ID (or other reference) to the current record during this event.

Using this saved ID, you can then use the form's Current Event to return to that filtered record.

Read through the documentation in the code below for more details...
Option Compare Database
Option Explicit
 
' Declare a module level variable to hold the ID of the filtered record
Dim varFilterID As Variant
 
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    ' Upon Applying the filter, save the ID of the current selection
    varFilterID = Me.ID
End Sub
 
Private Sub Form_Current()
    Dim rs As DAO.Recordset
    Dim strCriteria As String
    Set rs = Me.RecordsetClone
    
    ' If the filter is OFF, and we have a stored ID from the filter setting,
    ' use standard bookmark code to return to the record selected for the filter.
    If Me.FilterOn = False Then
        If Nz(varFilterID) <> "" Then
            strCriteria = "ID = " & varFilterID
            rs.FindFirst strCriteria
            If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
            ' Reset the stored filterID so that the code does not keep forcing this
            ' selection as the user navigates through the records.
            varFilterID = Null
        End If
    End If
    
    Set rs = Nothing
                    
End Sub

Open in new window

0
 
AMixMasterCommented:
I found your solution quickly with a search.  It is exactly what I was looking for.
 I tried the code and recieved the error "Method or Data Member not found".  The code stops on .ID in the =Me.ID declaration.  
Cheers
Allen
PS: if another user butts in to the Ask side of the question, do you get extra points for solving that query? :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mbizupCommented:
That said, I think that the Asker could benefit from that information as well, and that is good information to have in this thread.

ID is a generic fieldname I used to represent the Primary Key or other "defining" field for that form's recordsource. You would have to replace ID with whatever fieldname you have given your primary key (for example, employeeID).
0
 
AMixMasterCommented:
I changed:   varFilterID = Me.ID   to:
  varFilterOrderID = Me!OrderID  (don't forget the "bang" "!" !)
I also changed all the instances of ID to my field OrderID
It worked!
Thanks
Allen
soooo....
0
 
mbizupCommented:
Allen -

<soooo.... >

Not much more to do here - you can't "Accept" an answer or award points on someone else's question ;-)

No word here from the OP, so the question may have been abandoned.


<It worked!>

I'm glad this helped you out on your own project.

Things work out in strange ways sometimes :-)


mb
0
 
AMixMasterCommented:
mb
I don't know what happened. This is your solution.  I simply added the clarification and pointed out the missing bang to be helpful :)
Cheers
Allen
0
 
mbizupCommented:
Allen,

Understood :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now