Requirements: Knowledge of Visual Basic coding, database recordsets, and SQL
File to download: None
Related Articles: Macros vs Visual Basic Modules, Using Switchboard in Microsoft Access for easy navigation, Use Embedded SQL Query for better efficiency and security, Hide Visual Basic Modules by saving your Access Files in MDE or ACCDE file format
Parameter Query is a query that needs input(s) from the users before it can be run and produced a result. When a user runs a parameter query, a dialog box will pop up and prompt the user for a parameter value (or multiple parameter values). Parameter query greatly enhances the flexibility and versatility of a query.
However, there are some limitations on Parameter Query. Below are some of the concerns:
Let’s look at a project that I did before. The project had these requirements:
If I wrote a parameter query for each report, the users would have ended up inputting same information over and over again if multiple reports needed to be generated. If a deal requires 15 reports, there will be a hell of input for the same user. In addition, you could expect that the users would have made some mistakes on the inputs, especially some deal names are long and tricky and typo errors are almost unavoidable. If you were the person who created the Access model, you could be ready to get some very unappreciated comments.
When I designed the Access program, the number one concern to me (besides accuracy) is the user-friendliness. There was a whole department of analysts that would use the program. I wanted to make sure that the program was easy and intuitive to use. New users could learn to use it with just minutes of instructions. The number of inputs and the typo errors must be minimized. The second concern was the flexibility of the program. The users should be able to run any report with any time period that they specified. The final concern was the database integrity. I wanted to make sure the users would not accidentally or intentionally change anything to the program. I wanted my program could last for years. As a testament, the program that I designed has been used for more than ten years already.
That was what I did for the project:
Please note that the below procedures require intermediate to advance knowledge of Visual Basic coding, database recordsets, and SQL. This article is to give the readers some ideas how to create Embedded SQL Parameter Query. It is not intended to teach the readers all the basic knowledge of VB coding, database, and SQL. Furthermore, there are some company secrets that I can not disclose here, so I cannot offer the readers the entire SQL query and the VB codes. I apologize that in advance.
Step 1: Created a table called “Control”. You can name it as “Parameters”, “Input”, etc. if you want to.
Step 2: Created a form for the input parameters. All the inputs will be saved to the “Control” table. All the reports use the same Input form. So every time when a user picks a report, the form will pop up and grab the existing data from the “Control” table. The parameters will be kept the same until the user change them. In this case, if a user needs to generate 15 reports for the same deal with the same start date and end date and same output file directory, the user just needs to click “OK” button to generate reports without further inputting any parameters.
Please note that there should only be one record in the Control table. I put in some VB codes that will automatically delete any redundant record.
To provide further convenience and maintain data integrity, there is a drop down button for the “Deal” field. In fact, I could have added two Active-X calendar icons for the Start and End Date field, but many of the users felt that manually typing in the dates are faster and easier.
Step 3: Below is the original design of the Parameter Query. It uses the regular query in the MS Access. You can see that the “Criteria” field links to the “BeginDate” and “EndDate” of the “Control” table. Everything works well except that the Parameter Query runs awfully slow. Somehow MS Access allows you to put in a “no-join” table in the query but it runs ten times more slowly than a regular Parameter Query that requires users input. The problem got worse as time went by when the size of the database kept growing. The query ran more and more slowly every day.
Using the Embedded SQL query will make your query run at least the same speed as the regular Parameter Query (or might even be faster). In addition, you can save your file into MDE or ACCDE format so your query is now completely hidden from users. This will significantly enhance the integrity of your MS Access database.
Step 4: This is an improved way by embedding the query into VB codes.
First you need to create a subroutine in a VB module. The data are pulled from the “Control” table a SELECT SQL statement. The data from the recordset are put into pre-defined variables
Set rs = DB.OpenRecordset("Select File_Directory, BeginDate, EndDate, DealID, ReportChoice from Control")
FileDir = rs![File_Directory]
BeginDate = rs![BeginDate]
EndDate = rs![EndDate]
DealID = rs![DealID]
ReportChoice = rs![ReportChoice]
Then you put in codes to call the report subroutines. In below example, if the user chooses the first report from the Switchboard, the “ReportChoice” variable will be assigned to “1” through the Subroutine RunReport1()). The “ReportChoice” field is also stored in the “Control” table
Dim DB As Database, rs As Recordset
Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Select ReportChoice from Control")
rs.Fields("ReportChoice").Value = 1
When “ReportChoice” field is “1” (Case = 1), the subroutine of the main program will call the “CalcABC” subroutine. Please note that there are 3 fields (DealID, BeginDate, EndDate) to be passed to the subroutine.
Select Case ReportChoice
Call CalcABC(DealID, BeginDate, EndDate)
Call CalcDEF(DealID, BeginDate, EndDate)
Below VB coding shows the report subroutine. Please refer to this article, Use Embedded SQL Query for better efficiency and security, to see how to convert a regular query to an Embedded SQL query. You can see that the parameters are now embedded in the SQL query statement (e.g. & qry_BeginDate &).
Sub CalcABC(qry_DealID, qry_BeginDate, qry_EndDate)
DoCmd.RunSQL ("SELECT … FROM … WHERE (ABC_Date Between #" & qry_BeginDate & "# And #" & qry_EndDate & "#) AND (ABC_Deal & qry_DealID & ");")