How to Remove Filter via Macro after Macro Lookup ??

[Will]

- I have a query that looks up selected data in a table.
- I have a form built on the query... showing only selected data for each
record in Datasheet View
- When I double click on a record in that Form it runs a Macro that opens
another Form that shows all the data fields for that record.

- The Macro "Filters" the records to find and show only the one we double
clicked on.

Problem: Once we look up the selected record we want to be able to go
backwards or forwards through the database by clicking the navigation arrows
at the bottom of the form... we want the "Filter" turned off.

But, when we click the Filter Off Icon the record being viewed changes... it
goes back to the first record the underlying querry found.

My Question: Can I modify the Macro to first lookup the record we want and
display that in the Form that shows all fields... and secondly removes the
Filter while not changing the record currently being viewed?

I really need some help on this one so thanks to anybody who can advise.

Will

 

[Allen Browne]

You have a couple of options, both better if you can use code.

It sounds like you want to *find* (move to) a record, rather than filtering
the form. If so, you could add an unbound combo box to the top of your form,
and use its AfterUpdate event to move to that record. When you add a combo
to your form (from the toolbox), there is a wizard that offers to do this
for you. Or you can follow the steps in this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If that is not what you want to do, then you could write some code to save
the primary key value of the selected record into a variable, then remove
the filter, then find that record again.

This example assumes an AutoNumber primary key field named "ID":

Dim varID As Variant

If Me.Dirty Then 'Save any edits in progress first.
Me.Dirty = False
End If

varID = Me.[ID] 'Remember the primary key value.
Me.FilterOn = False 'Remove the filter

If IsNull(varID) Then 'Must have been at a new record.
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
Else
With Me.RecordsetClone
.FindFirst "ID = " & varID
If .NoMatch Then
MsgBox "The previous record is no longer in the form."
Else
Me.Bookmark = .Bookmark
End If
End WIth
End If