ADDING MORE POWER WITH VBA
To get even more power from queries, use them from VBA where you can use control-of-flow statements such as If..Endif and Select Case..End Select to build SQL statements and pass parameters to queries. You can give your users more versatile user interfaces for selecting and viewing their data.
Building Faster Queries in VBA
With VBA, you no longer need a parameter query for the criteria to be specified by users. Consider a report that requires two or three optional parameters. If the parameters involve multiple tables, the query might be joining in tables that are needed only some of the time. To make the query run faster, build only the portion of the select statement that's required.
Suppose that users can choose to select all states or just one state. The criteria for =[State] or IsNull() would need to be evaluated as part of the selection. Building the select statement in VBA gives you the option of eliminating the criteria completely if users don't supply a value.
Some criteria can be determined based on information in other tables or information determined in the VBA code. Most often, the need to alter criteria comes from the query-by-form requirements.
Note
Building queries on-the-fly like this means that they aren't precompiled. This might turn out to be slower than having a more complex query stored as a precompiled QueryDef.
Using Query by Form
Query by form (QBF) is one of the most requested features of most any system. Developers are regularly challenged with striking a balance between ease of use and features. Query by form provides users with a “fill-in-the-blank” approach to solving most queries users likely will ask regularly.
The simplest version of query by form is to provide users with a form containing all the fields in a table. Users fill in the field; then, when they click the form's search or OK button, the records that match the values the users entered are retrieved and shown on a report or in a list box. Access provides this functionality by using the filter-by form available to users in the form and table views. Generally, this won't be enough for end users who want a simple screen with yes/no options.
A Simple Query by Form
Figure 8.21 shows a simple query-by-form example. The user regularly wants to run a query for customers based on the first letter of the customer's last name. If a letter isn't specified, all customers are included.
Figure 8.21. This form, frmCustomerLast, is an example of query by form.
When the user clicks Preview, the following code executes:
strSQL = "SELECT Customers.* FROM Customers"
If Not IsNull(Me![txtLetter]) Then
If Me![txtLetter] <> "" Then
strSQL = strSQL & " WHERE LastName Like '" & Me![txtLetter] & "*'"
End If
End If
Set qd = CurrentDb.QueryDefs("ztqryCustomersByLetter")
qd.SQL = strSQL
qd.Close
DoCmd.OpenQuery "ztqryCustomersByLetter"
The code creates a SQL string with the basic SELECT statement, and then adds the WHERE clause only if the user has entered a value in the first letter text box. You can extend this example by adding more text boxes for the user to fill in. If the user needs to select two letters, for example, you can add a second text box. The code that builds the WHERE clause would need to be modified to include OR LIKE if the user filled in each text box.
A More Complex Query by Form
To give you a better idea of what you can do with query by form, here's a more complex example, which takes multiple fields on a form and creates a record source for a subform (see Figure 8.22). This form, frmQueryByFormExample, can be found in the Chap08.mdb database, located on the accompanying CD-ROM in the ExamplesChap08 folder.
Figure 8.22. Allowing your users to see results based on their choices increases a system's flexibility.
Note
This example must be broken out into its own database to make it as simple as possible to use in your own applications. You'll take the techniques used here, rather than the specific code, because this example is pretty exact in its use.
This example is actually pretty complete in that it allows you to
-
Select data items and have them automatically requery the subform
-
Turn off the auto requery feature, but notify the user if the subform needs to be requeried because of changed criteria by turning a manual requery button red
-
Use a manual Requery button after all the criteria is set
-
Clear the criteria currently chosen with a Clear button
Tip
I included an option for turning off the auto requery feature because with large recordsets, it can become cumbersome to have the system requery every time you pick a new criteria. This way, users can set up all the criteria they want, and then click the manual Requery button. Users will then feel that the system is pretty quick regardless of the data items, and also feel as though they're in control. (Do we have them fooled, or what?)
One thing this example doesn't do is let users combine the different criteria with ANDs and ORs. It ANDs the different pieces of criteria, and ORs the like pieces of criteria. This will become clearer as you walk through the example, so let's get started.
You can find all the important items in this example on the form frmQueryByFormExample. If you open this form in Design view, you'll see the following controls:
The lboCategoryToInclude multiselect list box, which allows users to select multiple categories of movies to include. This list box is based on the Categories table.
A series of check boxes that represent the possible ratings for movies. These are called chkRatedG, chkRatedPG, and so on.
Tip
You can specify the same type of criteria in two ways. In other words, you can use either a multiselect list box or a series of check boxes to choose multiple items of a criteria. The reason these are both shown is to give you a more complete example to help you choose between different methods of displaying information.
The check box method is a good one to use when there's a limited and static list to present; otherwise, I'd recommend using the list box because it's easier to maintain.
The txtBegPurchaseDate text box.
The optAutoRequery option group, which enables and disables the Auto Requery feature.
The cmdRequery command button, which re-creates the subform record source when you have the Auto Requery feature turned off.
The cmdClear command button, which resets the criteria choices and clears the subform.
In the first scenario, you leave the Auto Requery feature turned on. Whenever you click the list box, select the ratings check boxes, or enter a new purchase date, the RequerySubform() subroutine is called from the control's AfterUpdate event. RequerySubform() is located in the general routines of the form frmQueryByFormExample. You can see this code in Listing 8.1.
Listing 8.1. Chap08.mdb: Combining the User's Choices and Updating the Subform's RecordSource Property
The RequerySubform()subroutine performs the bulk of the form's tasks, either by doing the work itself or by calling those routines that do. First, the subroutine checks to see whether optAutoRequery is set to true, or the Requery button was clicked. This is performed by the following line of code:
If Me!optAutoRequery Or Screen.ActiveControl.Name = "cmdRequery" Then
Tip
This code line is a great example of using an option group in a true/false situation. Setting the first option to –1 (true) and the second option to 0 (false) cuts down coding because you can treat the option group as a logical expression.
Next, RequerySubform()calls routines that create the individual criteria segments. The calls made by RequerySubform() are a good way to create the segments, because you can then debug each routine. The following code calls the subroutines:
strCategorySQL = IncludeCategories() strRatingsSQL = IncludeRatings() strPurchaseDateSQL = IncludePurchaseDate()
These subroutines demonstrate how to create SQL criteria strings with different methods. The IncludeCategories() subroutine in Listing 8.2 shows how to create a portion of SQL criteria by using the lboCategoryToInclude multiselect list box. In Listing 8.3, the IncludeRatings() subroutine uses the check boxes and combines those that have been chosen into one string. The last routine, IncludePurchaseDate() in Listing 8.4, takes a text box and concatenates it to a string used for the SQL criteria. These routines all pass back a string that's then combined later.
Note
For more information on using the multiselect list box programmatically, see Chapter 10 or Chapter 11, “Creating Powerful Reports.”
Listing 8.2. Chap08.mdb: Creating a SQL Criteria String with a Multiselect List Box
Listing 8.3. Chap08.mdb: Using Check Boxes for a Static List with a Limited Number of Choices
Listing 8.4. Chap08.mdb: Using a Text Box with Query by Form
Private Function IncludePurchaseDate() As String
'-- Create the Categories Where portion of the SQL statement
If Not IsNull(Me!txtBegPurchaseDate) Then
IncludePurchaseDate = "([DatePurchased] >= " & _
"Forms!frmQueryByFormExample!txtBegPurchaseDate)"
End If
End Function
|
After the subroutines are completed, RequerySubform() starts the WHERE clause, with the Categories string concatenated to it with this line of code:
strWhereSQL = "Where " & strCategorySQL
You can use the next piece of code both for the Ratings and the Purchase Date, with minor changes. The code for the Ratings is shown here:
'-- If a rating was passed back, then add it to the Where clause.
If Len(strRatingsSQL) <> 0 Then
'-- If the Category criteria was already added,
'-- AND it with the Ratings criteria.
If strWhereSQL <> "Where " Then
strWhereSQL = strWhereSQL & " And "
End If
strWhereSQL = strWhereSQL & strRatingsSQL
End If
This code is similar for the Purchase Date; the code checks to see whether there's anything in the current section of the Where statement, strRatingsSQL, and then concatenates an AND statement if something has already been added to strWhereSQL. Finally, it adds the strRatingsSQL to strWhereSQL.
Next, the code checks to see whether any conditions were created; if not, the code uses Where False so that no records show up. The code then concatenates the Where clause to the rest of the SQL statement and assigns that to the RecordSource property of the subform:
If strWhereSQL = "Where " Then
strWhereSQL = "Where False"
End If
'-- Create the new SQL String and Store it to the Recordsource.
strFullSQL = "Select * From MovieTitles " & strWhereSQL
Me!subQueryByForm.Form.RecordSource = strFullSQL
Finally, the Requery button's color is set, based on whether AutoRequery is set or a manual requery has just been performed. If so, the color is set to black; otherwise, the color is set to red, to let users know that they might want to click the Requery button. Here's the code:
'-- Set the requery button to black.
Me!cmdRequery.ForeColor = 0
Else
'-- Set the requery button to red.
Me!cmdRequery.ForeColor = 255
End If
That's it for the RequerySubform() routine. Most buttons and controls call RequerySubform(). The only other routine is the event procedure behind the Clear button's OnClick event (see Listing 8.5).
Listing 8.5. Chap08.mdb: Clearing All Controls and Calling RequerySubform()
That's all there is to using query by form.
Creating Temporary QueryDefs
A temporary QueryDef allows you to create a QueryDef that won't be included in the QueryDefs collection. This feature is useful when you have common library routines called throughout an application. A temporary QueryDef isn't given a name and therefore eliminates the need for you to be concerned about finding a unique name for the query throughout the entire application. You can create a temporary QueryDef only in VBA, and the QueryDef is deleted automatically when closed. You also can create multiple temporary QueryDefs within VBA at the same time.
To create a temporary QueryDef in VBA, give the QueryDef an empty string as a name. You can set the QueryDef SQL property to perform a specific action; the Execute method will invoke the action. The Close method closes and deletes the QueryDef.
The following code sample shows one possible use of a temporary QueryDef by deleting records from a table without saving the QueryDef:
'*** Open the database and create temp QueryDef
Set db = DBEngine.Workspaces(0).Databases(0)
Set qd = db.CreateQueryDef("", "Delete * FROM tblCompany")
'*** Delete the records in tblCompany with a temporary QueryDef
qd.Execute
Using the DoCmd Object's RunSQL Method
The DoCmd object in Access allows you to execute SQL statements against the current database. DoCmd is similar to the functionality of a temporary QueryDef used to execute Action queries. The method used off the DoCmd object is RunSQL.
One disadvantage with RunSQL is that it operates only on the current database. Unlike a temporary QueryDef, which can be created against another database, RunSQL is based only on tables that exist in the current database.
RunSQL prompts users about changes performed unless warnings are turned off. The following code clears the salesperson code from all records in the invoice table. Warnings are turned off and then back on so that users don't have the option of canceling the operation.
DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE tblInvoice SET SalesPerson = NULL" DoCmd.SetWarnings True
RunSQL doesn't allow you to create an object, so you can't use it to create dynasets or snapshots. The DoCmd object was created to help users migrate from Access macros to VBA. Developers generally avoid using DoCmd.RunSQL and instead use temporary QueryDefs as a consistent method of executing Select and Action queries against a database. This avoids the problem of turning warnings off before and then back on after the RunSQL method, as well as during error recovery. Using the temporary QueryDef also allows the statement to be executed on a database other than the current default database.
Issuing Parameter Queries From VBA
The parameters passed to parameter queries are handled differently in VBA than they are from the database container or query design window. In the query design window you can create parameters that reference forms. When the query is run, it retrieves the parameters from the forms as needed. If the query is called from VBA, the parameters must be set from VBA even if the form is open. The form shown in Figure 8.23 has three options for running the query: Preview, Bad Code, and Good Code.
Figure 8.23. This form, frmPhoneList, demonstrates how and how not to use parameter queries with VBA.
The Preview button uses DoCmd.OpenQuery to open the query from the database container:
DoCmd.OpenQuery "qryPhoneList"
The Bad Code button runs VBA code that doesn't properly set the parameters before calling the OpenRecordset method, resulting in a runtime error (see Figure 8.24). The parameters haven't changed and the form is still open, but the form parameters aren't understood under VBA.
Figure 8.24. When dealing with parameterized queries through VBA, not entering the correct amount of parameters results in the Too few parameters error.
The following code sets the parameter properly so that the query will execute and return the five records you expect. By setting the parameter this way, you can use parameter queries from code even when they reference forms.
qd![Forms!frmPhoneList!txtAreaCode] = Forms![frmPhoneList]![txtAreaCode]
Note
Setting the parameters from code requires a slightly different syntax from the way the parameters are defined in the query design grid. Make sure that you remove the extra square brackets in the parameter and enclose the entire parameter with one pair of square brackets.