In this article, Frank Kegley illustrates a technique where a query is constructed, used, and destroyed via Visual Basic for Applications code that executes during the lifetime of the form or report that uses it.

SEVERAL years ago, I worked for a company that had two offices. After creating a rather complex Access report and e-mailing it to the other office, I then hadto talk the rather naïve Access user at the other end of the phone through several export operations. Needless to say, it would have been a lot faster and a lot less nervewracking if I had just driven the 150 miles to the other office and installed the report myself. If I had only known then what I know now.

Many Access developers, myself included, base forms and reports on queries. However, allowing queries to exist independently in your Database window isn’t necessary and can create problems. What kinds of problems? Even authorized database users sometimes harm the database by running queries outside of their intended form/report environment. Users have even deleted queries from the database, preventing the application from running correctly. It can also be difficult to keep track of which query or queries produce which forms/reports, making it more difficult to export and maintain forms/reports than it needs to be. While you can use SQL from your code, it can be difficult to read when intermixed with VBA.

Putting SQL statements in queries causes them to be compiled before executed (successful compiles also let you know that your SQL is valid). SQL submitted from code isn’t compiled until it’s run, which can cost you some extra time for complex queries. Using my “Just-InTime” technique, queries are kept around for just the amount of time that the form that’s based on the query is being used. This means that the SQL is compiled when the form is loaded, but then isn’t recompiled for the life of the form.

One warning: Just as it should be easier to export and maintain one object rather than several, it’s also easier to delete one object rather than several. This can make recovering from the deletion of “Just-In-Time” forms/ reports much more difficult.

In this article, I’ll show you a technique that I call “Just-In-Time” (JIT) queries, where the query is constructed, used, and destroyed via Visual Basic for Applications (VBA) code that executes during the lifetime of the form or report that uses it. As a result, JIT queries don’t exist independently of the forms and reports that use them; there’s only the one object. Since I’ll be using DAO to implement this technique, my code will work with Access 97, 2000, and 2002.

Form/sub-form

Many times, records are presented to the user through a form/sub-form combination. The tables involved must be in a one-to-many relationship. The main form presents records from the “one” table and the sub-form presents associated detail records from the “many” table. In the example for this article, I created a form in the Northwind database (using the Form Wizard) that displays the CustomerID, CompanyName, ContactName, Phone, and Fax fields from the Customers table. I also created a sub-form that displays the OrderID, CustomerID, OrderDate, ShippedDate, and Freight fields from the Orders table. I called the main form frmCustomersAndOrders and the subform sfrmOrders.

For an article on how you can dispense with separate queries, my next steps may seem perverse: I created two queries. My first query was on the Customers table and fetched the fields that the main form needed (qryCustomers). I called the equivalent query on the Orders table, qryOrders. I used these queries to generate the SQL that I’d need in my program. So, once the queries had been created, I opened qryCustomers in Design view, copied its SQL statement, and closed it (later, I’ll delete the query from the database).

With the SQL statement on the clipboard, I opened my frmCustomersAndOrders in Design view and erased the RecordSource property set up by the Form Wizard. I then opened a code window. Check your references and, if it’s missing, add a reference to the DAO library and move it above the ADO library.

Since I’ll be using two variables throughout the whole code module, I’ll save myself a little bit of typing by declaring them so that they’ll be known throughout the whole code module. So, after being sure that I’m at the top of the code window and above any procedure, I typed in the following lines:

Dim db as Database
Dim qd as QueryDef

Since I can reach the objects and methods of a database by using the Access database window, I think of an object of type Database as referring to the Database window. My qd variable will eventually refer to a query object. The variables db and qd will now exist as long as frmCustomersAndOrders does.

Although there are many events that occur during the lifetime of a form, I’ll only be concerned with three of them, the Open, Current, and Close:

• Since the Open event is the first event to fire, I’ll attach code to it that creates my “Just-In-Time” qryCustomers and sets the RecordSource property of frmCustomersAndOrders to use it.
• After the Open event fires, the Current event will fire as Access fetches the first Customer record. I’ll attach code to that event to create a JIT qryOrders, set the RecordSource property of the sub-form to qryOrders, and delete the qryOrders. Because the Current event fires each time the end user navigates to a different customer record, qryOrders will be created, used, and destroyed over and over throughout the lifetime of the form.
• The Close event fires when the form is closed. In this event, I’ll place code that destroys qryCustomers.

In the Form_Load event, I insert this code to create my query:

Set db = CurrentDB()
Set qd = db.CreateQueryDef("qryCustomers")

The first line tells Access that I want db to point to the currently open database—in this case, Northwind. The second line invokes the CreateQueryDef method of db to create a query definition called qryCustomers. The query name must be new to the database. Since both a database and a query definition are objects, I must use the Set verb to assign values to db and qd.

A query must have a SQL statement to do its job, so I’ll set qryCustomers’ SQL property by using the query’s SQL property. Now I need to paste the SQL statement that has been waiting patiently on the Clipboard. It isn’t usable in its current form, so I’ll need to clean it up so that it’s enclosed in quotation marks and that it concatenates properly. Here’s the way I like to do it:

qd.SQL = "SELECT Customers.CustomerID, " _
& "Customers.CompanyName, " _
& "Customers.ContactName, " _
& "Customers.Phone, " _
& "Customers.Fax, " _
& "FROM Customers"

You can, however, put it all on one line. Since the SQL property is a text string, I don’t need to

use the Set keyword. Once the SQL statement is correct, I need to assign the query to the form’s Record Sourceproperty. Here’s the line that does that (Me refers to the current form):

Me.RecordSource = "qryCustomers"

That’s all the code for the Open event. I’ll need to delete qryCustomers from the database when the form closes, so in the Form_Close event I’ll place the following lines:

Set db = CurrentDB()
db.QueryDefs.Delete("qryCustomers")

The Delete method of the QueryDefs collection will delete my qryCustomers object from the collection.

After closing the code window, I save frmCustomersAndOrders. This is typically when I delete the qryCustomers query that holds my SQL statement. Now that I have it in my code, I don’t need this copy.

The sub-form

After opening qryOrders in Design view, I copy its SQL statement to the Clipboard and close the query as I did with my qryCustomers. I’ll place the code for this query in the form’s Current event, which fires when the first record is displayed and every time the user navigates to another record.

Here’s the start of the code for the Current event:

Set db = CurrentDB()
Set qd=db.CreateQueryDef("qryOrders")

Queries are pretty worthless without a SQL statement, so I need to add it to my code:

qd.SQL = "SELECT Orders.OrderID, " _
& "Orders.CustomerID, " _
& "Orders.OrderDate, " _
& "Orders.ShippedDate, " _
& "Orders.Freight, " _
& "FROM Orders "_

However, I don’t want to retrieve all the orders in the database—just the orders for the current customer. To make that happen, I must now add a WHERE clause to the SQL statement that retrieves only the records from the orders table that match the ID of the customer currently being displayed on the main form. So, on the next line, I type the following:

& "WHERE Orders.CustomerID = " _
& "'" & Me.CustomerID & "'"

Me.CustomerID is the text box holding the CustomerID field from the Customers table. Orders.CustomerID is the CustomerID from the “many” table.

The next lines should look familiar, as they’re very similar to the ones that I used in the main form:

Me.sfrmOrders.Form.RecordSource = "qryOrders"
db.QueryDefs.Delete("qryOrders")

The first line sets the RecordSource property of the main form’s sub-form control. The second line invokes the Delete method of the database to delete qryOrders from the QueryDefs collection.

Before testing, I deleted the qryOrders query from the database, and then opened frmCustomersAndOrders and navigated through some customer records—verifying that the sub-form displays the corresponding orders from the Orders table. After minimizing frmCustomersAndOrders and opening the Queries window, I verified that my queries didn’t appear in the list of queries (just to make sure that I’m successfully deleting the queries). My report is now a “Just-In-Time” query.

Report

To construct a JIT report, I’ll begin by constructing a query that presents the same data from the Customers and Orders tables as frmCustomersAndOrders. I used the Report Wizard to construct a report based on qryCustomersAndOrders (rptQustomersAndOrders).

Reports are enough like forms that I can copy much of the code from my customer form to the report. This includes:

• The two module-level variables.
• The lines from the frmCustomersAndOrders’ Close event to the report’s Close event (I change the name of the query in my code to qryCustomersAndOrders).
• The lines from the frmCustomersAndOrders Form_Open event to the Open event of rptCustomersAndOrders (again, changing the name of the query).

After that, it’s just a matter of copying the SQL statement from my query into the code of my report:

qd.SQL = "SELECT Customers.CustomerID, " _
& "Customers.CompanyName " _

& "Customers.ContactName, " _
& "Customers.Phone, " _
& "Customers.Fax, " _
& "Orders.OrderID, " _
& "Orders.CustomerID, " _
& "Orders.OrderDate, " _
& "Orders.ShippedDate, " _
& "Orders.Freight " _
& "FROM Customers INNER JOIN Orders " _
& "ON Customers.CustomerID = Orders.CustomerID"

Before testing, I deleted qryCustomersAndOrders. By the way, this gives you an opportunity to see the benefits of using “Just-In-Time” queries: After opening rptCustomersAndOrders, minimize it and open the Queries window. You should see your qryCustomersAndOrders in the list of queries. You can open this query and check your SQL statement. This lets you debug any problems with your SQL without having to decode it from your VBA code.

Conclusion

Although I’ve created Select queries, the technique can be used to create other kinds of queries and other kinds of objects. The creation code can be attached to other events also, such as the Click event of a command button.

There are several reasons to use this technique:

• If you ever need to Export a report or form created with this technique, there’s only one item involved in the Export operation. You might even be able to arrange a VBA startup routine to import the items into the user’s databases.
• You’ll have no more worries about queries being executed outside of their normal form/report environment. Several times in my career, I’ve had to undo the actions of a query that was never intended to be executed as a standalone.
• The technique eliminates worries about queries being deleted from the database. I’ve also, on several different occasions, had to reconstruct queries that had been deleted from the database.
• You’ll no longer have to remember which queries belong to which forms/reports. While it’s true that newer versions of Access make this easier by allowing you to form groups of the objects that contribute to the same goal, you now may not even have to do that.

There’s one big reason not to do this: If a user deletes a form or report from the database that’s been created with this technique, it becomes much more difficult to reconstruct the deleted item. My suggestion is that forms/reports created with this technique be kept in the “back room” where the developers live and exported as needed to the user databases.

Other Pages On This Site You May Be Interested In

Logging Access Startup Details and Version Control
Simplifying Complex SQL
Access Subquery Techniques
Combining Tables using Union Queries
Tame the Crosstab Missing Column Beast
Subquery Notes