CODEWITHSUNDEEP
formsms-accessfiltervba
Absinthe
Absinthe

Reputation: 3391

Access 2007 VBA: use Form.Filter in query parameters

I'd like to create and open a select query based on the user's current form filters. I can do this in VBA by parsing the form's Me.Filter string, extracting the bits I need and building a WHERE statement. However, putting in the all the required logic, punctuation and syntax is going to be a pain.

So my question, before I do all that is: is there any existing function to do this?

Thanks.

Upvotes: 0

Views: 1567

Answers (1)

Andre
Andre

Reputation: 27634

Maybe my comment was a little cryptic.

Let's say the recordsource of your form is myQuery.

The form is filtered, Me.Filter = myQuery.field1 LIKE 'asdf*' AND myQuery.field2 = 42

So your select query is e.g. 

SELECT field1, field3 
FROM myQuery
WHERE myQuery.field1 LIKE 'asdf*' AND myQuery.field2 = 42

or 

myQuerydef.SQL = "SELECT field1, field3 FROM " & Me.Recordsource & _
    " WHERE " & Me.Filter

So I don't quite see where the problem is.

The answer to your question is no, there is no function - but you shouldn't need anything besides Me.Filter.

Edit as suggested by HansUp:

If the RecordSource of your form currently isn't a single query, but a SELECT statement, create a named query from that SELECT statement, and use that query as RecordSource.

Upvotes: 2

Related Questions