How to give your Access users flexible reports using simple list box functionality
Reports are a funny thing. You can build a dozen different reports in a small
database, and still the user will complain that, ďThe important fields just
arenít shown here.Ē Take the most popular form of report, for instance, what
I call a list report. This is the simplest of all reports. Along the top of
the page, there are field headings, and below, there are rows of actual data.
Each line is a separate record.
Generally speaking, users will use this kind of report as a quick summary.
The database that lists customers, for instance, may contain 30 or more fields
for each customer, but a simple report showing their names, phone numbers and
value of orders will often be enough to let the telemarketing team target a
particular call out exercise. This is, of course, until the time that someone,
somewhere, wants one or more of those other 30 fields just added to the report.
This is easy, of course, if youíre using a full installation of Access and
you know how to create your own reports. But for the ordinary data user,
who doesnít go (or isnít allowed to go) under the hood, this is unviable. With
a little work, you can give users of your Access application—even runtime
version users—the ability to add fields to standard reports without having
to work in the Report Design window. If they can work a pull-down list, they
can make a custom report this way.
Letís start by looking at a pretty simple database that Iíll be basing this
There it is. For now, itís just a single table, a good, old-fashioned flat
file database. For our custom report, that will be the data source, but it
could as easily be a query, bringing together information from a far more complex
Thereís a report Iíve set up, too, which is again pretty conventional:
Design View, though, shows that certain planning has gone into the architecture
of the report:
In the Page Header, we start with Surname and Forename, and the text boxes
directly below in the Detail section display the values in these fields. These
two will be the only fixed items on the report. There are four labels to the
right of these in the Page Header, together with corresponding text boxes below
in the Detail section.
I have named these objects as below:
And this is the key to the entire process. What I will need my code to do
is to write new values to the four label objects and the four text box objects
to change them into different fields.
But Iím getting slightly ahead of myself, because what I need first is a mechanism
for the user to choose what fields he or she wants in the report. Iíve built
a form to do this:
Those four combo boxes area called cboField1, cboField2, cboField3 and cboField4.
You can probably see a pattern emerging here. When we get down to the code,
by the way, youíll be very grateful Iíve used prefixes in line with a common
To set the lists for these combo boxes, I used Accessís little-known ability
to use table or query field names as a row source:
When selecting ĎField Listí as the RowSourceType property, I am then offered
a list of tables and queries as the RowSource. I chose our main table, of course,
which is the record source for the report:
Once this is set, the combo boxes list the field names from the table, thus:
I canít put it off any longer: itís time to do some code. Hereís what I want
the code to do:
Read the value the user selected in cboField1
Check whether that value is a Ďnullí
If it is a Ďnullí then blank both
lblField1 and tbField1 in the report
the field name selected as the Caption property
the field name selected as the ControlSource property
Repeat for the other three combo boxes
Hereís the code, which I put in a new module:
Option Compare Database
On Error GoTo Err_MakeReport
'Open report in design view to write properties to objects
DoCmd.OpenReport "rptCustom", acDesign
'Read combo box selections and use subroutine to set report object properties
SetReportControls Forms!frmChooseFields.cboField1.Value, _
SetReportControls Forms!frmChooseFields.cboField2.Value, _
SetReportControls Forms!frmChooseFields.cboField4.Value, _
'Close design view without prompting to save changes
DoCmd.Close acReport, "rptCustom", acSaveYes
'Open finished report in preview view
DoCmd.OpenReport "rptCustom", acPreview
Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)
'Check if selection is 'null'
If IsNull(varFieldName) Then 'Blank out the relevant objects
conLabel.Caption = " "
conTextBox.ControlSource = ""
Else 'Write the selected field name to the appropriate objects
conLabel.Caption = varFieldName
conTextBox.ControlSource = varFieldName
I also need just a little code behind my form to make the buttons work:
Option Compare Database
Private Sub btnCancel_Click()
Private Sub btnMakeReport_Click()
Weíre now up and running. Just to test it all, hereís my selection on the
And here is the report I get when I hit the button:
So, there we are. Within the context of an Access application, this is a very
powerful technique and one which I know from experience can dramatically increase
your usersí satisfaction with the system.