Access 2007 VBA: use Form.Filter in query parameters

Question

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.

It doesn't have to be a pain, if you use the form's recordsource as base of the select query. — Andre
– Andre, Commented Oct 20, 2015 at 14:51
Thanks, but source isn't the problem and I can't see any method to get an SQL WHERE string from the recordsource property. — Absinthe
– Absinthe, Commented Oct 20, 2015 at 15:26

Andre · Accepted Answer · 2015-10-20 18:27:34Z

2

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.

edited Oct 20, 2015 at 18:27

answered Oct 20, 2015 at 18:06

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andre Over a year ago

True, but then for this use case the solution is to have a single query as RecordSource - if it currently isn't, then create a named query from the current RecordSource. @HansUp

Absinthe Over a year ago

Didn't realize you could use Me.Filter directly as the WHERE ( myQuerydef.SQL = "SELECT field1, field3 FROM " & Me.Recordsource & " WHERE " & Me.Filter). Thanks, new one for the toolkit.

Collectives™ on Stack Overflow

Access 2007 VBA: use Form.Filter in query parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related