Multiple forms into one Report

[Arob]

I have several data input forms which I would like to place into one report. This part is easy, however the user may not have to fill out all possible forms, therefore the report would have a blank section with no data if all the forms are not filled out. I was just wandering if anyone knew if it was possible to design the report that it would omit one of the forms sections if the form was not filled out. I would appreciate any advice.

 

[RickSpr]

You can't put forms onto reports, so I guess what you're talking about is that you have several forms which you use to enter related records into several tables, and your reports prints data from all those tables. You want to omit the parts of the report for tables that have no related data.

Does your report's Record Source specify a query that joins these tables together, or are you using subreports for some of the tables?

If you're using a query with inner joins to the optional tables, you can omit a section by letting it shrink to nothing when there is no data. All of the text boxes bound to that table's fields should have their Can Shrink properties set to Yes. The trick is to make the labels on the report disappear, too, so that the entire section can shrink. One way I've done that is to convert the labels to text boxes, and set their Control Source properties to an IIf() function that generates the label caption, if the corresponding field contains data, or Null if the field is null. These text box "labels" need Can Shrink too, of course.

If you're using subreports, you probably need VBA code in the Format event for the Detail section of the report. (I haven't ever done this, so I'm not sure this will work, but I think the general idea is correct.) If the data is null, the code sets the subreport's Visible property to False, otherwise set it to True. The subreport control's Can Shrink property must be set to Yes. Rick Sprague

 

[Arob]

Do you have an example of setting the text boxes control source properties to an IIf() function that generates the label function. I understand all but this section. Thanks Aaron

 

[RickSpr]

Ok, let's say one of your "sections" is a text box named "Descr", with a label having the caption "Description". Change the label to a text box (the right-click menu has a Change To... item you can use). Then set the Control Source property for this new text box to:

    =IIf(IsNull([Descr]), Null, "Description")

The first name (Descr) is the name of the text box control whose label this was, and the second name (Description) is the caption.

The formula generates Null for the pseudo-label text box if the data is Null. Since both text boxes are Null and have the Can Shrink property set to Yes, they shrink to 0 height. Rick Sprague