Display subform when no data

[chancer1967]

I have a database for credit control and within this there are tables for invoices, credits and payments.

As a reconciliation tool I have created a form that gives activity totals for a given month.

The top of the form has two text boxes for year and month (they will become combos at some point), and beneath that are 3 subforms, each of which is linked to a query which gives the total invoices (or credits or payments) for the month and year specified.

I then have an update button that the user presses to refresh the 3 subforms when the date is changed.

It all works very well, but there is one small annoyance:

In some months there are no credits. In these months the subform for credits vanishes. I would rather it stay there and show a zero.

Is there a parameter in the subform that I need to change for this to happen?


Thanks

 

[adam_fleck]

quick fix?

Try checking this in your queries:

I'm guessing your subform queries are "Group By" queries. Open them in design View and have a look at the date fields that set the date criteria.

Suppose you have a date_Fld_1 as one of these date fields. If underneath this, there's a Group By and then underneath that a tick, then that's your problem (probably). Remove the tick, and change "Group By" to "Where".

A query that "Groups By" a field that is included in its selection criteria will not return even a single blank row if there is no data matching the criteria.

A query that doesn't Group By any of it's selection criteria, but "Where's" them instead will return a single blank row if there's no matching data.

With not even a blank row, your subforms won't show at all when there's no data (as you describe).

With a single blank row, on the otherhand, your sub forms will show up, but will be empty of data (what you'd like to happen).

Hope this will work. There will be other ways of fixing this if it doesn't, though you might need to do some coding.

 

[chancer1967]

Perfect!


thanks

 

[livvie]

I have a similar problem with subreports except I don't have a group by. Any ideas ?

 

[adam_fleck]

This might work

Hi Livvie

If the report you’re working with isn’t too complex, this might not be too difficult to fix.

Suppose the table your main report is based on (table_1) on has primary key tbl_1_ID. Suppose also, the table your subReport is based on (table_2) has primary key tbl_2_ID, but also includes tbl_1_ID as what’s often called a foreign key.

What you could do is base your subReport on a query rather than on table_2. This query joins table_1 and table_2 on tbl_1_ID, but it’s a Left Join query (one way of setting this up is to double click the join line, and choose the option “Include ALL records from table_1 and only those records from table_2 …”).

If you make sure your query returns tbl_1_ID from table_1, and takes all the other fields it needs from table_2, then your subReport should always show up.

If your reports are based on queries that are already fairly complicated, it might be quite tricky to get the joins to line up right, but it should still be doable.