• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

Access 2007 Master-Detail Report Question

I am trying to better understand how to get a detail subreport to display a message when there are no detail records.  For example, I have a master record for an customer, but the customer has no orders (detail records). I would like a message to appear that says, "no orders", rather that just blank space.   I played around with a few tables, but when there are no detail records, the detail subreport is omitted from the report.  This includes any labels in the detail report, such as in the page and header sections.  

  • 2
2 Solutions
I tend to do this as a main report rather than a master and sub report so the main report source lists all customers and the details and I use the Customer Group Header and Footer to display the customer information and the detail section to display the order lines.

Cheers, Andrew
Helen FeddemaCommented:
You could do a count of the number of items in the "many" recordset, and if it is zero, make a label with the caption "No orders" visible (run it from the Detail_Format section of the report).  To give more exact code, I would need the database.
johnfaigAuthor Commented:
Helen Feddema,

It seems like Access 2007 has made life much easier for me than Access 2002 (which I was using).  In the old days, detail records had to be done as subforms. In Access 2007, you just define a relationship and I don't even have to create a query to join the master and detail tables.  WOW.

For my testing, the tables are simple.  The master table (Test-Master) has one numeric field called "Key".  The detail table (Test-Detail) has two numeric fields ("Key" and "Data").

I successfully counted the detail records by a calculated field  "Count(*)"  in the "Key Header" section

I would like to add a text field that gets assigned a value "No records" if there are zero detail records and blank if there are records.  I experimented with an IIF in the ControlSource of the textbox, but I get an error.

Any idea how I can accomplish this?

Helen FeddemaCommented:
Here is some code that you could adapt for this purpose -- substitute the appropriate field (just don't call it Count, as that is a reserved word).
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
   If Me![RecordCount] = 0 Then 
      Me![txtMessage].Visible = True
      Me![txtMessage].Visible = False 
   End If 
End Sub

Open in new window


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now