Solved

access sub-report conditionally hiding

Posted on 2014-02-13
7
909 Views
Last Modified: 2014-02-14
Hi All,
I have a (2010) report with multiple sub-reports, all bound to queries, and wish to hide them (and set their height to 1) if the particular report has no data.

I was considering using a recordcount property on the 'open' (sub-report) event to do this but apparently this property is accessible in adp.

What properties, on what event, would I use to ascertain recordcount and set properties?

Please provide a code snippet e.g.

Private Sub Report_Open(Cancel As Integer)
  If Me.Recordset.RecordCount = 0 Then
    Me.Height = 1
    Me.Visible = False
  End If
End Sub

thank you.
0
Comment
Question by:COACHMAN99
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 39858355
Use the main report onload event and loop through all the subreports. Something like

Private Sub Report_Load()
	Dim ctl as control 

	For Each ctl In Me.Controls

		If ctl.ControlType=acSubform then
		
			If ctl.Form.Recordset.RecordCount = 0 Then
			
				ctl.Height = 1
				ctl.Visible = False
				
			End If
			
		End If
	Next
End Sub

Open in new window

0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 39859441
Thanks for the input.
Unfortunately I get errors:

1. when code used in Load:
2467, The expression you entered refers to an object that is closed or doesn't exist.

2. in Open
2455, You entered an expression that has an invalid reference to the property Form/Report.

How did you get line#s in your code?

thanks
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 39860016
MOdify to the following and make sure that you select the DAO library reference (Tool>References>Microsoft DAO 3.6 Object Library).

Private Sub Report_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset
    
Set db = CurrentDb

Dim ctl As Control

    For Each ctl In Me.Controls

        If ctl.ControlType = acSubform Then
        
        Set rs = db.OpenRecordset(ctl.Report.RecordSource, dbOpenDynaset)
              
            If rs.RecordCount = 0 Then
            
                ctl.Height = 1
                ctl.Visible = False
                
            End If
            
        End If
    Next
    
End Sub

Open in new window

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 7

Author Comment

by:COACHMAN99
ID: 39860032
Excellent! thanks
P.S. How do you get line#s in your code?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 39860107
Click code on the post toolbar and enter your code between "
 and 

Open in new window

"
0
 
LVL 16

Expert Comment

by:Sheils
ID: 39860114
between "
" and "

Open in new window

"
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 39860155
Thanks,
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

930 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

12 Experts available now in Live!

Get 1:1 Help Now