Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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

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]
    Reports![sreport_fichaProximidade].Report.Requery
   
    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
ss1
ss2
0
João serras-pereira
Asked:
João serras-pereira
  • 3
  • 3
  • 3
2 Solutions
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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

0
 
João serras-pereiraAuthor Commented:
Hi.
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?
Screen-Shot-2016-11-13-at-11.16.26.png
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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)
 
   ' PARAMETERS:
   ' 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
 
Proc_Exit:
   On Error Resume Next
   Set rpt = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SetReportRecordSource "

   Resume Proc_Exit
   Resume
End Sub

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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)
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "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.
0
 
João serras-pereiraAuthor Commented:
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?


ss1

Screen-Shot-2016-11-13-at-17.23.27.png
1
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
acViewHidden is not something Access recognizes.
https://msdn.microsoft.com/en-us/library/office/ff195735.aspx

acHidden can be specified as a window mode:
https://msdn.microsoft.com/en-us/library/office/ff195725.aspx

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.
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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

0
 
João serras-pereiraAuthor Commented:
Thanks for the help!!!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now