Access 2007 Master-Detail Report Question

Posted on 2010-01-03
Last Modified: 2013-11-28
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.  

Question by:johnfaig
    LVL 28

    Assisted Solution

    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
    LVL 31

    Accepted Solution

    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.

    Author Comment

    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?

    LVL 31

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now