Solved

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

Posted on 2014-01-23
7
414 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 35

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

809 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