The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Reduce Infrastructure Dangers – Don’t Use Queries as the Source for Forms and Reports

Introduction

In my career as an Access Developer, I have been called in periodically to take over a project where the original developer got in over his or her head or just was unavailable to continue supporting the application that s/he built.

This can be a very challenging situation. When you are faced with troubleshooting and maintaining a database someone else has created, one of the tasks is to get inside that person’s head to determine how s/he did certain things or why s/he chose one solution over another. One of the biggest mistakes I see other developers make is something that is very easy to avoid, something you should avoid even if you are the only person who ever uses the database. I’m talking about the source for reports and forms. My message: Don’t use queries.
 

Microsoft Invites You to Make the Mistake

Everything I say from this point on pertains to both forms and reports. I will be focusing on forms; however, know that everything I say about forms also applies to reports.

The reason that people end up using queries for forms and reports is because Microsoft paves the road for you to do exactly that. When you start the form wizard, you get the dialog box below.


 
You see that the source for the form is limited to tables and queries. So it is natural to assume that they would be the appropriate source. Keep in mind that when Microsoft designed Access, they were trying to make it as easy to use as possible for people who knew little or nothing about databases or software development. In their wisdom, they lead users into selecting a table or query as the source for a form.
 

Why It Is Bad

There are several downsides to using a query in this way. The first is that the query used appears in the navigation pane. There is a possibility for you or another user to change (or even delete) the query without knowing that it is the source for your form. In many cases, changing a query is not an issue. For instance, if the only change is adding new fields, then little harm is done. But in other circumstances it can spell disaster, especially if they change the logic behind the query so that it pulls a different population. If it is sitting out in the open anyone can double-click the query and make changes that may trash your form.
 
Second, you may be tempted to use a query from one form for a second form if both forms use the same data. You might think that this is OK, and in the short run that may be true. However, experience tells us that what is true today may not be true tomorrow; database applications tend to change, morph, and mature over time. So if you are using the same query for two forms and your need for one of the forms changes, you may change logic behind the query for the first form and forget that the same query is used in another form. If this happens, you have built yourself a problem which will come back to haunt you downstream.
 

The Solution

Behind every form there is a record source. You can identify it by opening the form in design view and finding the record source property on the Data tab. If you have used a query or table, this is where you’ll see it.
 
If you click on the downward facing black triangle that I’ve highlighted below, you are invited to choose an existing table a query.  The trick is, rather than hitting the triangle combo box button, click on the ellipsis (…) directly to its right. When you click the ellipsis, you will be sent to the query design window where you can see the current source and change it, if needed, by manipulating the query in that window.
 

 
If you have already based your record source on a query, you will have to delete the query from that window before proceeding. You can certainly keep the query in your database; however you must disconnect the query as the source for the form.
 
The key is to get the SQL statement out of the query. This is not a hard operation; for details, see the June 2005 Access Wizard.
 
The process is as follows:
  1. Open the source query and copy the SQL on to your clipboard.
  2. Open the form in design view and choose the data source property.
  3. Delete the existing record source if it is a query or table.
  4. Click the … button and paste the SQL statement as the source for your form.
Now you can go into your form to change the query knowing that you are able to do it without affecting any other objects in your database. You have this freedom because that particular SQL statement is now embedded as part of the form and is available to change from within form and no place else. 
 

Conclusion

This month, I showed you a way to avoid problems over time by isolating a form’s record source by embedding it in the form itself rather than using a query. With this approach, you can rest assured that you will not accidentally change the query that could destroy your form.
 
A problem prevented is much easier to deal with than one that has to be solved.
 

Tip of the Month: Get the Current Name and Full Path of Your Database


Sometimes when working in your database, it would be helpful if you can get the full name and path of your application. There is a really easy way to do this: Use the current project object.
 
To get this information, open up the immediate window in your database by pressing control-G. This will bring you to a code window where you can begin typing. Enter the following line:
? currentproject.FullName

This will return the fully qualified path and name of your database. If you want just the name, you can use
? currentproject.name

If you need just the path, you can use
? currentproject.path
 
This particular statement is something that you can also reference in your code if you want to give the user information about the location of the database.  
 

 

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886