Pass a Form Filter to a Query

[kenton.l.sparks@gmail.com]

Dear Access Colleagues:

I'm trying to pass the filter on a form (for example, one that's selected 25 of 100 records in the underlying table) to a query that, when run, will select the same 25 selected records. As near as I can tell, I want to take the "filter" property from the form and insert it into the filter property of the query. But so far, no luck on that. Any help will be appreciated.

Thanks,


Kent Sparks
Berwyn, PA

[pbaldy]

I believe you can do it opening another form or a report, using the wherecondition argument. I can't think of a way to filter a query. Most of us don't let users into tables or queries directly anyway.

[kenton.l.sparks@gmail.com]

Queries have a "Filter" property. I've put filters in it directly, but I really need to automate it so that the filtered datasset in the form is picked up and used by the query. End users wont touch this ... which is, in fact, why I need to do it this way.

Basically, I need to know how one would set the Query filter property = to the Form filter property. I've figured out that I can't do this with functions and the like. I need code for it.

Thanks

[pbaldy]

The only possibility that comes to mind is using a DAO QueryDef to manipulate the query.

[kennejd]

Here's some dao code that might work for you...I added it to a click event:

Private Sub cmdFilter_Click()
On Error Resume Next
Dim strFilter As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prp As DAO.Property

strFilter = Me.Filter

Set db = CurrentDb
Set qdf = db.QueryDefs("qEmployers")

With qdf

Set prp = qdf.Properties("Filter")
If Err Then
Set prp = .CreateProperty("Filter", dbText, strFilter)
.Properties.Append prp
Else
prp.Value = strFilter
End If

If strFilter = "" Then .Properties.Delete ("Filter")

End With

End Sub