Solved

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

Posted on 2014-01-23
7
421 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
[X]
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
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 36

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 36

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

735 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