Solved

access sub-report conditionally hiding

Posted on 2014-02-13
7
1,016 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 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