On MS Access applications, usually I use subreport objects with SQL queries of their own, linked to the main report which includes the subreport. Classic example of such master-detail reports is invoices:

+ master (main report) section includes invoice date, invoice number and details of the customer account such as name and address lines.
+ detail (subreport) section includes the products and services charged, product code, desription, quantity, price, and line total etc.

Another example could be statement of share transactions of an employee within a date interval.

+ employee record (name, surname, NI number etc) on main section
+ share transaction list in a subreport underneath the employee record

The problem: When the subreport has no data, MS Access does not show it at all. I may want to see the column headings, at least, or a message with it.

The Solution: We can modify the SQL query (record source) of the subreport and make sure that it has data every time it is run.

Let’s say the SQL query of the main report is something like this:

And the original SQL query for our subreport which brings the share transactions of employees is:

We can convert it into a UNION query which will always produce data:

This way, when there is at least one share transaction the subreport will be visible as usual, listing the transactions of the employee. If there are no transactions, the second part of the union query will have the information we want to show.

Note: In my SQL queries, I referred to my public functions placed inside a VBA module; GetCurrentEmployeeNumber(), GetStartDate(), GetEndDate(). That’s my way of collecting and holding the user inputs as criteria affecting my query results, using global variables; basically, setting them and getting them when required.