how to address a report control from a form in ms/access vba

Posted on 2016-11-13
Last Modified: 2016-11-16
I need to change, from a button in a form, the source of a subreport.

currently, my code is

    Reports![sreport_fichaProximidade].Report.RecordSource = Forms![_commonVariables]![strSetSQL]
    DoCmd.OpenReport "fichaProximidadeFrame", acViewPreview

but I am getting an error as the [sreport_fichaProximidade] control is unknown

How should I refer to it?

thanks for the help
Question by:jirdeaid
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41885259
The issue in this case is that the report is not yet open. The Reports! collection only contain open reports.

Have you tried to reverse the order?
DoCmd.OpenReport "fichaProximidadeFrame", acViewPreview
Reports![sreport_fichaProximidade].Report.RecordSource = Forms![_commonVariables]![strSetSQL] 

Open in new window

I believe the requery is not required in this situatoin (i.e. it should requery on its own when the recordsource is changed)

If this doesn't work, it might be better to open the form hidden first, do the modification, and then change it to visible like below:
DoCmd.OpenReport "fichaProximidadeFrame", acViewHidden
Reports![sreport_fichaProximidade].Report.RecordSource = Forms![_commonVariables]![strSetSQL] 
DoCmd.OpenReport "fichaProximidadeFrame", acViewPreview

Open in new window


Author Comment

ID: 41885280
I need to postpone this by a bit...

my code was perfectly working and, when I pasted in your solution, in a button on the form, I am suddenly getting the error  459 "object does not support this class of events"

I know it's a different matter but do you have a clue, or shall I open a new question?
LVL 21

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41885413
you can set the report RecordSource before you open it for viewing by calling this procedure first:
Sub SetReportRecordSource(pReportName As String, pRecordSource As String)
   ' written by crystal (strive4peace)
   ' pReportName is the name of your report
   ' pRecordSource is a valid Record Source (SQL, Tablename or Queryname)
   ' USEAGE:
   ' Call SetReportRecordSource ("MyReportname","QueryName")
   ' SetReportRecordSource "MyAppointments" _
      , "SELECT A.* FROM Addresses A WHERE City='Detroit';"
   On Error GoTo Proc_Err
   '---------- declare variables
   Dim rpt As Report
   '---------- open as hidden if you don't want to see what is happening
   DoCmd.OpenReport pReportName, acViewDesign ', , , acHidden
   Set rpt = Reports(pReportName)
   '---------- set report Record Source
   rpt.RecordSource = pRecordSource
   '---------- save and close the changed report
   DoCmd.Save acReport, pReportName
   DoCmd.Close acReport, pReportName
   '----------  Release object variable
   Set rpt = Nothing
   On Error Resume Next
   Set rpt = Nothing
   Exit Sub
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SetReportRecordSource "

   Resume Proc_Exit
End Sub

Open in new window

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Assisted Solution

by:Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP) earned 250 total points
ID: 41885441
Just a word of caution. The code posted by Crystal will not work in a ACCDE file (Which may or may not be relevant, just saying)
LVL 21
ID: 41885451
> "not work in a ACCDE file"

that is true.  You can change the record source of a report (or form) in its OPEN event if you deploy an ACCDE.  The Recordsource can be passed in OpenArgs or be a database property or be read from the form, ...

Rather than this, however, if you base the report on a query, you can change the SQL of the query before the report is opened.  This will work also in an ACCDE.

Author Comment

ID: 41885474
well... something weird is now happening.

My code is:

Private Sub btn_reportProximidade_Click()

    Dim strSetSQL As String

    DoCmd.OpenReport "fichaProximidadeFrame", acViewHidden
    Debug.Print "report:" & strSetSQL
    Reports![sreport_fichaProximidade].Report.RecordSource = Forms![_commonVariables]![strSetSQL]
    DoCmd.OpenReport "fichaProximidadeFrame", acViewPreview
End Sub

where [sreport_fichaProximidade ] is the name of the control of the subreport inside the main report.

1. first it does print a  page containing the complete query per record. I have 16 records, so I get 16 compelete pages (!)
2. it does no show the preview - it directly goes to print in a printer :(
3. the control is no recognized...

can you help?


LVL 21
ID: 41885504
acViewHidden is not something Access recognizes.

acHidden can be specified as a window mode:

If you want to change the RecordSource for a report and are NOT deploying as an ACCDE, then please refer to the code I gave you to run.  If this will be an ACCDE, then change the RecordSource in the OPEN event of the report.

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41885973
I am so sorry. That was my bad, I got the syntax wrong. As Crystal indicates, the hidden is a window property, not a report property. I have corrected the syntax below.
DoCmd.OpenReport "fichaProximidadeFrame",acViewPreview,,,acHidden
Reports![fichaProximidadeFrame]![sreport_fichaProximidade].Report.RecordSource = Forms![_commonVariables]![strSetSQL] 
DoCmd.OpenReport "fichaProximidadeFrame", acViewPreview

Open in new window


Author Closing Comment

ID: 41890366
Thanks for the help!!!!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question