Creating Report based upon multiple fields

[orbic1]

This is my problem, and I can't solve it!

My parent table/form is "Consultation". From this there are many subforms all linking to their respective tables all linked to "Consultation" via their ID (primary key). They are used for the different outcomes or requirements arising from the consultation and these all have this relationship set up. The consultation itself may have anywhere between 1 and 10 different outcomes.

A report needs to be generated to summarise the consultation in the guise of a letter. I can't figure out how to do this without all of the criteria being shown when I don't need it to be! For simplicity I've added in a check box on each subform called "Active", the idea being that a script could simply find both the ID and "Active" to form the query. The logic probably required is:

if table1!active = true then show all of table1's fields
elseif table2!active = true then show all of table 2's fields
etc etc

Really stuck with this one! I can get it to show all of the data, but can't filter it or generate "on the fly". Any ideas?!

Many thanks!

PS have also created a query for each individual subform's table.

 

[llkhoutx]

Set the report recordsource to to query.

On your form, dynamically build that query (already named) based on your criteria.

 

[orbic1]

Thanks! I've tried what you've suggested and got stuck again.
I've got my main report based upon the Consultation. Within this I have several subforms, each relating to each outcome. They only show when active=true by using the statement:

select table from table where [active]=true;

However, what I get is a report that shows giant gaps between each subreport. I've tried setting them to auto-resize, making them small and putting them next to each other, but due to the number of sub forms, I still get a huge gap. Do you have any ideas on a better / alternative way to display this data?

Thanks

 

[llkhoutx]

Be sure the borders of your subreports are not overlapping. Overlapping borders will cause gaps.

 

[orbic1]

So you reckon that I'm going to just have to put them one underneath the other with a minute gap between them?
I've tried this, but as I have 20 sub forms, if the last subform is active, and the others aren't, there's going to be a good inch's gap.

 

[llkhoutx]

I battled the gap program with A97 with a report that had almost 200 subreports; converted to A2k and the problem wasn't so bad, but finally ended up repositioning all the subreports merely using the align top icon; overlapping subreports became evident immediately because they were moved way out of position.

Also, be sure to set the ConGrow/CanShrink properties for each subreport (when in the report design veiw) to Yes.

 

[orbic1]

That may well be the ticket! Thanks! I'll try that asap!