It should be clear that the requirements expressed above would require a separate form and report to be written to satisfy them. We will need a form to display the matching results in detail, and if the user wants to print off the results, then we will also need a report showing the matching sales. Now we already have a form displaying details of sales ( frmSales ), and we will use this to display the results that match our criteria.
What we want to do now is to create a report that will display those results as well, so we can print them out. That is what we'll do now.
Open the IceCream07.mdb database and display the Database window if it is not already visible, by pressing F11 .
Select the sales details form ( frmSales ) and right-click it. From the resultant popup menu, select Save As
In the dialog that then appears, specify that you want to save the form as a report called rptSales .
Now switch to the database window and open the report you have just created. It looks OK, but it could sure do with some tidying up.
Switch to design view and make the following changes to the report. (You might also need to resize some of the controls to make sure that they all fit within the width of one page).
fkCompanyID , fkIceCreamID , Quantity , DateOrdered , DateDispatched , DatePaid , AmountPaid
Special Effect Border Style
Now switch back to report view. The report should now look like this:
Close the report, saving the changes when prompted to do so.
Now we have a form ( frmSales ) and a report ( rptSales ) both based on the tblSales table. So, how do we go about ensuring that the form and the report display only the records that match the criteria selected by the user? In fact, there are two common methods that we can use to restrict the records displayed to those the user has selected. The first involves modifying the underlying QueryDef and the second involves creating a table of matching records. We will look at each of those in turn in some detail now.
Perhaps the most intuitive way of restricting the records on a form or report is to modify the QueryDef object to which the form is bound. That is, after all, what we have been looking at so far. Why not simply bind the form and the report to the qryExample query that we modified above? That way, whenever the user hits the Find Matches button and the qryExample query is redesigned, the population of the form and report will be automatically be redefined to match the selected criteria.
This is a frequently used method and has the advantage of being fairly straightforward. In order to modify one form and one report, we simply modify one QueryDef object.
Open the rptSales report in design view and change its RecordSource property to be qryExample . Then close the report, saving changes when prompted to do so.
Now open the frmSales form in design view and change its RecordSource property to be qryExample as well. Instead of closing the form, place a command button in the footer of the form calling it cmdPrint and setting its Caption property to Print .
Open the property window and select the print button's On Click event property. Select [Event Procedure] from the drop-down list and then hit the Builder button to the right of the property to create a VBA event handler for the Click event.
Add the following code to the cmdPrint_Click event.
Private Sub cmdPrint_Click() DoCmd.OpenReport "rptSales", acViewPreview End Sub
Now switch back to Access and add another button to the frmSales form and call it cmdClose . Add the following code to the event handler for the button's Click event and then close frmSales , saving changes when prompted to do so.
Private Sub cmdClose_Click() DoCmd.Close acForm, Me.Name End Sub
Next , switch back to the VBA IDE and open the code module for the form frmCriteria . Modify the code in the cmdFind_Click event procedure by adding a command to open the frmSales form and remove the MsgBox strSQL command as we no longer need to see this. The procedure should now look 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 CurrentDb.QueryDefs("qryExample").SQL = strSQL DoCmd.OpenForm "frmSales", acNormal End Sub
Save the changes to this module and, after switching back to Access, save the changes to frmCriteria .
Finally, run frmCriteria by clicking on the Form View button in the toolbar, 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, the sales details form is displayed containing only the records that match the criteria you specified. When you hit the Print button, the report containing those records is previewed on screen ready for printing to paper if we are happy with it.
Now that really is something! We are well on our way to implementing the functionality that we set out to achieve at the start of this chapter. In fact the only thing that we haven't put in place is a message informing the user of the number of qualifying records. Don't worry about that, though; we'll look at that a little later. For the moment, let's consider in a little more detail the solution we have implemented.
To recap, then, the steps involved in producing this solution are as follows :
The user selects various criteria
The application modifies the query upon which frmSales and rptSales is based
frmSales (and optionally rptSales ) are then opened
In this particular situation this works fine. However, this method does have a drawback. It will only work if the form and the report can both use exactly the same query as their record source. What if we had wanted the report to contain two grouping levels such that the report was grouped first by company name and then by ice cream name? Not too difficult, you might think, until you try to implement it.
You see, if you have a look at the query on which the report is based, you will discover that it does not contain either the company name or the ice cream name. Just like the frmSales and frmCriteria forms, the rptSales report contains only the ID of the ice cream and the company. And, like the form, the report uses combo boxes to display the details of the ice cream and company. The combo box is populated by a query that displays the name of the ice cream or company, but stores its ID. What that means is that, as it stands, we could group on fkIceCreamID or fkCompanyID but not on the ice cream's name or the company's name, as they are not contained in the query.
Now in this situation we could get around this problem fairly easily by simply adding the ice cream name and company name into qryExample (although this would mean that the query now involves two join operations). In practice, however, once you have allowed your users to isolate the sales they are interested in, they might want to carry out a large number of operations on these results. They might want to use them in a mail merge; they might want to count how many of them there are; they might want to display the results in reports which have very different record sources, but use the same criteria to select from among those records. What we would like is to be able to run a complicated query once to determine the records we want to deal with and then achieve some kind of permanence or persistence to the results of that query. We might want to base all sorts of queries on the results that are returned to us, but we don't want to have to re-evaluate the criteria the user has selected. Let's do that once, save the results, and then we are free to do whatever we want with the results.
So the method that we have looked at so far - modifying qryExample every time we change our criteria - is good as far as it goes. But is there a better way?
We are now going to look at a slightly different way of tackling this problem and then we will look at some of the pros and cons of this new way of restricting records in a form and a report based on criteria selected by the user.
What we are going to do is to dynamically build a query - as before - but this time we are going to use it to generate a small table of matching key values rather than a table of matching rows. This table of key values (the values of SalesID for every record which matches the selected criteria) will be called tblResults and can be used in any query to restrict the results of that query to records that match the user's criteria.
It will probably become clearer once we have tried it out.
Open the IceCream07.mdb database (if it's not already open).
Now open Form_frmCriteria , the code module behind the frmCriteria form, and modify the cmdFind_Click procedure so that it now reads like this:
Private Sub cmdFind_Click() Dim strSQL As String Dim lngRecordsAffected As Long 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 If Not BuildResultsTable(strSQL, "tblResults", lngRecordsAffected) Then MsgBox "There was a problem building the results table" Exit Sub End If DoCmd.OpenForm "frmSales", acNormal End Sub
Next, open the BuildSQLString function and change it so that it only returns the SalesID column.
Function BuildSQLString(ByRef strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String strSELECT = "SELECT s.SalesID " 'set the column to return results from strFROM = "FROM tblSales s " 'set an alias "s" for tblSales and "i" for tblIceCreamIngredient ...
Now create a new function in Form_frmCriteria called BuildResultsTable . The function should look like this:
Function BuildResultsTable(ByRef strSQL As String, _ ByVal strTableName As String, _ ByRef lngRecordsAffected As Long) As Boolean Dim db As Database Dim qdfAction As QueryDef Dim boolResultCode As Boolean boolResultCode = True 'so far so good! Set db = CurrentDb On Error Resume Next db.TableDefs.Delete strTableName 'delete the existing table If Err.Number <> 0 Then 'failed for some reason - probably because it doesn't exist 'not a problem - continue End If On Error GoTo 0 'modify the SQL string strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ") If boolResultCode Then 'check everything is still OK to continue On Error Resume Next Set qdfAction = db.CreateQueryDef("", strSQL) 'create the new query If Err.Number <> 0 Then boolResultCode = False 'failed for some reason - cannot continue End If On Error GoTo 0 End If If boolResultCode Then 'check everything is still OK to continue On Error Resume Next qdfAction.Execute dbFailOnError 'execute the new query If Err.Number <> 0 Then boolResultCode = False 'failed for some reason - cannot continue Else 'suceeded so get the results lngRecordsAffected = qdfAction.RecordsAffected qdfAction.Close 'close the query End If On Error GoTo 0 End If BuildResultsTable = boolResultCode 'report what happened End Function
Save the changes to this module and switch back to Access by hitting Alt+F11 .
Now open the criteria form frmCriteria in form view and hit the Find Matches button without entering any criteria.
The frmSales form should now appear. Close it down and switch to the Tables tab of the database window. There should now be a table called tblResults .
Next, create a new query with the following definition and save it as qryResults :
Now open the frmSales and rptSales forms and change their RecordSource property to be the name of the new query, qryResults . Close these objects, saving changes when prompted to do so.
Finally open the criteria form frmCriteri a in form view 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, the frmSales form should be displayed showing the results that match the criteria you have entered. When you hit the Print button the rptSales report should appear with the same results displayed.
How It Works
There's quite a bit going on here, and it may seem that the end result is no different from what we had before. But bear with us, and you will see just what the benefits are of using this method of implementing the criteria functionality. To better understand what is happening, let's take a look at the query qryResults that now sits behind frmSales and rptSales .
SELECT tblSales.* FROM tblResults INNER JOIN tblSales ON tblResults.SalesID = tblSales.SalesID
This query joins the list of all sales with a list of the key values ( SalesID ) of those sales that meet the criteria we have specified. In order to implement this, we need to build a table containing those key values. Now obtaining the key values themselves is not too tricky - we simply modify the BuildSQLString procedure so that instead of returning all rows from tblSales :
strSELECT = "s.* "
it simply returns the key values:
strSELECT = "s.SalesID "
Now what we need to do is to somehow get those key values into a permanent table. The simplest way to do that is to change our select query into a make-table query.
Select queries always use this general syntax:
SELECT select-list FROM table-list WHERE criteria-list
Whereas make-table queries use this syntax:
SELECT select-list INTO new-table FROM table-list WHERE criteria-list
So to turn our select query into a make-table query, we simply have to insert INTO and the name of the table we want to create between the SELECT and FROM clauses in our query. We can do that by using the Replace function, which replaces a number of characters in a string with other characters :
strSQL = Replace(strSQL, " FROM ", " INTO " & strTableName & " FROM ")
In this case we are replacing
" FROM "
" INTO " & strTableName & " FROM "
This has the effect of inserting the required INTO clause into our query string ( strSQL ). For more information on the Replace function consult the help file.
Then we take this SQL string and create a temporary query with strSQL as its SQL property.
Set qdfAction = db.CreateQueryDef("", strSQL)
Until now, we have only been dealing with saved queries that are visible in the database window. However, if we create a QueryDef object and give it an empty string as its name, VBA knows that we only want a temporary query. As soon as we close the QueryDef object later in this procedure, the query will be deleted.
Having created the query, and checking for any errors, we now execute it by calling the Execute method of the QueryDef object. Notice that we use the optional dbFailOnError argument with the Execute method. If we had not specified the dbFailOnError argument and our query could not be executed for some reason, VBA would have ignored the error and executed the next line of code without warning us. By specifying the dbFailOnError argument, we are instructing VBA to alert us if the query could not be run and we can then report this back to the procedure that called this function.
When we run the query, a new table is created and is called whatever we set the value of the argument strTableName to. In our example we set the value of this argument to tblResults so that is the name of the new table we created.
lngRecordsAffected = qdfAction.RecordsAffected
After running the query, we can then inspect the QueryDef object's RecordsAffected property. As its name suggests, this lets us know how many records were affected by the query. In this case it tells us how many records met the criteria we specified and were therefore inserted into the new tblResults table by our make-table query. If you remember, one of the demands made by our users was the ability to see how many records met the criteria they specified. The RecordsAffected property gives us just that ability.
Finally, we close the QueryDef object. Because this was only a temporary query, the QueryDef is not saved as a query in the database window. Strictly speaking we don't really have to close the query as it will be destroyed automatically after the function returns its result. It is always good practice to explicitly use close, however, as this frees up resources more quickly and guarantees that it really does get closed.
Of course, one potential problem is that the next time we run the BuildResultsTable , there will already be a table called tblResults . If we did not pre-empt this situation, then VBA would generate a run-time error telling us that the table already existed. To get around this problem we delete the table before running the query:
On Error Resume Next db.TableDefs.Delete strTableName 'delete the existing table If Err.Number <> 0 Then 'failed for some reason - probably because it doesn't exist 'not a problem - continue End If On Error GoTo 0
The first line ( On Error Resume Next ) instructs VBA that we will deal with any errors that may occur when we try to delete the table. If an error occurs then Err.Number will be set to the error number and the error handling code between the If ... EndIf is run, if not then it will remain and execution will continue with the line after the EndIf . In fact we don't actually do anything if an error occurs as it's probably just because it's the first time we ran this function. In that case there will be no tblResults table to delete. At some point in the future we could easily add some more sophisticated error handling code to check that this really was the case and that we have not encountered a more serious error. For now we'll just leave the comment there to remind us what's going on.
The last line ( On Error GoTo ) instructs VBA not to ignore errors in any subsequent lines of code, that is, this is the end of our error checking code.
You don't have to worry if you don't quite understand the error handling in this section of the code. 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 these lines of code delete the tblResults table if it already exists and doesn't crash horribly if it doesn't.
It might also be worth mentioning that rather than exit the function immediately when we detect an error we instead continue. A series of If ... Then ... End If s make sure that code further down the function is only executed if everything is still OK. This is an alternative method to using Exit Sub s that allows us more control over what code is executed and what is not. We could, for example, add some code to retry some of the operations if they fail or perhaps some code near the bottom to inform the user what errors we have encountered and offering helpful suggestions on how to cure the problems. As is usual with any form of programming, there are a number of different ways to achieve the end result. Unfortunately, there are often no fixed rules on the best choice to make - you must rely on experience.
Now as you can see from the example above, the end result of rewriting this part of our application is that it yields exactly the same results as our previous attempt where we embedded the user's criteria directly within the query itself. So why bother? There are a couple of reasons for implementing this method of building a table of matching records as opposed to merely modifying a query to include the new criteria inline.
The first benefit of the 'matching-keys' method is that we only have to run the complicated query that evaluates which sales meet the user's criteria once, irrespective of the number of times that we want to use those results. By contrast, in the 'modify-query' method we used earlier, that complicated WHERE clause that was used to restrict the sales to those that met the user's criteria was run when both the frmSales form and the rptSales report were opened.
Now there is obviously going to be an overhead involved in creating the initial table of matching keys, and it is really the way that your application works that will determine whether this overhead is one that is worth bearing . In our example there is little difference between the overall time taken to open the form and report using either method. However, in many situations the user will want to select a number of records and then perform a large number of different operations against those records. If there were, for example, 2 forms and 10 reports that needed to be run against these records, we would see the following overheads:
Build SQL string
Build SQL string
Create make-table query
Run complex select query x 12
Run complex make-table query x 1
Run simple select query x 12
Obviously, the more complex the initial query, the greater the benefits of the 'matching-keys' method will be. The initial query might be slow because it contains complex criteria or because the client PC has a modest processor or little RAM. Because the 'matching-keys' method will only need to run the more complex query once, it will tend to give better performance than the 'modify-query' method which will have to run it whenever a form or report based on those results is to be opened.
Another benefit of the 'matching-keys' method comes from the fact that the results of the query are persisted (saved). This means that a user can exit the database and reopen it and the results of the last criteria search will still be there. We will look at the implications of this in more detail in Chapter 11 when we look at what we can do with custom properties.
A final and significant benefit of the 'matching keys' method is the flexibility that this method affords us. Using this technique, we no longer have to base a report and form on the same query. And once we have the key-set saved, we can use this in any subsequent queries without having to deal with the added complexity of redefining the selection of records in those queries.
There are two normal methods of deploying Access database solutions in a networked (multi-user) environment:
The first, most basic, solution employs a single database stored on a shared network drive. This one database contains all of the data as well as the other Access objects (for example forms, queries, reports, and modules) that comprise the application. This solution makes installation very easy.
The second, and generally preferable, solution uses two or more databases. One is located on a shared network drive, and contains the base data in Access tables. The other database(s) are located individually on each user's PC, and contains the other Access objects (for example forms, queries, reports, and modules) that comprise the application. This local database contains links to the base data in the shared network database and the data appears, to all intents and purposes, to reside locally even though it is located elsewhere. This solution may give significant performance benefits as less data has to be passed over the network.
You should employ the second of these two architectures if you want to use either the 'modify-query' or 'matching-keys' method. That is because you want to ensure that the objects that the user is modifying (either tblResults or qryExample ) are objects that belong solely to that user. If tblResults or qryExample existed in a central database that everyone shared, then one user's modifications would affect the result set that everyone else used.
It is also for this same reason that we don't just add an extra column to our main data tables to keep track of which rows have been selected and which haven't - again changes made by one user running a query would be seen by all users.
It is possible to employ the first solution successfully but only if all queries and results tables are additionally identified as belonging to each particular user. We could, for example, add the user's name to the beginning of each table name to achieve this. Generally speaking, though, it is usually easier to employ the second, local table, solution.
For information on how to obtain user names please see Chapter 17: Multi-User.
Something else to be aware of is the fact that constantly deleting and creating tables can lead to a condition that goes by the name of 'database bloat'. What that means is that Access allocates storage space when new objects are created, but does not always reclaim that space when the object is deleted. The result is that frequent deletion and creation of tables will cause the database size to grow steadily, so causing a decrease in performance.
The good news is that this condition is easily cured. Either manually compact the database at frequent intervals or use the new Compact on Close option on the General tab of the Tools Options dialog, which causes the database to be automatically compacted whenever it is closed.
'Compact on Close' only causes the database to be compacted if it calculates that doing so will reduce the size of the database by 256 Kb or more.