Finding Form.Recordsource (not query name)

  • Thread starter Thread starter Rick Brandt
  • Start date Start date
R

Rick Brandt

In an Access 2000 form, I'm trying to find its form.recordsource. Since
it's a query already defined in Access, it'll give me its name (eg.
qry_getEmployees).

How can I find the contents of this query (or any query) from within VBA?
Click to expand...

Dim strSQL as String
strSQL = CurrentDB.QueryDefs("YourQueryName").SQL

The form uses a query but I'd like to add additional filters to it.
Click to expand...


You "can" do this by modifying the SQL in the query, but you can also add a
filter to the form without touching the query at all. Just have the query
return a super-set of records and then apply filters to the form. There are
occasions where changing the SQL of the base RecordSource would be more
efficient (data processing wise), but if applying form filters gives you decent
performance I would stay with that as it is much simpler.

Another consideration is that form filters can easily be removed. Changes to
your Query's SQL are permanent. If you later want to remove the additional
criteria that you added, then you have to modify the SQL again to restore it to
its original state. Sometimes that is an advantage, other times that would be a
problem.

I most often modify a Query's SQL when it is a Pass-Through query to a server
database. In those cases I have my base SQL stored in a table. I start with
that and dynamically apply different WHERE clauses for each specific
circumstance and can use the stored SQL to return the query to its original
format if I need to.
 
In an Access 2000 form, I'm trying to find its form.recordsource. Since
it's a query already defined in Access, it'll give me its name (eg.
qry_getEmployees).

How can I find the contents of this query (or any query) from within VBA?
The form uses a query but I'd like to add additional filters to it.

Thanks.
 
However, if what you want is to add filters to the query, there
may be a better way than reading the SQL.

You can add a filter to the form,

or you can adjust the recordsource:

me.recordsource = "Select * from qry_getEmployees where idx = " & idx

(david)
 
Back
Top