Use Input Form for Parameter Query

However, there are some limitations on Parameter Query. Below are some of the concerns:

• If a user needs to run the query frequently, it will be tedious to input the same parameters over and over again. This gets worse if multiple parameters are required to be put in.
• Users may misspell words or have typos. Parameter Query does not allow you to put in a drop down menu or an Active-X calendar for easy selection.
• Like other queries, Parameter Query is not secure. Users can accidentally or intentionally make changes to the query if no security measures are in place.

Let’s look at a project that I did before. The project had these requirements:

• There were many deals in the Oracle database that required financial reporting.
• The Oracle database linked to the Microsoft Access model through ODBC connection.
• There were some very complicated reports that were written in the MS Access database.
• Each deal required some of the reports. The requirement of the reports is different from deal to deal. In addition, reports might be required to be generated in different time basis. Reports might be needed to be generated on daily, weekly, monthly, or quarterly basis. All the requirements are really based on clients’ requests.
• To provide maximum flexibility, users were allowed to run different reports for each deal on an individual basis.
• Users needed to put in a few parameters every time they ran the reports for their deals, notably the deal name, the start date, end date, and the file directory that the reports to be saved.

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:

1. Put in a Switchboard for easy navigation. Please refer to this article for more information (Using Switchboard in Microsoft Access for easy navigation).
2. Put in an Input Form with Drop-down menu for the Parameter Queries.
3. Converted the queries into SQL queries and embedded them into Visual Basic Modules. Please refer to this article for more information (Use Embedded SQL Query for better efficiency and security)
4. Saved the file into MDE and later ACCDE format to enhance the security of the database. . Please refer to this article for more information (Hide Visual Basic Modules by saving your Access Files in MDE or ACCDE file format)

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

Sub RunReport1()

Dim DB As Database, rs As Recordset

Set DB = CurrentDb()

Set rs = DB.OpenRecordset(“Select ReportChoice from Control”)

rs.Edit

rs.Fields(“ReportChoice”).Value = 1

rs.Update

rs.Close

DB.Close

End Sub

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

Case 1

Call CalcABC(DealID, BeginDate, EndDate)

Case 2

Call CalcDEF(DealID, BeginDate, EndDate)

Case 3

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 & “);”)

End Sub