You will probably remember from the previous chapter that a QueryDef is the name given to a query object variable in VBA. Just as we can create a String -type variable to hold a piece of text, so we can create a QueryDef object to hold a query definition. Note that a QueryDef object holds the definition of a query, not the results. Query results, as we saw in the previous chapter, are held in Recordset objects. If you want, you can think of the QueryDef as being like a query in design view and a Recordset as representing a query in datasheet view.
As you can see from the diagram above which shows a section of the DAO hierarchy, the QueryDefs collection belongs to the Database object. This is fairly logical really - after all, a database can have multiple queries in it, but a query can only exist in one database.
Create a new standard code module and call it Chapter 7 Code .
Type in the procedure as it appears below.
Function MakeQueryDef(ByVal strSQL As String) As Boolean Dim qdf As QueryDef Dim boolResultCode As Boolean If strSQL = "" Then boolResultCode = False 'no query string to use! Else On Error Resume Next 'get ready to handle any errors 'Create the new query object Set qdf = CurrentDb.CreateQueryDef("qryExample") If Err.Number <> 0 Then boolResultCode = False 'failed for some reason so cannot continue Else qdf.SQL = strSQL 'succeeded so load the query object with 'the query string qdf.Close 'finished with the query object so close it RefreshDatabaseWindow 'update the Query window boolResultCode = True 'report a success End If On Error GoTo 0 'reset error handler End If MakeQueryDef = boolResultCode 'report what happened End Function
To run the procedure, open the Immediate window. Type in the following and hit Enter :
?MakeQueryDef("SELECT * FROM tblCompany WHERE CompanyID = 10")
The word True should appear in the Immediate window, indicating that the function completed successfully.
If you try to execute this procedure for a second time it will return False indicating that it has failed. This is because there is already a query called qryExample in the database. We will look at how to handle this situation later on in the chapter. You can check this by commenting out the On Error Resume Next line in the code then running the procedure again. This time the error will not be trapped and instead you will see the Access error message "Object 'qryExample' already exists."
You don't have to worry if you don't quite understand the error handling techniques used in this chapter. We will be revisiting this code in more detail in Chapter 12 when we look at error handling. For the moment, you only need to understand that the use of On Error allows us to handle run-time errors gracefully without confusing messages popping up in front of the user .
Press Alt+F11 to view the Database window and change to the Queries tab. You should see a query there called qryExample .
Open qryExample in design view. The design of the query should match the criteria that you passed as an argument to the MakeQueryDef function.
Now go back to the VBA IDE and type the following procedure in the code window for the Chapter 7 Code module:
Function ChangeQueryDef(ByVal strQuery As String, ByVal strSQL As String) As Boolean Dim qdf As QueryDef Dim boolResultCode As Boolean If (strQuery = "") Or (strSQL = "") Then boolResultCode = False 'no query name or query string to use! Else On Error Resume Next 'get ready to handle any errors 'modify the query object with the name given Set qdf = CurrentDb.QueryDefs(strQuery) If Err.Number <> 0 Then boolResultCode = False 'failed for some reason so cannot continue Else qdf.SQL = strSQL 'succeeded so load the query object with 'the query string qdf.Close 'finished with the query object so close it RefreshDatabaseWindow 'update the Query window boolResultCode = True 'Report a success End If On Error GoTo 0 'reset error handler End If ChangeQueryDef = boolResultCode 'report what happened End Function
Run the procedure by typing the following in the Immediate window and hitting Enter :
?ChangeQueryDef("qryExample", "SELECT * FROM tblCompany ORDER BY CompanyName")
The word True should appear in the Immediate window, indicating that the function completed successfully.
Press Alt+F11 to view the Database window and change to the Queries tab. Open qryExample in design view. The design of the query should now have changed to match the new SQL you passed to the ChangeQueryDef function.
How It Works
Creating a QueryDef is very simple.
Dim qdf As QueryDef Set qdf = CurrentDb.CreateQueryDef("qryExample")
First, we create an empty QueryDef object. The CreateQueryDef method both creates a QueryDef object and assigns it the name that it will have when it is saved. If you don't intend to save it and will only use it in the current procedure, you can give it an empty string ( "" ) as its name and it won't be saved. However, we do want to save our QueryDef so we can use it again later and so we'll call it qryExample .
If you try to create a QueryDef object with a name that is the same as a saved query that already exists in the database, Access will generate a run-time error. In our example we have decided to trap errors and return a false value (which we would then have to deal with appropriately) rather than let Access fill the screen with its error message and kill the application. Notice that we also handle the possibility of sending the function an empty SQL string, which would not actually cause an error but it would obviously make no sense to create a query which didn't do anything.
qdf.SQL = strSQL
Next, we assign the SQL property of the QueryDef object. If you aren't too sure about how to write SQL, you can always try designing the query normally in the Query Designer and then switching the query to SQL view to see the SQL created. You can then copy the SQL to the clipboard and paste it into your procedure from there.
Finally, we close the QueryDef , with the following line:
The act of closing the QueryDef saves it to the database. Then we refresh the database window to show the changes:
The RefreshDatabaseWindow method causes any changes to database objects such as forms, reports , and queries to be immediately reflected in the database window.
Modifying the QueryDef is just as simple as creating it. First you use a variable to reference the QueryDef called qryExample in the current database.
Dim qdf As QueryDef Set qdf = CurrentDb.QueryDefs(strQuery)
And then you modify its SQL property, save it, and refresh the database window like we did in the previous function.
A QueryDef object has over a dozen different properties, but you will find that there is one property in particular that you will use more than any other; the SQL property. As we saw above, the SQL property is used to set the SQL statement that will be run when the QueryDef object is executed.
If the frmCriteria form is to be used to allow users to frame their queries in a manner of their own choosing, we will then need to convert the entries that the user makes on the form into an SQL statement and use that SQL statement to select the records that should be displayed.
SQL SELECT statements generally consist of three clauses:
SELECT fields FROM source table AS alias nameWHERE criteria
The SELECT clause indicates the columns or fields that will be displayed in the result set of the query.
The FROM clause indicates the base table or tables from which the results are drawn. The AS keyword is optional but if used gives the alias name to the table or tables specified. For example SELECT tblSales AS s means that you can use s instead of the longer tblSales throughout the rest of the query. Using aliases can save a lot of typing and if used carefully can make queries much easier to read and, therefore, to maintain. It is also possible to abbreviate this clause by omitting the AS keyword but retaining the alias name, for example: SELECT tblSales s
The WHERE clause indicates the criteria for determining which rows or records will be represented in the result set of the query.
Put another way, if you think of the table as a kind of spreadsheet, the SELECT clause is a form of vertical partitioning, and the WHERE clause a method of horizontal partitioning:
So let's see how this works when we apply it to the frmCriteria form in the Ice Cream database.
Well, we know which fields we want displayed. We know that the user is to be shown details of each sale and so we will have to display all of the fields from the tblSales table. That will be fixed and won't change, irrespective of the criteria we select on frmCriteria . In other words, the SELECT clause will not change and will always be:
which selects all of the fields from the tblSales table.
Things get slightly more complicated with the FROM clause. Most of the time we will only need to use the tblSales table. This is because most of the criteria on the frmCriteria form represent columns in the tblSales table. In fact, if we look at the following table, we can see which columns are represented by which criteria on the frmCriteria form.
relates to this field
Ice Cream is
Ice Cream contains
Payment delay is
tblSales.DatePaid - tblSales.DateOrdered
tblSales.DateDispatched - tblSales.DateOrdered
As you can see, however, one of these criteria is dependent on a field in a separate table. If we want to view sales of ice creams that contain specific ingredients , then we need to join the tblSales table with the tblIceCreamIngredient table and then search for records with an fkIngredientID matching the one selected on the criteria form.
Remember, although the combo box on frmCriteria displays the ingredient's name, the bound column contains the ingredient's fkIngredientID . Without this fkIngredientID , we would have needed to join the tblIngredient table as well to search on the Name column. This would have been slower to run as well as more complicated to implement.
So, we can see that most of the time our FROM clause will look like this:
The only variation is when the user chooses to restrict by ingredient, when the FROM clause will look like this:
FROM tblSales INNER JOIN tblIceCreamIngredient ON tblSales.fkIceCreamID = tblIceCreamIngredient.fkIceCreamID
This FROM clause implements the join between the two tables. The two tables to be joined are tblSales and tblIceCreamIgredients :
FROM tblSales INNER JOIN tblIceCreamIngredient
And the fields participating on either side of the join are both called fkIceCreamID :
ON tblSales.fkIceCreamID = tblIceCreamIngredient.fkIceCreamID
Now for the WHERE clause, this one is certainly going to change. What we are interested in is building up a SQL statement which will restrict the rows selected to those which meet our criteria and as we know, it is the WHERE clause which restricts the rows that are returned. Each of the criteria selected by the user will add at least one extra element to the WHERE clause. So, in the specific example we have been looking at so far, the following WHERE clause would be generated.
WHERE tblCompany.CompanyID=10 AND tblSales.DateOrdered>=#11/01/02# AND tblSales.DateOrdered<=#11/30/02# AND [DateDispatched]-[DateOrdered]>7
Anyway, enough of the theory; let's get on with writing a procedure which converts our criteria into a SQL string.
Open your version of the IceCream.mdb database and open the code module for the frmCriteria form.
Now create a new function called BuildSQLString and add the following code to it:
Function BuildSQLString(ByRef strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String strSELECT = _ "SELECT s.*.* " 'set the columns to return results from '(all columns in this case) 'set an alias "s" for tblSales and "i" for tblIceCreamIngredient strFROM = "FROM tblSales AS s " If chkIngredientID Then strFROM = strFROM & " INNER JOIN tblIceCreamIngredient i " & _ "ON s.fkIceCreamID = i.fkIceCreamID " 'check for 2nd or more WHERE term If strWHERE <> """" Then strWHERE = strWHERE & " AND " strWHERE = "i.fkIngredientID = " & cboIngredientID End If If chkCompanyID Then 'check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "s.fkCompanyID = " & cboCompanyID End If If chkIceCreamID Then 'check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "s.fkIceCreamID = " & cboIceCreamID End If If chkDateOrdered Then If Not IsNull(txtDateFrom) Then 'Check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "s.DateOrdered >= " & _ "#" & Format$(txtDateFrom, "mm/dd/yy") & "#" End If If Not IsNull(txtDateTo) Then 'Check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "s.DateOrdered <= " & _ "#" & Format$(txtDateTo, "mm/dd/yy") & "#" End If End If If chkPaymentDelay Then 'check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "(s.DatePaid - s.DateOrdered) " & _ cboPaymentDelay & txtPaymentDelay End If If chkDispatchDelay Then 'check for 2nd or more WHERE term If strWHERE <> "" Then strWHERE = strWHERE & " AND " strWHERE = strWHERE & "(s.DateDispatched - s.DateOrdered) " & _ cboDispatchDelay & txtDispatchDelay End If strSQL = strSELECT & strFROM If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE BuildSQLString = True End Function
Next, amend the event handler for the Click event of the cmdFind button so that it looks like this:
Private Sub cmdFind_Click() Dim strSQL As String Dim strTableName As String If Not EntriesValid(strTableName) Then Exit Sub If Not BuildSQLString(strSQL) Then MsgBox "There was a problem building the SQL string" Exit Sub End If MsgBox strSQL CurrentDb.QueryDefs("qryExample").SQL = strSQL End Sub
Now save the changes you have made to the Form_frmCriteria module and switch back to Access by hitting Alt+F11 .
Open the frmCriteria form and enter the following criteria.
01-Nov-02 and 30-Nov-02
Dispatch delay is
More than 7 days
When you hit the Find Matches button, a message box will appear displaying the SQL string that has been constructed .
Hit the OK button and then open the qryExample query in design view. It should reflect the criteria that you entered.
When you run the query it displays the records that meet your criteria.
How It Works
The principle behind this procedure is simple enough, although it may look a bit daunting at first! The general idea is that a variable of type String , strSQL , is passed into the procedure by reference. We do this rather than passing the variable by value as we would normally do so that the function can modify it. Note that we can't simply return the string using the function's return value as we are already using this to indicate success or failure of the operation. The procedure eventually puts into that variable a SQL statement that reflects the choices entered by the user on frmCriteria .
In fact, there is nothing in this exercise that we have not already covered in this chapter. The first step was to create a procedure that generates an SQL string from the criteria that were selected. The heart of this procedure is three variables that correspond to the three parts of the SQL string.
Dim strSELECT As String Dim strFROM As String Dim strWHERE As String
As we said earlier, the SELECT clause is invariant, so we can populate that immediately.
strSELECT = "SELECT s.* "
The next step is to complete the FROM clause. Again, we looked at this earlier and decided that it should contain just the tblSales table unless the user chose to restrict by ingredient, in which case the tblIceCreamIngredient table should be joined to it. We also alias tblsales to s and tblIceCreamIngredient to i . This simply helps to make the rest of the string shorter and easier to read.
strFROM = "tblSales AS s " If chkIngredientID Then strFROM = strFROM & " INNER JOIN tblIceCreamIngredient i " & _ "ON s.fkIceCreamID = i.fkIceCreamID " If strWHERE <> """" Then strWHERE = strWHERE & " AND " strWHERE = " AND i.fkIngredientID = " & cboIngredientID End If
You can use the "+" (addition) operator instead of the " & " (concatenation) operator to build strings with instead but the " & " operator is able to perform automatic type casting and so is preferable. For example, if you had a string called strTestString and an integer called intTestInt then the equivalent of " strTestString & intTestInt " is " strTestString + Str(intTestInt) " - if you don't explicitly cast the integer variable by using the Str() function then you get a "Type Mismatch" error.
Next we construct the WHERE clause. To build the WHERE clause, we look at each of the textboxes in frmCriteria in turn . If the user has checked the checkbox for a criterion and has entered something in the relevant textbox, we add that criterion to the WHERE clause, after first checking to see if we need to add an and or not as whenever we add a new section to the end of the WHERE clause we must also add an AND in front of it to link the sections together.
Remember that the variable that is being populated with the SQL string is the one that was passed into the function at the start.
Function BuildSQLString(strSQL As String) As Boolean ... strSQL = strSELECT & strFROM If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE BuildSQLString = True End Function
This variable was passed in by reference which means that we can amend the contents of the variable and those changes will persist when the function exits.
All that is left in this procedure is to return True to indicate that the procedure completed successfully. Strictly speaking this is unnecessary as we don't actually perform any validation checks on the string we build. We could have just used a subroutine rather than a function, that is, we don't strictly need to return a value here and so we don't need to use a function. However, it is standard practice to use a function that returns a Boolean value to indicate success or failure, and if at a later date we decide to modify the function to include checking code then we will not need to change any code that calls the function.
Having built the function, we then need to make sure that it is invoked when the user presses the Find Matches button on the criteria form. So we modify the cmdFind_Click procedure by declaring a variable to hold the SQL string and then passing it into the BuildSQLString procedure.
Dim strSQL As String Dim strTableName As String If Not EntriesValid(strTableName) Then Exit Sub If Not BuildSQLString(strSQL) Then MsgBox "There was a problem building the SQL string" Exit Sub End If
Note how we check the return value of the BuildSQLString procedure and display an error message if it's False . If it is True , however, we display the string in a message box and then modify the qryExample query to contain the SQL string that we built.
MsgBox strSQL CurrentDb.QueryDefs("qryExample").SQL = strSQL
That is fine as far as it goes and we are well on the way to achieving the functionality that we originally stated. If you remember, we stated earlier that when they run the query, the users want to know how many sales match the criteria that they have specified and should then be presented with the opportunity of viewing:
the matching results in detail on screen
a report containing the matching results
Well, so far we have a way of determining the criteria that the user wants to use to restrict the sales records that he or she wants to view, and we have a method of programmatically modifying a query in Access to express the user's choice. So, where do we go from here?