Solved

access sub-report conditionally hiding

Posted on 2014-02-13
7
896 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

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.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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