You are here:

Using MS Access/Refer to a subform control in macro


QUESTION: I have a main form and a subform. In the main form I have a combo box where I select the report I want to show (the combo box reads all existing reports). The report should be shown in the subform with the help of a macro.
In Access 2010 this is how the macro looks:
Browse to
Object type: Report
Object Name: [cboReports]
Path to Subform Control: frmReportmain.Reportsub

I did put the subform (Reportsub) in the details section as an unbound form and in the combobox I added this embedded macro in the after update event.
The macro runs but nothing happens when I select a report in the combo box.
What is the mistake?

ANSWER: You can't show a report in a Subform. What you CAN show is the query the report is based on. If you are just trying to show the name of selected report on the subform, I see no reason or value in doing so.

But if you want to set a control on a subform to a value selected in the main form you can use VBA code to do so:

Me.subformname.Form.Controlname = Me.controlname

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for your help. OK I cannot show a report in a subform. But what I want to achieve is to have a combobox and select a report. Whatever report I select I want to have the combobox still visible together with the selected report and be able to select another report.
What should I do to accomplish that?
Grateful for your first answer. Hope to get some more :)

OK, I do this frequently. There are a couple of ways to accomplish this. One way is to have the RowSource of your combo be like this:

SELECT Name FROM MSysObjects WHERE Type = -32764;

This will list ALL reports. There are, however a couple of drawbacks to this. First, The Name of a report could be cryptic and the user may not understand the name. Second, there may be reports you don't want the user to see. Third, some reports need to be run from a form that supplies criteria to filter the report.

So what I usually do is create  table or reports:


The RowSource of my combo becomes:

SELECT Reportname, Description, Formname FROM tblReports ORDER BY Reportname;

I also set the bound column to 1, the Column Count to 3 and the Column Widths to 0";2";0". This way only the Description column shows.

In the After Update event of the combo I have the following code:

If Not IsNull(Me.cboReport.Column(2)) Than
   DoCmd.OpenForm Me.cboReport.Column(2)
   DoCmd.OpenReport M.cboReport, acPreview
End If

So if the report requires a form it opens the form otherwise it previews the report.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.