Microsoft Access 2010 Bible

Chapter 20: Advanced Access Report Techniques

739

Trying More Techniques

As you’ve probably discovered by now, reporting in Access is a very large topic. I’ve included a few

additional techniques that will help you make your reports even more flexible to users.

Displaying all reports in a combo box

The names of all the top-level database objects are stored in the MSysObjects system table. You

can run queries against MSysObjects just as you can run queries against any other table in the

database. It’s easy to fill a combo box or list box with a list of the report objects in an Access database.

Choose Table/Query as the RowSource Type for the list box and put this SQL statement in the

RowSource of your list box to fill the box with a list of all reports in the database:

SELECT DISTINCTROW [Name] FROM MSysObjects

WHERE [Type] = -32764

ORDER BY [Name];

The -32764 identifies report objects in MSysObjects, one of the system tables used by

Microsoft Access. The results are shown in Figure 20.31.

Note
Reports don’t have to be open for this technique to work. MSysObjects knows all the objects in the data-
base, so no reports will escape detection using this technique.

FIGURE 20.31

frmAllReports displays the reports in Chapter20.accdb.

If you’re using a naming convention for your database objects, use a prefix to show only the

reports you want. The following code returns only those reports that begin with tmp:

SELECT DISTINCTROW [Name] FROM MSysObjects

WHERE [Type] = -32764 AND Left([Name], 3) = “tmp”

ORDER BY [Name];