One thing I really like about the dynamic SQL approach to creating saved queries is that it allows me to re-use some base queries without having to parameterize them.
With a forms reference, the query is tightly linked to the form itself. That means one query for each form where you want to use that same syntax--with variable input for the form.
With a parameter query, the user has to supply the parameter via the popup input box. In that case, you do get to reuse the single query, but the user gets to participate actively by having to remember and enter valid values for the parameters in the popup.
Both methods work, and both have advantages and disadvantages.
Lately, I've been working with dynamic SQL statements and a Function Call to replace the WHERE clause in queries so that they become more portable. The trade-off here, of course, is that you still have to write VBA in each form where you plan to use the query. So, there are three approaches, each with advantages.
Here's how I go about this.
Create the base query, calling it something "qfrmCompany_src", with syntax like this.
"SELECT tblCompany.CompanyID, tblCompany.CompanyName
FROM tblCompany
WHERE tblCompany.CompanyID= 0;"
This parameter (0) should NOT return any records when run, unless you've used 0 as a Primary Key (ProjectID). If your primary keys are Autonumbers that won't happen. If it is the recordsource for a form, for example, the form will have no records when it opens, which I find useful at times.
Next create a function like this:
Public Function WhereIsIt(qryName As String, strCriteria As String) As String
Dim qdef As DAO.QueryDef
Dim intWhere As Integer
Dim intOrderby As Integer
Dim strSELECTClause As String
Dim strWHEREClause As String
Dim strORDERBYClause As String
Dim strSQL As String
Set qdef = CurrentDb.QueryDefs(qryName)
strSQL = qdef.SQL
intWhere = InStr(1, strSQL, "WHERE ")
If intWhere > 0 Then
strSELECTClause = Left(strSQL, intWhere - 1) & " "
Else
WhereIsIt = qryName
Exit Function
End If
intOrderby = InStr(intWhere + 8, strSQL, "ORDER BY")
If intOrderby > 0 Then
strORDERBYClause = " " & Right(strSQL, Len(strSQL) - intOrderby + 1)
Else
strORDERBYClause = ""
End If
strWHEREClause = " " & Mid(strSQL, intWhere, Len(strSQL) - intOrderby) & " "
strSQL = strSELECTClause & strCriteria & strORDERBYClause
qdef.SQL = strSQL
WhereIsIt = qryName
End Function
In the After Update event of the control on your form where you want to call the query with a different where clause, place the following:
Private Sub cboSelectCompany_AfterUpdate()
Me.RecordSource = WhereIsIt("qfrmCompany_src", "WHERE CompanyID =" & Me.cboSelectCompany)
End Sub
On any form, then, where you want to dynamically reuse this query with a parameter selected in a control on that form to filter the form's recordsource, insert this same syntax:
Me.RecordSource = WhereIsIt("qfrmCompany_src", "WHERE CompanyID =" & Me.cboSelectCompany)
Here's another way I use this technique to initialize a series of subforms on a main form. For example, If I call this from the form's open event, I pass in 0 as the lngProjectiD, or I can call it from one combo box on the main form and synchronize all of the subform's recordsources.
Public Function fInitializeFormRecordsources(lngProjectID As Long) As Boolean
WhereIsIt "qfrmProject_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
WhereIsIt "qfrmProjectNextStep_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
WhereIsIt "qfrmProjectMilestone_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
WhereIsIt "qfrmProjectDeliverable_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
WhereIsIt "qfrmProjectEfforts_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
WhereIsIt "qfrmProjectBudgetDispersals_src", " WHERE ProjectID = " & lngProjectID, "WHERE"
End Function
I'm still experimenting with this technique and finding some limitations, but so far, it looks like a good approach for some basic parameterization.
Feedback is welcome and encouraged. I'm sure there are issues I've not even thought about yet, so feel free to sort me out....
De tanto cantarle al amor y la vida
Me quede sin amor una noche de un día
De tanto jugar con quien yo más quería
Perdí sin querer lo mejor que tenía.
