Solved

access sub-report conditionally hiding

Posted on 2014-02-13
7
977 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
[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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
print previewing the record showing on the form 4 50
Delete table fields 3 47
Microsoft Access VBA - allocate a colour 3 72
MS Access Duplicate Data Assistance 9 21
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

734 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