One of the additional benefits I found in working with this technique is that I can construct forms that work equally well as subforms and as stand-alone forms, even though their recordsource is a parameterized query that would traditionally have had to be written like this:
SELECT tblProject.ProjectID, tblProject.ProjectName AS Name, tblProjectEffort.ProjectEffort AS Project, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime AS Started, tblProjectEffort.EffortStopTime AS Stopped, nz(DateDiff("n",[EffortStartTime],[EffortStopTime]),"NA") AS TotalMinutes
FROM tblProject INNER JOIN tblProjectEffort ON tblProject.ProjectID = tblProjectEffort.ProjectID
GROUP BY tblProject.ProjectID, tblProject.ProjectName, tblProjectEffort.ProjectEffort, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime, tblProjectEffort.EffortStopTime
HAVING tblProjectEffort.EffortDate=[Forms]![FrmMainMenu].[frmDailyProject]![cboWorkDate]
ORDER BY tblProject.ProjectID;
Here's a screen shot of the app where I used a combo box to filter the records to display. The SQL above refers to the combo box dropped down in this image.
What may not be so obvious in this screenshot is that the form is actually a subform in the main menu, although the HAVING Clause in the SQL statement reveals it to be the case.
HAVING tblProjectEffort.EffortDate=[Forms]![FrmMainMenu].[frmDailyProject]![cboWorkDate]
Therefore, iIf you need a similar query for a different form, you have to write it with a different forms reference:
SELECT tblProject.ProjectID, tblProject.ProjectName AS Name, tblProjectEffort.ProjectEffort AS Project, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime AS Started, tblProjectEffort.EffortStopTime AS Stopped, nz(DateDiff("n",[EffortStartTime],[EffortStopTime]),"NA") AS TotalMinutes
FROM tblProject INNER JOIN tblProjectEffort ON tblProject.ProjectID = tblProjectEffort.ProjectID
GROUP BY tblProject.ProjectID, tblProject.ProjectName, tblProjectEffort.ProjectEffort, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime, tblProjectEffort.EffortStopTime
HAVING tblProjectEffort.EffortDate=[Forms]![FrmMainMenu].[frmProjectWorkByDate]![cboWorkDate]
ORDER BY tblProject.ProjectID;
The result is a separate query for each form.
That's not the only problem with these "standard" queries. If the form for which this query returns a record set is a subform, it can't be opened outside the parent form to which the parameter refers because the combo box isn't available otherwise.
Abre Me la Puerta
Granted, it is not all that common to want to use a form both as a subform and as a stand-alone form, but dynamic parameterized queries open the door to a lot more flexibility.
Here's a second screen shot, showing the same form opened as a standalone form, with the same filtered recordset as the subform. And I promise you that I used a single query to produce both instances of the form.
Preguntale
You may well ask how that's possible. It wouldn't be with a standard forms-reference parameter query, would it? You'd have to have two forms, or at least two queries. But with a dynamic paramterized query it's not hard at all.
Here's how it works.
The actual SQL in my query starts out looking like this:
SELECT tblProject.ProjectID, tblProject.ProjectName AS Name, tblProjectEffort.ProjectEffort AS Project, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime AS Started, tblProjectEffort.EffortStopTime AS Stopped, nz(DateDiff("n",[EffortStartTime],[EffortStopTime]),"NA") AS TotalMinutes
FROM tblProject INNER JOIN tblProjectEffort ON tblProject.ProjectID = tblProjectEffort.ProjectID
GROUP BY tblProject.ProjectID, tblProject.ProjectName, tblProjectEffort.ProjectEffort, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime, tblProjectEffort.EffortStopTime
HAVING 0=0
ORDER BY tblProject.ProjectID;
The key here is that the "HAVING" clause must be defined in the base, or source query. I want the form to open, by default to show all records, so I couldn't apply a date filter in the base query. The criteria 0=0 is always true, and always returns all records. (If I wanted to open it to show NO records, my HAVING clause would be "HAVING 0=1" instead.)
That gives me a string pattern I can search on and perform a replace function that creates a new filter of different sorts, depending on what I want to do.
For example, in this form, it's just a straight date filter, which might look something like this:
HAVING tblProjectEffort.EffortDate = #8/6/2009#
But I could equally well substitute in:
HAVING tblProject.ProjectID = 1
or even:
HAVING nz(DateDiff("n",[EffortStartTime],[EffortStopTime]),"NA") = "90"
Which would return every work effort session that lasted exactly 90 minutes. As you may already know, using NZ here returns a string, not a number, so I have to wrap the criteria in the text delimiter.
I hope that you are starting to see the power of this technique. I can have as many ways to filter my form's recordset as I want, simply by swapping in a new "HAVING" clause.
How do I go about doing the swapping? Happy to show you that, too.
Here's the code for the After Update event of the combo box on the form.
Private Sub cboWorkDate_AfterUpdate()
If IsDate(Me.cboWorkDate) Then
Me.RecordSource = WhereIsIt("qfrmDailyProject_src", "HAVING tblProjectEffort.EffortDate=#" & Me.cboWorkDate & "#", "HAVING")
Else
Me.RecordSource = WhereIsIt("qfrmDailyProject_src", "HAVING 0=0", "HAVING")
End If
End Sub
It checks to see if the user selected one of the dates, or the first option, "ALL Dates" , which I make available in a union query for the combo box' rowsource.
SELECT tblProjectEffort.EffortDate, Format(EffortDate, "yyyymmdd") As SortDate
FROM tblProjectEffort
GROUP BY tblProjectEffort.EffortDate
UNION SELECT "ALL Dates" AS EffortDate, format(dateadd("yyyy",10,Date()), "yyyymmdd")As SortDate
FROM MsysObjects
ORDER BY SortDate DESC;
I'm Gonna Let it Shine
Pardon the detour, but I want to point out another of my tricks. I use a system table in the "all" option of UNION queries to ensure that at least one row is returned, even if there are no records in the source table yet. If I had used the following option instead, the combo box would have no rows in it when the source table is not yet populated. Like a lot of my tricks, I learned this one from a master many years ago and I've long since forgotten whom. The only way I can thank him or her is to pass it forward.
SELECT tblProjectEffort.EffortDate, Format(EffortDate, "yyyymmdd") As SortDate
FROM tblProjectEffort
GROUP BY tblProjectEffort.EffortDate
UNION SELECT "ALL Dates" AS EffortDate, format(dateadd("yyyy",10,Date()), "yyyymmdd")As SortDate
FROM tblProjectEffort
ORDER BY SortDate DESC;
Gimme Shelter
Let's get back to the function that does the heavy lifting here, WhereIsIt:
Public Function WhereIsIt(qryName As String, strCriteria As String, strType As String) As String
Dim qdef As DAO.QueryDef
Dim intWhere As Integer
Dim intHaving As Integer
Dim intOrderby As Integer
Dim intUNION As Integer
Dim strUNIONClause As String
Dim strSELECTClause As String
Dim strWHEREClause As String
Dim strORDERBYClause As String
Dim strSQL As String
Set qdef = CurrentDb.QueryDefs(qryName)
strSQL = qdef.SQL
Select Case strType
Case "WHERE"
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) & " "
Case "HAVING"
intHaving = InStr(1, strSQL, "HAVING ")
If intHaving > 0 Then
strSELECTClause = Left(strSQL, intHaving - 1) & " "
Else
WhereIsIt = qryName
Exit Function
End If
intOrderby = InStr(1, strSQL, "ORDER BY")
If intOrderby > 0 Then
strORDERBYClause = " " & Right(strSQL, Len(strSQL) - intOrderby + 1)
Else
strORDERBYClause = ""
End If
Case "Union"
intWhere = InStr(1, strSQL, "WHERE ")
If intWhere > 0 Then
strSELECTClause = Left(strSQL, intWhere - 1) & " "
Else
WhereIsIt = qryName
Exit Function
End If
intUNION = InStr(1, strSQL, "UNION ")
If intUNION > 0 Then
strUNIONClause = " " & Right(strSQL, Len(strSQL) - intUNION + 1) & " "
Else
strUNIONClause = ""
End If
Case "PARAMETER"
strSQL = Replace(strSQL, "[CHANGEIT]", strCriteria)
qdef.SQL = strSQL
WhereIsIt = qryName
Exit Function
End Select
strSQL = strSELECTClause & strCriteria & strUNIONClause & strORDERBYClause
qdef.SQL = strSQL
WhereIsIt = qryName
End Function
I'm only going to discuss one of the options here, although you can see there are several, selected by the CASE statement. The one we're interested in is the "HAVING" case.
I pass in three arguments
qryName As String
strCriteria As String
strType As String
The function takes the designated query, and swaps out the relevant criteria based on the type of clause (having or where).
The Instr() function locates the initial position in the HAVING clause. If it doesn't find it (which shouldn't happen, but, hey, nobody's perfect), it simply exits the function, leaving the query intact.
If the function finds a HAVING clause, then it starts looking for an ORDER BY clause. If it finds that clause, the function can parse out the pieces before the HAVING clause and after it. At the end of the function, the pieces are returned to the query's SQL statement, with the HAVING clause passed in as the second argument swapped into the string in place of the original.
As you can see in the function, I've worked out a few options, some more fully than others. I'm still looking at UNION queries, for example, because I'm not sure they can always be parsed this way. But that's fodder for a future blog, I suppose.
One option that I just recently started working on is to create a more generic query like this:
SELECT tblProject.ProjectID, tblProject.ProjectName AS Name, tblProjectEffort.ProjectEffort AS Project, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime AS Started, tblProjectEffort.EffortStopTime AS Stopped, nz(DateDiff("n",[EffortStartTime],[EffortStopTime]),"NA") AS TotalMinutes
FROM tblProject INNER JOIN tblProjectEffort ON tblProject.ProjectID = tblProjectEffort.ProjectID
GROUP BY tblProject.ProjectID, tblProject.ProjectName, tblProjectEffort.ProjectEffort, tblProjectEffort.EffortDate, tblProjectEffort.EffortStartTime, tblProjectEffort.EffortStopTime
[CHANGEIT]
ORDER BY tblProject.ProjectID;
That one would be parsed by the last case option in the function. While it promises to be more generic than any of the others, it also prevents me from ever using the "base" query as the recordsource for an object by itself, which is one of the attractions of this technique. I'm sure there's a way to get there, though, and I'll be back to tell you about it when I find it.
In the meantime, let's wrap up by observing that this form can be opened either as a subform or as a standalone form because it's recordsource will always be defined by the self-contained criteria returned by it's combo box.
Returning to the After Update Sub, note that the references are to Me, the form on which the code runs, no matter where or how that form is opened. And that, to me, is a very good thing. It makes theform independent. Us old cowboys are inordinately fond of independent.
If you want to look at the demo db where I used this, you can find it at Downloads at GPCData
Storms Never Last
Storms never last do they baby
Bad times all pass with the wind
Your hand in mine stills the thunder
And you make the sun want to shine.
Until next time.<
