MS Access: Query Reuse / Supplying Query Parameters Using VBA

Question

I have two very complex queries that are displayed on the same form and that use multiple parameters supplied by that Form. I would like to reuse the query in a VBA function and/or on another form. As such, I have two questions:

1. Is there a way to write the SQL statement such that a query dynamically determines the form to read its parameter from rather than having to specify the name of the form? E.g., Something along the line of Me!startDate, rather than Forms!myForm!startDate.

2. Is there a way to supply parameters to a query when opening it as a DAO RecordSet?

Answer 1

For the most part, Jet (Access) is not subject to the same injection problems that other databases experience, so it may suit to write the SQL using VBA and either update a query with the new sql, or set the form's record source.

Very roughly:

sSQL = "SELECT f1, f2 FROM tbl WHERE f3 = '" & Me.txt3 & "'"

CurrentDB.QueryDefs("aquery").SQL = sSQL

Alternatively, you can use parameters:

Query:

PARAMETERS txtCompany Text(150);

SELECT <...>
WHERE Company = txtCompany

Code:

Set qdf = db.QueryDefs("QueryName")

qdf.Parameters!txtCompany = Trim(frm!txtCompany)

Answer 2

I almost never define parameters or store references to form/report controls in saved QueryDefs. To me, those should be supplied at runtime where you use the saved QueryDef.

In general, I write my SQL on the fly in code, rather than using saved QueryDefs.

Also, keep in mind that you can set the Recordsource of a form in its OnOpen event, which means you can use conditions there to decide on what filtering you'd like for the specific purpose. This can be determined based on outside forms, or using the OpenArgs parameter of DoCmd.OpenForm.