access sub-report conditionally hiding

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.
LVL 7
COACHMAN99Asked:
Who is Participating?
 
SheilsConnect With a Mentor Commented:
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
 
SheilsCommented:
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
 
COACHMAN99Author Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
COACHMAN99Author Commented:
Excellent! thanks
P.S. How do you get line#s in your code?
0
 
SheilsCommented:
Click code on the post toolbar and enter your code between "
 and 

Open in new window

"
0
 
SheilsCommented:
between "
" and "

Open in new window

"
0
 
COACHMAN99Author Commented:
Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.