Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

How to set visible parameter of a control on a report in VBA

From a separate VB module, I open a Report in acPreview mode (or acViewDesign mode), and attempt to set the visibility of a control based on the value of another control in the same report per the code below.  

                DoCmd.OpenReport SummaryReportName, acPreview, , "HHID = " & ClientNowPrinting
                Debug.Print "This should print"
                Debug.Print "Text135 = " & [RpQtr - Performance131231Charts]!Text135
                Debug.Print "text199 visible = " & [RpQtr - Performance131231Charts]!Text199.Visible
                If [RpQtr - Performance131231Charts]!Text135 <> 0 Then
                    [RpQtr -Performance131231Charts]!Text199.Visible = False
                End If

The Report loads fine, and subsequently prints, but my attempt to set control visibility before printing is fruitless.  Any line of code having the name of the report just seems to be ignored, even the debug.print commands.  The "This should print" line does print in the Immediate window, but the lines following do not print.  The variable SummaryReportName = "RpQtr - Performance131231Charts"

Is it not possible to read the value in controls of a Report and change them from code in another module?  If it is possible, what am I doing wrong?
0
David_W_R
Asked:
David_W_R
6 Solutions
 
Dale FyeCommented:
where is this code?

Generally, it will have to be in the Report, not in some external, VBA code module.

Depending on where the controls are that you are trying to manipulate (report header, page header, group header, detail, group footer, page footerreport footer), you will have to play with the event (format or Print) within that section to see whether the code works or not.

It will also depend on whether you are using the ReportView or the PreviewView, as some events that fire in ReportView don't do so in the PreviewView.
0
 
Helen FeddemaCommented:
Use code on the Detail_Format event of the report, like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   If Me![Sales] >= 10000 Then
      Me![txtSales].FontBold = True
      Me![txtSales].FontName = "Arial Black"
      Me![txtSales].FontSize = 12
      Me![txtSales].ForeColor = vbRed
      Me![txtSales].BackColor = vbYellow
      Me![imgGoldStar].Visible = True
   Else
      Me![txtSales].FontBold = False
      Me![txtSales].FontName = "Arial"
      Me![txtSales].FontSize = 9
      Me![txtSales].ForeColor = vbBlack
      Me![txtSales].BackColor = vbWhite
      Me![imgGoldStar].Visible = False
   End If
      
End Sub

Open in new window

0
 
PatHartmanCommented:
Is it not possible to read the value in controls of a Report and change them from code in another module?
Not from outside the report.  How would your code know which instance of a control it was referencing.  Any reference you make (your reference is incorrect and that's why it is not working at all) would reference the first instance of a particular control.

A report is not like a spreadsheet where you have a row and a column to pinpoint an exact spot.  All the controls in the report repeat as many times as they have records/pages to print.

We'll need some more information regarding what your objective is in order to offer a solution.  As Helen mentioned, you will certainly need to put the code into a report event but unless we know what you are trying to do, we can't guide you to the correct event.

PS - When the wizard builds a report or you drag a control from the field list, Access assigns the name of the bound field as the Name property of the control.  When you add a control by using the ribbon, you get non-informative names.  Best practice is to fix all the names before you actually write any code.  Professionals go even further and add prefixes to the Name properties so that the control name can be distinguished from the field name.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
GozrehCommented:
change your code to this
   DoCmd.OpenReport SummaryReportName, acPreview, , "HHID = " & ClientNowPrinting 
   Dim rpt As Report
   Set rpt = Reports("[RpQtr - Performance131231Charts]")
   Debug.Print "This should print"
   Debug.Print "Text135 = " & rpt!Text135
   Debug.Print "text199 visible = " & rpt!Text199.Visible
   If rpt!Text135 <> 0 Then
       rpt!Text199.Visible = False
   End If

Open in new window

It will do the job
0
 
PatHartmanCommented:
rpt!Text135 refers to ONLY the first instance of that control.  You can only affect every instance from inside the report's class module.  The If statement would need to be in the Format event of the report section where it is defined.  So, if the control is in the Header, the code needs to be in the Format event of the header.  If the control is in the detail, the code needs to be in the Format event of the detail.
0
 
GozrehCommented:
PatHartman is right if its a continues report (meaning many pages), so you will need to repeat this on every page, then the only solution is to run the code from the report itself.

but if you want to use it on single page report, like one invoice at a time (it seems you filtered one ID "HHID = " & ClientNowPrinting) , so you can use the code above from anywhere.
0
 
David_W_RAuthor Commented:
Many good points here.  Quite a tutorial!  Many thanks to each contributor.   "Best" answer was the one that went furthest to solve the problem, but more nuggets in the others.  Great to have a roomful of experienced teachers!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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