Stop printing if BOTH report & its subreport are empty

[Uvuriel03]

Alright, so most of it is pretty self explanatory. I have a report with a nested sub-report (couldn't figure out any other way to get both on the same page). They are both based on parameter queries, and the input is drawn from a form. The user types in two dates, a start date and an end date, in the form and checks off which reports he would like to be printed for those dates. Now I would like to figure out how to keep the report from printing if both the report AND the sub-report have no records. If one or the other has data, that's fine, it should still print.

Thankee muchlyful!

 

[gemma-the-husky]

if a report has no data, then a subreport wont either will it? (or it shouldnt anyway)

in any event, in the main form, just use the on no data event, and cancel the form opening

 

[Uvuriel03]

Well, actually, maybe this will span two problems of mine.

The subreport is NOT based on the main report. It's an entirely different report dropped into the Report Footer of the main report. The main report is for the trucks in between the two given dates, the 'subreport' is the report for the trucks out. I couldn't figure out any other way to make them show up on the same report.

Sometimes there are incoming trucks but no outgoing, and vice versa. I don't want to cancel the printing if there were no incoming trucks, but there WERE outgoing trucks, see?

 

[Fifty2One]

Alright, so most of it is pretty self explanatory. I have a report with a nested sub-report (couldn't figure out any other way to get both on the same page). They are both based on parameter queries, and the input is drawn from a form. The user types in two dates, a start date and an end date, in the form and checks off which reports he would like to be printed for those dates. Now I would like to figure out how to keep the report from printing if both the report AND the sub-report have no records. If one or the other has data, that's fine, it should still print.

Thankee muchlyful!

Before you call up the report test one of the data elements on the report with a dcount statement then test the subreport the same way. if the dcount does not find at least one record then maybe pop up a message that there is nothing to report when the user tries to select the reports.

 

[Uvuriel03]

I'm having issues getting the coding and/or macro right. I've tried both, but I don't really know coding, and I can't seem to get the details right for the condition in a macro...

It'd be great if I could get a little help. ^_^ Thanks!

 

[Mike375]

Here is a step by step way...like a manual system that is automated and should be easy for you to make.

Make a query that is based on the query that supports your subform. In this query click the big E at the top of the toolbar in Query design view and GroupBy will appear in the query grid. This is a drop down list...select Count. Pick field such as you have for Lastname or th ID field etc.

When you open this query it will have a field called CountOfLastName or whichever field name you applied the count to. The result will be either 0 or >0.

Now make a simple form based on that query.

Let's call the form Tester and assume the field is CountOf lastName.

Now to your macro. On the first action line select OpenForm action and for the form Tester. Also add MoveSize action and set each dimension at 0.

For the next action line do OpenReport. For the condition line put [Forms]![Tester]![CountOfLastName]>0

For the next action line do PrintOut. For the condition line put [Forms]![Texter]![CountOfLastName]>0

Then have two more action lines for Close. One being for the report and the other for the form. Macro action for close normally works even if the Report is not open. If you have trouble then remove the condiotion for OpenReport.

To add in the subreport as a condition then you have a Count query for that on the same basis. You can make a query that will join the two so you would have two field such as CountOfLastName and CountOfWhateverField. You can add a calculated field that adds the two values. You could have that field in your form and again the condition would be >0. That way if you have least one record in either the Report or Supreport the print out will occur

In your condition line you can have Or/And such as [Forms]![Texter]![CountOfLastName]>0 Or [Forms]![Texter]![CountOfFiledname]>0 and so on.

After you make the macro you can then save it as a module. Just right click on the macroname in the data base window and select Save As and pick module. That will then let you see what the code version of the macro looks like.

You could also include a MsgBox or a form as a message box and that is also subject to the condition. If [Forms]![Texter]![CountOfLastName]>0 then the form or msgbox will not open.

 

[gemma-the-husky]

rather than do it with a report and supreport, have one query with trucks out, and another with trucks in and union them. Add an extra column to each query called Direction (say), and set it to "Inwards" and "Outwards" to distinguish them.

Or even have a routetype lookup IN your route table, so you can set the route directly, as RunOut, RunBack, Backload, or any other sort of routetype you have. At the moment, you possibly have two tables for routes, one for routes out, and one for routes in, so this might be awkward. If so, your data is probably not properly normalised, so anything drawing different routetypes together will be more awkward


Union queries have to be done in SQL, but simply stated

in sql

select * from trucksoutquery

union select * from trucksinquery

the columns have to match exactly in type, although not necessarily in column headers.

now you just have one query to use in your report,

------------
I've done stacks of logistics stuff, so I may be able to help, if you need it

 

[Uvuriel03]

I'm thinking some help might be a good thing. Most of what I know of access is self-taught, so I'm not familiar with all the terminology and processes.

At the moment, you possibly have two tables for routes, one for routes out, and one for routes in, so this might be awkward. If so, your data is probably not properly normalised, so anything drawing different routetypes together will be more awkward

True. Actually, our inventory is in an excel database that is linked into access. One tab for incoming trucks, one for outgoing. Everything is linked through unique pallet IDs.

And I've heard of this 'normalized' concept, but I don't really understand it.

Also, what is the process for creating a join query?

Add an extra column to each query called Direction (say) {is this 'say' as in, 'for example'? Or part of what it's supposed to be called?}, and set it {set what, exactly?} to "Inwards" and "Outwards" to distinguish them.