Are We There Yet?
Actually, I think so. I really do. We still have to unload the car and carry a few bags and boxes into the house, but we're certainly sitting in the driveway.
I've done up a small demo with MS Access 2010 (beta) running on the SharePoint 2010 (beta) and shown it to a few folks. It runs on my employer's site, so I can't publish the URL publically, unfortunately. Still waiting for a few "spare minutes" to float by so I can set up my own SharePoint site. However, from first responses, I'd say this is the real thing.
What Have You Done for Me Lately?
Here's a scenario where I think this new Access/SharePoint technology makes great sense (and dollars, if you'll pardon the pun). Consider a centralized sales organization which operates a Home Office in a large West Coast City. They have a few local agents in several other states (Michigan, Florida, Tennessee). Each agent is exclusively assigned to follow up on leads that originate in his or her territory. This organization obtains leads via a very sophisticated Affinity Program direct mail program. The Access database (with a SQL Server backend) which manages this program sits on a workstation PC in their Home Office. All potential customers, mailings, leads, assigned agents, and sales are tracked "cradle to grave" in this database.
One factor has always been a challenge for them: lead distribution and reporting is mostly manual.
Someone in the Home Office has to rubber-band a stack of mailed-in lead cards and stuff them in an envelope to resend to the assigned agent. Sometimes they photocopy and email them as PDFs. Getting results back from Agents? Well, yes, they are good about reporting results, especially sales, but leads can, and do, end up in a local agent's hands for quite some time before they are eventually disposed of.
Feedback from agents is hardest to manage. Management wants to know what's happening with all those "In Process" leads.
Sometimes a Great Notion
So, tracking leads and lead status is where this new Access/SharePoint capability comes in. Here's how I picture it:
The lead card comes in and the Office Manager updates that mailing record to indicate a response was received. Then, he assigns that lead to the agent nearest to the customer's address by selecting from a drop down list of agents. All of this happens now, by the way. The next step is where Access on SharePoint can really improve their process. Instead of getting up to photocopy the lead card, or sticking it on a pile to mail to the agent at the end of the day (or tomorrow if she gets too busy), the Office Manager pushes a button on the screen that says "Post to Lead Tracker". Access uploads that new lead record, along with any others that have been added recently, to the SharePoint site and sends an email to the assigned agent to notify them a new lead is ready.
The agent gets the email, goes to the URL for his or her leads and sees a list of "In Process" leads in his lead bank, with the new one sitting at the top. A good agent won't even be waiting for that email--he'll be checking his Lead Bank a couple of times a day anyway.
And when the agent disposes of leads from her lead bank, all she needs to do is pop open her Lead Bank form on the SharePoint site and select the new status from a drop-down list. If it's a sale, of course, the paper work now has to be completed, the same as it is done now. The main difference, though, is that the Office Manager back in the Home Office knows, by opening her desktop Access FE, that the lead has been disposed of, and she can set a flag to watch for the paperwork from the agent if necessary. (There is one unfortunate consequence for the procrastinators among us: "It's in the mail" is no longer a valid excuse.)
No more snail mail. Not no more photocopies. And everyone knows what is happening at both ends of the process in real time.
Wanna Buy a Good Buggy Whip? Cheap?
Access on SharePoint just might help to make the US Postal Service even less relevant than it already is. There's still email though.
.
Sunday, November 8, 2009
Sunday, October 11, 2009
And Now For Something (Almost) Completely Different
I have a guest blogger today. Ben Clothier has agreed to let me post this article he recently wrote. Ben is an MS Access MVP as well as being a some-time colleague and full-time friend.
You'll find more of his excellent posts at Utter Access and at Access Word Forums.
Ben writes on the use of Parameterized Queries to achieve the results I've recently described with other techniques involving dynamic SQL. I'll leave it to you to compare the two approaches and figure out how best to adapt each to your needs. I'm aware of advantages in both approaches, as well as limitations.
============================================
Assumptions:
We have an employee table from which we want to retrieve a personnel record derived from a given Primary key. Access provides us with a very convenient way of parameterizing the query by supporting references to controls on forms such as this:
SELECT *
FROM tblEmployees
WHERE empID = Forms!frmEmployees!empID;
This works out very well when the query is intended to live and die with this specific form and this approach is very commonly used in defining a recordsource for a report.
But references are only good when there's actually something to reference. Thus, the above query will definitely fail should the form frmEmployees not be open at time the query is called. We also run into problems of reusability. Surely the need to reference a employee record based on the primary key can be used in more than one places. Should we then write each query for each possible form reference? That would be a maintenance problem. Thus we may want to consider using parameters in more general forms.
Consider this syntax, found in a Parameterized Query:
PARAMETERS lempID INT;
SELECT *
FROM tblEmployees
WHERE empID = [lempID];
Now the query is disassociated from any references to any other objects and can run on its own two feet. Of course when you run it, Access will, as usual, present a little parameter input dialog. Therefore, sometimes people like to do something like this:
PARAMETERS [Enter an Employee ID];
This will work okay for simple use, but sometime we don't want to enter the paramter via a simple dialog; perhaps we already have a combobox on a form and want to make use of it. No problem; we can use VBA to fill in this for us.
With CurrentDb.QueryDefs("qryEmployee")
.Parameters("lempID") = Me.MyComboBox
Set frm.Recordset = .OpenRecordset(dbOpenDynaset)
End With
With this code snippet we can make use of a complete pop up form or subform that is filtered by the selection made in MyCombobox.
We certainly can make use of the techniques for action queries:
PARAMETERS lempID INT;
DELETE FROM tblEmployees
WHERE empID = [lempID];
With CurrentDb.QueryDefs("qdlEmployee")
.Parameters("lempID") = Me.MyCombobox
.Execute dbFailOnError
End With
As can be seen, using parameters is much more straightforward with any action queries and provides an convenient means for reusing the same query for several different parameters that may be chosen by user at runtime.
String Sanitizing
One more benefit is that there's no need to perform any string sanitizing as is required when writing a dynamic query. Suppose, for example, an user entered this value to pass into the query as a parameter:
Mike's and Al's "Good" Cars
In a dynamic query, we would be facing lot of headache in ensuring that the quotes are correctly delimited and escaped, with one of several variants:
'Mike''s and Al''s "Good Cars'
"Mike's And Al's ""Good Cars"""
Chr(34) & "Mike's And Al's " & Chr(34) & "Good" & Chr(34) & " Cars"
But in the code based on a parameterized query:
Parameters("sParam") = Me.MyTextbox
it's always parsed correctly with no help from us!
Maintainability
One more technique. This isn't strictly related to the parameter query but pertains to maintainability. I like to put parameter queries in a function or property procedure....
Something like this:
Property GetPersonnelRecord(EmpID As Long) As DAO.Recordset
Static q As DAO.QueryDef
If q Is Nothing Then
Set q = CurrentDb.QueryDefs("qryEmployees")
End If
With q
.Parameters("EmpID") = EmpID
Set GetPersonnelRecord = .OpenRecordset (dbOpenSnapshot)
End With
End Property
The reason for doing it this way is to provide an interface for VBA into individual queries and make the code maintainable especially in regards to queries that are reused in many different places.
But here's a more important reason. Suppose that after years of doing the above, we had to change the requirement and partition the employee table between active employees and past employees due to data storage restrictions and performance reasons. With a consistent interface as given above, it's a snap to add a 2nd parameter to identify whether the employee is active or not, update the property procedure to enter the 2nd parameter. Hit the compile and the compiler will find missing arguments in all places, which is far more accurate than using Find & Replace to track down all uses of the same queries all over in the code base.
Thus, parameter queries have lot to offer in terms of code maintainability and documentation in exchange for a little investment in some extra VBA coding that otherwise wouldn't be necessary with control references or dynamic queries.
Limitations
Of course, it goes without saying that they do have their limitations. Many people working on various RDBMS have had tripped over this innocuous looking parameter (adapted into Jet SQL):
PARAMETERS MyParam TEXT;
SELECT *
FROM aTable
WHERE SomeData IN([MyParam]);
The query will compile and save OK without any errors and you can even run it. If the first parameter you put in was just '''Apple''' (of which does exist in the table), it'd work as expected. Great. Now try this
'''Apple'', ''Pear'', ''Grapes'''
and you get nothing in return. What happened? Well, IN() actually does not mean 'parse this string at the runtime', but rather a compile shortcut that translates into X = A OR X = B OR X = C and thus must be hard-coded.
In Jet SQL, a dynamic query is probably most appropriate for this type of parameter.
Optional Parameters
Another situation where it becomes an problem is when we desire optional parameters. Some developers may try to work around it like this:
WHERE SomeData = [MyParam] OR SomeData IS NULL
which unfortunately negates any index optimization Jet can perform. Here, dynamic queries makes more sense as it's cheaper to create a new execution plan and use index than to suffer the performance penalty.
You'll find more of his excellent posts at Utter Access and at Access Word Forums.
Ben writes on the use of Parameterized Queries to achieve the results I've recently described with other techniques involving dynamic SQL. I'll leave it to you to compare the two approaches and figure out how best to adapt each to your needs. I'm aware of advantages in both approaches, as well as limitations.
============================================
Assumptions:
We have an employee table from which we want to retrieve a personnel record derived from a given Primary key. Access provides us with a very convenient way of parameterizing the query by supporting references to controls on forms such as this:
SELECT *
FROM tblEmployees
WHERE empID = Forms!frmEmployees!empID;
This works out very well when the query is intended to live and die with this specific form and this approach is very commonly used in defining a recordsource for a report.
But references are only good when there's actually something to reference. Thus, the above query will definitely fail should the form frmEmployees not be open at time the query is called. We also run into problems of reusability. Surely the need to reference a employee record based on the primary key can be used in more than one places. Should we then write each query for each possible form reference? That would be a maintenance problem. Thus we may want to consider using parameters in more general forms.
Consider this syntax, found in a Parameterized Query:
PARAMETERS lempID INT;
SELECT *
FROM tblEmployees
WHERE empID = [lempID];
Now the query is disassociated from any references to any other objects and can run on its own two feet. Of course when you run it, Access will, as usual, present a little parameter input dialog. Therefore, sometimes people like to do something like this:
PARAMETERS [Enter an Employee ID];
This will work okay for simple use, but sometime we don't want to enter the paramter via a simple dialog; perhaps we already have a combobox on a form and want to make use of it. No problem; we can use VBA to fill in this for us.
With CurrentDb.QueryDefs("qryEmployee")
.Parameters("lempID") = Me.MyComboBox
Set frm.Recordset = .OpenRecordset(dbOpenDynaset)
End With
With this code snippet we can make use of a complete pop up form or subform that is filtered by the selection made in MyCombobox.
We certainly can make use of the techniques for action queries:
PARAMETERS lempID INT;
DELETE FROM tblEmployees
WHERE empID = [lempID];
With CurrentDb.QueryDefs("qdlEmployee")
.Parameters("lempID") = Me.MyCombobox
.Execute dbFailOnError
End With
As can be seen, using parameters is much more straightforward with any action queries and provides an convenient means for reusing the same query for several different parameters that may be chosen by user at runtime.
String Sanitizing
One more benefit is that there's no need to perform any string sanitizing as is required when writing a dynamic query. Suppose, for example, an user entered this value to pass into the query as a parameter:
Mike's and Al's "Good" Cars
In a dynamic query, we would be facing lot of headache in ensuring that the quotes are correctly delimited and escaped, with one of several variants:
'Mike''s and Al''s "Good Cars'
"Mike's And Al's ""Good Cars"""
Chr(34) & "Mike's And Al's " & Chr(34) & "Good" & Chr(34) & " Cars"
But in the code based on a parameterized query:
Parameters("sParam") = Me.MyTextbox
it's always parsed correctly with no help from us!
Maintainability
One more technique. This isn't strictly related to the parameter query but pertains to maintainability. I like to put parameter queries in a function or property procedure....
Something like this:
Property GetPersonnelRecord(EmpID As Long) As DAO.Recordset
Static q As DAO.QueryDef
If q Is Nothing Then
Set q = CurrentDb.QueryDefs("qryEmployees")
End If
With q
.Parameters("EmpID") = EmpID
Set GetPersonnelRecord = .OpenRecordset (dbOpenSnapshot)
End With
End Property
The reason for doing it this way is to provide an interface for VBA into individual queries and make the code maintainable especially in regards to queries that are reused in many different places.
But here's a more important reason. Suppose that after years of doing the above, we had to change the requirement and partition the employee table between active employees and past employees due to data storage restrictions and performance reasons. With a consistent interface as given above, it's a snap to add a 2nd parameter to identify whether the employee is active or not, update the property procedure to enter the 2nd parameter. Hit the compile and the compiler will find missing arguments in all places, which is far more accurate than using Find & Replace to track down all uses of the same queries all over in the code base.
Thus, parameter queries have lot to offer in terms of code maintainability and documentation in exchange for a little investment in some extra VBA coding that otherwise wouldn't be necessary with control references or dynamic queries.
Limitations
Of course, it goes without saying that they do have their limitations. Many people working on various RDBMS have had tripped over this innocuous looking parameter (adapted into Jet SQL):
PARAMETERS MyParam TEXT;
SELECT *
FROM aTable
WHERE SomeData IN([MyParam]);
The query will compile and save OK without any errors and you can even run it. If the first parameter you put in was just '''Apple''' (of which does exist in the table), it'd work as expected. Great. Now try this
'''Apple'', ''Pear'', ''Grapes'''
and you get nothing in return. What happened? Well, IN() actually does not mean 'parse this string at the runtime', but rather a compile shortcut that translates into X = A OR X = B OR X = C and thus must be hard-coded.
In Jet SQL, a dynamic query is probably most appropriate for this type of parameter.
Optional Parameters
Another situation where it becomes an problem is when we desire optional parameters. Some developers may try to work around it like this:
WHERE SomeData = [MyParam] OR SomeData IS NULL
which unfortunately negates any index optimization Jet can perform. Here, dynamic queries makes more sense as it's cheaper to create a new execution plan and use index than to suffer the performance penalty.
Thursday, August 6, 2009
The Cheese Stands Alone
In a previous blog I talked about the usefulness of dynamically parameterized queries in generating small recordsets for forms and reports, a technique that is particularly good for client-server applications with potentially large recordsets in the back end.
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.<
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.<
Sunday, July 26, 2009
Data Macros. How Hard Could It Be?
The last week has been a lot of fun. Like many of the people who have access to the Technical Preview (TP) release for Access 2010, I lost a few hours of sleep the first two or three days of the week.
However, over the last couple of days, it's begun to feel more like "business as usual". And I think that's a good thing. A very good thing.
The new "look and feel" are natural and intuitive and the improved ability to customize the ribbon really make it seem like an old friend with a few new tricks up it's sleeve. I'm looking forward to this version like never before.
True, I'm still trying to get comfortable with macros, and doing so is one of my personal goals here. Next time I'll try to have some successes to report with regard to the new data macros in A2010. "Data macros" appear to be corollaries to the triggers on SQL Server tables. I find SS triggers to be quite useful, so I'm really looking forward to mastering this tool for my first love, Access.
No Me Vuelvo a Enamorar
Oh, well. Es parte de la aventura.
However, over the last couple of days, it's begun to feel more like "business as usual". And I think that's a good thing. A very good thing.
The new "look and feel" are natural and intuitive and the improved ability to customize the ribbon really make it seem like an old friend with a few new tricks up it's sleeve. I'm looking forward to this version like never before.
True, I'm still trying to get comfortable with macros, and doing so is one of my personal goals here. Next time I'll try to have some successes to report with regard to the new data macros in A2010. "Data macros" appear to be corollaries to the triggers on SQL Server tables. I find SS triggers to be quite useful, so I'm really looking forward to mastering this tool for my first love, Access.
No Me Vuelvo a Enamorar
Oh, well. Es parte de la aventura.
Sunday, July 12, 2009
Blue Skies Smiling at Me
...Nothing but blue skies from now on. Would that it were true.
Another blue weekend and not much Access work got done over here at GPG Manor.
It's funny how languages work (I'm thinking of English at the moment, of course, but every language has it's own quirks.)
For example, I was listening to Willie Nelson a bit ago and I got to thinking how ironic it is that we call it a blue day when there is no blue sky overhead, but it's also a gray day if the sky is gray. Explain that if you can.
Fool Hearted Memories
Nickels and dimes
Memories and wines
She's on his mind once again
The same old fool
...
But according to Willie the sky should be blue again mañana.
Inevitable Robot Apocalyse
Of course, it's not all bad down here in Oregon. Yesterday, I found a YouTube video of my nephew's Ska band and had a good old time for nearly an hour. Skippy's still in High School, but he already has his own groupies, from what I hear.
Uh, no. I don't get their music exactly, but that's my sister's son behind the shades, for crying out loud. How much better can it get than that?
Just Your Old Rock, A'Rollin' Away
Oh, I did do a bit of Access related stuff this weekend. I updated the resources page at my website. Lots of great work out there. It was fun. I'm sure I left out way more than I found, but that's about all I had time for.
Next Time.
Another blue weekend and not much Access work got done over here at GPG Manor.
It's funny how languages work (I'm thinking of English at the moment, of course, but every language has it's own quirks.)
For example, I was listening to Willie Nelson a bit ago and I got to thinking how ironic it is that we call it a blue day when there is no blue sky overhead, but it's also a gray day if the sky is gray. Explain that if you can.
Fool Hearted Memories
Nickels and dimes
Memories and wines
She's on his mind once again
The same old fool
...
But according to Willie the sky should be blue again mañana.
Inevitable Robot Apocalyse
Of course, it's not all bad down here in Oregon. Yesterday, I found a YouTube video of my nephew's Ska band and had a good old time for nearly an hour. Skippy's still in High School, but he already has his own groupies, from what I hear.
Uh, no. I don't get their music exactly, but that's my sister's son behind the shades, for crying out loud. How much better can it get than that?
Just Your Old Rock, A'Rollin' Away
Oh, I did do a bit of Access related stuff this weekend. I updated the resources page at my website. Lots of great work out there. It was fun. I'm sure I left out way more than I found, but that's about all I had time for.
Next Time.
Sunday, July 5, 2009
"Inappropriate Language" in MS Access 2007 Templates
Okay, so I've been trying to figure out how to submit additional Access 2007 templates to the MS Access Template Site. For some time now, the MS Access team has been encouraging MVP's to participate in the template program.
Well, I tried, I really tried. A little over a year ago, I submitted a dead simple template that consists of the table schema and two forms. That one was accepted.
However, MS has a policy of not accepting "community submitted" templates which contain any VBA whatsoever. Embedded macros are permitted, but not VBA, so that first one is actually a pretty uninteresting template, as templates go.
Nonetheless, a few weeks ago, encouraged by seeing that simple little "template" go over 10,000 downloads, I decided to try again, with a slightly different approach. I created a demo accdb from a Project Work Tracking tool I first created for my own use a few years ago. (You can download the original from my website, here.)
Then I created a second copy of the accdb, from which I removed all code, macros and VBA. Then I turned it into a sanitized template so I could upload it to the MS template site.
It was rejected--three times! Yes, even without code it is not publishable. The reason given is "inappropriate language". Okay, I say, if "Project Budget", "Project Milestone" and "My Company Location" are inappropriate language, then I plead guilty. I used all three terms and several others, similar in content and scope, in many places in the template.
However, I strongly suspect the real reason this templete got rejected might be that, by offering a code-less, and therefore non-operational template, but WITH a link to the full working accdb on my site, I'm suggesting potential users could start with the "shell" template from the MS site and then go to my website for the full working version if they like the shell. Apparently that's not an acceptable alternative to a macro driven MS Template.
I could be wrong about that interpretation, of course, but I'm pretty discouraged by this latest experience anyway.
It's pretty hard for me to see a way to take advantage of the template opportunity MS has been promoting so hard for the last couple of years, unless I agree to abandon VBA entirely in favor of embedded macros.
Revising my little Tracking Tool around macros, of course, would make a simple sample accdb simpler still. Much as I would love to continue offering templates via the community submission route, I just can't see myself simplifying functionality with pure embedded macros simply to get them published by MS.
Here's a bit more "inapropriate language":
To me, MS community-submitted templates are beginning to look more and more like a terrific idea that is nonetheless doomed to failure because of the way it has been implemented. Oh well, it was fun trying, and you can download the full, working accdb (or mdb) version of the sample from my website. If you choose to do so, though, look out for the "inappropriate language" in it.
Well, I tried, I really tried. A little over a year ago, I submitted a dead simple template that consists of the table schema and two forms. That one was accepted.
However, MS has a policy of not accepting "community submitted" templates which contain any VBA whatsoever. Embedded macros are permitted, but not VBA, so that first one is actually a pretty uninteresting template, as templates go.
Nonetheless, a few weeks ago, encouraged by seeing that simple little "template" go over 10,000 downloads, I decided to try again, with a slightly different approach. I created a demo accdb from a Project Work Tracking tool I first created for my own use a few years ago. (You can download the original from my website, here.)
Then I created a second copy of the accdb, from which I removed all code, macros and VBA. Then I turned it into a sanitized template so I could upload it to the MS template site.
It was rejected--three times! Yes, even without code it is not publishable. The reason given is "inappropriate language". Okay, I say, if "Project Budget", "Project Milestone" and "My Company Location" are inappropriate language, then I plead guilty. I used all three terms and several others, similar in content and scope, in many places in the template.
However, I strongly suspect the real reason this templete got rejected might be that, by offering a code-less, and therefore non-operational template, but WITH a link to the full working accdb on my site, I'm suggesting potential users could start with the "shell" template from the MS site and then go to my website for the full working version if they like the shell. Apparently that's not an acceptable alternative to a macro driven MS Template.
I could be wrong about that interpretation, of course, but I'm pretty discouraged by this latest experience anyway.
It's pretty hard for me to see a way to take advantage of the template opportunity MS has been promoting so hard for the last couple of years, unless I agree to abandon VBA entirely in favor of embedded macros.
Revising my little Tracking Tool around macros, of course, would make a simple sample accdb simpler still. Much as I would love to continue offering templates via the community submission route, I just can't see myself simplifying functionality with pure embedded macros simply to get them published by MS.
Here's a bit more "inapropriate language":
To me, MS community-submitted templates are beginning to look more and more like a terrific idea that is nonetheless doomed to failure because of the way it has been implemented. Oh well, it was fun trying, and you can download the full, working accdb (or mdb) version of the sample from my website. If you choose to do so, though, look out for the "inappropriate language" in it.
Saturday, June 27, 2009
Where in the World is Common Parameter?
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.
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.
Subscribe to:
Posts (Atom)
