|| Home | Excel | Access | Word | Outlook | FrontPage | VBA | Downloads | Index ||
So, now we have the required components of the multi-purpose query. The next step is to write the code to make them work together.
Writing the VBA and SQL Code
When the user clicks the dialog's OK button several things must happen:
The code to carry out these operations will run on the On Click event of the OK button.
In form design view right-click the OK button and choose Properties to open its properties window and locate On Click on the Events tab. Click in the white bar then click the Build button: . In the Choose Builder dialog select Code Builder and click OK. You are now ready to write the code...
Declare and Fill the Variables
The first few lines of code establish contact with the database, telling Access that we are referring to the current database (i.e. the one containing the code) and identifying the query that we are going to work on. In addition, a string (i.e. text) variable is declared, which I have called strSQL. It will hold the SQL statement that will be applied to the query:
Dim db As
NOTE: I am using DAO language here because I think it is simpler for this sort of work. DAO is the default for Access 97, but the default for Access 2000/2002 is ADO. Access 97 users need do nothing (you can omit the "DAO." bits if you want but it doesn't really matter) but Access 2000/2002 users need to set a reference to DAO so that their database understands the code. In the Visual Basic Editor go to Tools > References. In the dialog box scroll down to "Microsoft DAO 3.x Object Library" (where x is the highest number if you have more than one) and put a tick in the box. Click OK to set the reference. You only need to do this once for the database and any code you put in it will be able to use the reference.
Build the SQL Statement
Next comes a VBA statement which places a text string into the strSQL variable. There was a detailed explanation of how I like to write my VBA/SQL and the rules you need to know in the second tutorial in this series: Access and SQL Part 2: Putting VBA and SQL Together.
This code combines SQL keywords and clauses into which have been placed references to the combo boxes that contain the user's criteria choices:
= "SELECT tblStaff.* ” & _
Although it doesn't seem to make a readable SQL statement as it is, when Access reads the code and substitutes, for example, Me.cboOffice.Value with London a sensible SQL statement results.
Here is what your code should look like so far [click the thumbnail to see a full-sized image]:
Test the Code
Now is a good time to test the code you have written so far and there are a couple of ways you can do this. You can "print" the SQL to the Immediate Window or you can display it in a message box (or you can do both!). Here's what to do:
Using the Immediate Window:
In Access 97 the Immediate Window is called the Immediate (lower) pane of the Debug Window. In all versions the window can be displayed by pressing Ctrl+G from the VBA code window. First, add the following line of code before the End Sub line of your cmdOK_Click procedure:
Using a Message Box:
Add the following line of code before the End Sub line of your cmdOK_Click procedure:
Now you are ready to run a test. Switch to Access and open your dialog box in Form View (now is a good time to save the form!). Make some choices from the combo boxes and click the OK button...
If you chose to use a message box it will open displaying the SQL string that your code created from the choices in the dialog box. Read the SQL statement to check that it makes sense:
If you chose to use the Immediate Window, switch to the VBA code window and press Ctrl+G to open the Immediate Window where the SQL string will be displayed (it is written in a single line):
The message box method is quick and, because I am familiar with SQL, it is my preferred method. Using the Immediate Window has the advantage that you can select and copy from it the SQL statement that your code generated and paste it into a query to test it. To do this first select and copy the SQL statement then return to the Access database window and choose Create query in design view. Close the Show Table box and open the SQL window by choosing View > SQL View. Delete any entry that is already there (it usually shows SELECT;) and paste in your SQL statement. Run the query and check the result.
If you see an error message when you try to run your SQL you can trace the error and correct it. Read the section on "Debugging Your SQL Code" in Part 2 of this series. When you are satisfied that this part of your code is working properly, you can remove the line(s) Debug.Print strSQL or MsgBox strSQL.
Update and Open the Query
All that remains is to apply the SQL statement to the stored query that you saved earlier, and to close the dialog box.
Enter the line:
qdf.SQL = strSQL
to apply your new SQL statement to the stored query. There is no need to give a command to save this change to the query because this happens automatically. Next add the lines:
to open the query displaying the results of the user's criteria choices, and to close the dialog. Finally add the lines:
Set qdf = Nothing
which empty the variables used to identify the query and the database. All variables normally lose their values when a procedure finishes, and the memory that was allocated to them is freed. But sometimes Access forgets to clear "object" variables (those with which you have to use the "Set" keyword) so it is good coding practice to clear them manually like this, just to make sure. Your finished code should look like this [click the thumbnail to see a full-sized image]:
Return to the Access database window and save the dialog box form (to save your code changes).
Your Multi-Purpose Query is now ready to run. Remember that the query should be run from the dialog box. If you open the stored query it will display records using the same criteria as the last time it was run.
The Multi-Purpose Query will work fine as it is, but you can make it even more user-friendly with the addition of a few refinements. The second part of this tutorial shows you how to turn your multi-purpose query into a really professional tool.