Access Form: Return to current record when applying or removing a sort or filter

I have a Access 2010 form which users are allowed to sort or filter on any field (using standard Access buttons on the ribbon). When the user applies or removes a sort or filter, I want the form to return to the record that is currently displayed -- or to go to the first record of the filtered data if the current record has been filtered out.

Does anyone have code they routinely use for this, or can they suggest how it should be coded. I'm thinking it depends on the Current and ApplyFilter events, but I can't work out the logic that I need.
colevalleygirlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you need to create a variable to store the id of the current record, then look for it using FindFirst and bookmark it after you reset your filter.

post the codes you are using for your filtering
0
mbizupCommented:
Try this -

Define a module level variable in your form's code right under the Option Compare Database line:

Dim mlngRecordNo as long

Open in new window

In the current event:

mlngRecordNo = Me.YourPrimaryKeyField

Open in new window


And in the apply filter event:

Dim rs as dao.recordset
set rs = me.recordsetclone
rs.findfirst "YourPrimaryKeyField =" & mlngRecordNo
if rs.nomatch = false then
    me.bookmark = rs.bookmark
end if
set rs = nothing

Open in new window

0
Rey Obrero (Capricorn1)Commented:
;-)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
Rey, Great minds think alike.  That's essentially what you suggested in the first comment.  :)
___


I'm not sure if the method I posted will work -  try it ... it depends on the order of form Events.

But if you are using access's built-in buttons for filtering, you don't have the flexibility of integrating this code directly into filtering code as you would have if you had rolled your own filtering solution.

0
Rey Obrero (Capricorn1)Commented:
hence, my request of posting the codes in use....
0
colevalleygirlAuthor Commented:
My current code for handling the filter events looks like this.

In form module declarations:

private mvarFilterID as variant

Open in new window


In Form_Open:

myForm.FilterOn = False
mvarFilterID = Null

Open in new window


In Form_Current:

 
If myForm.FilterOn = True Then
  If Not IsNull(mvarFilterID) Then
    'Form has been filtered -- return to record when filter event applied if it exists in the data set
    GoToNavRecord (mvarFilterID) 'This routine moves the form to the specified record if it exists in the form's filtered dataset
     mvarFilterID = Null
  End If
Else
  mvarFilterID = myForm!MasterID
End If

Open in new window


In Form_ApplyFilter:

 
If myForm.FilterOn = True Then
   mvarFilterID = myForm!MasterID
Else
   'Move to the last recorded current record
    If Not IsNull(mvarFilterID) Then
      'Form has been unfiltered -- return to record when filter event applied if it exists in the data set
       GoToNavRecord (mvarFilterID)
       mvarFilterID = Null
     End If
End If

Open in new window


Which works when applying the filter but not when removing it.
0
colevalleygirlAuthor Commented:
As far as I can tell, when removing the filters, the Current Event is triggered first (with FilterOn = False) by the form moving to the first record in the dataset. The code dutifully records mvarFilterID as that first record. Then the ApplyFilter event triggers and moves the form to... that first record.
0
mbizupCommented:
Did you try the code I suggested?
0
colevalleygirlAuthor Commented:
It works when applying the filter. However, it doesn't work when removing the filter, because the Current event is triggered by the move to the first record BEFORE the ApplyFilter event is triggered.
0
mbizupCommented:
Try thise modifications to the code I suggested.

Module level variables:
Dim mlngRecordNo as long 
Dim mblHasBeenFiltered as boolean

Open in new window


Current Event:

Dim rs as DAO.Recordset
if Me.FilterOn = False and mblHasBeenFiltered = TRUE then
     mblHasBeenFiltered = False
     set rs = me.recordsetclone
     rs.findfirst "YourPrimaryKeyField =" & mlngRecordNo
     if rs.nomatch = false then
        me.bookmark = rs.bookmark
     end if
     set rs = nothing 

End IF
    
mlngRecordNo = Me.YourPrimaryKeyField 

Open in new window



Apply Filter:

Dim rs as dao.recordset
If Me.FilterOn = True
set rs = me.recordsetclone
rs.findfirst "YourPrimaryKeyField =" & mlngRecordNo
if rs.nomatch = false then
    me.bookmark = rs.bookmark
end if
set rs = nothing 
mblHasBeenFiltered = TRUE
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
You can probably replace the recordset blocks with calls to your own function for moving to the records...
0
colevalleygirlAuthor Commented:
Sorry; that goes to the first record both when filtering and unfiltering.

0
colevalleygirlAuthor Commented:
OK, this seems to work for filters

Form_Open:

myForm.FilterOn = False
mvarFilterID = Null
mbooHasBeenFiltered = False

Open in new window


Current:

If myForm.FilterOn <> mBooHasBeenFiltered Then
   mBooHasBeenFiltered = Not mBooHasBeenFiltered
   'Navigate to mvarFilterID here
End If
mvarFilterID = myForm.MasterID 'Update current record

Open in new window


Apply_Filter: No code!

Now to work out what events are triggered in what sequence for sort.
0
mbizupCommented:
Go ahead and accept your own answer to close the question if this one is resolved...
0
colevalleygirlAuthor Commented:
mbizup provided the germ of the approach that I needed to solve this.
0
colevalleygirlAuthor Commented:
Thanks for the assist.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.