Testing a MS Access subreport for records

I haven’t done one of these in a long, long time, but I happen to be doing a bit of coding for a friend, and wanted to make a subreport control on an Access report invisible should it have no data. Of course, since I’ve been mostly out of the Access game for a while now, I was racking my brain for a bit, trying to figure it out and stumbling through a whole bunch of non-working solutions on the net.

But I eventually figured it out….

Assuming you have a subreport control on a parent report called MySubReport and you want the subreport control to be not visible should it have no records, this is one option you can use.

Private Sub Report_Activate()
If Me.MySubReport.Report.HasData = False Then
Me.MySubReport.Visible = False
Me.MySubReport.Visible = True
End If
End Sub

Report.hasData is the key!

Many solutions on the Internet talk about using code like this in the IF..THEN expression:

Me.SubReport.Form.RecordSet.RecordCount = 0

Of course, that won’t work (we’re working with reports not forms and Microsoft inform us that the recordset object isn’t available in reports. If you try use the above, Access will keep throwing you errors like:

This feature is not available in an MDB.

I’ve tested this in Access 2007 and it seems to work fine.

Access Report Design – Cluttered Controls – Can Grow & Can Shrink

Here’s a quick tip with regards to report, and to a lesser extent, form design. Many times when you’re design Ms Access reports, often you will come across spatial problems, where you simply trying to fit too many controls (usually textboxes) on a report – even when you’re designing in landscape mode.

Two extremely handy TEXT-BOX properties are the CAN GROW and CAN SHRINK property. By setting these properties to YES on a text-box, these will tell a text book to grow and shrink vertically with regards to the the size of the data that they are displaying. This means that the report will add extra vertical space for the control on the detail section of report, without upsetting any other controls that may sit adjacent to the particular control.

In my experience, these two properties can save the developer a lot of time and grief when trying to set out a report in a neat and readable fashion.