Solved

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

Posted on 2014-01-23
7
411 Views
Last Modified: 2014-02-03
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
Comment
Question by:David_W_R
7 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39803378
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 150 total points
ID: 39803985
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 39804373
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 150 total points
ID: 39820948
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 39821001
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
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 150 total points
ID: 39821034
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
 

Author Closing Comment

by:David_W_R
ID: 39830108
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now