Solved

Remove filter from Access form but stay on current record

Posted on 2008-10-08
9
1,067 Views
Last Modified: 2012-06-27
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
Comment
Question by:ksilvoso
  • 4
  • 3
9 Comments
 
LVL 1

Author Comment

by:ksilvoso
ID: 22667999
I forgot to mention I am applying the filter to a field in the subform.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 22680350
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
 

Expert Comment

by:AMixMaster
ID: 22697005
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
 
LVL 61

Expert Comment

by:mbizup
ID: 22699455
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

by:AMixMaster
ID: 22708982
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
 
LVL 61

Expert Comment

by:mbizup
ID: 22711421
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
 

Expert Comment

by:AMixMaster
ID: 22722456
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
 
LVL 61

Expert Comment

by:mbizup
ID: 22726693
Allen,

Understood :-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now