Thanks in advance for any help!


I have an Access 2007 report that creates a dues invoice that we print and mail to members.

We have six types of members, and each type needs to get different info on their invoice. This info includes text and a table that needs to be formatted in a certain way.

In the past, we had six different reports to generate, but it is much easier to have one report that varies according to member type.


So if I create six different subreports, I'll be in business - but I don't know how to get Access to select the right subreport for each invoice.

My workaround right now is a new table I've created. It has a column for each member type, linked to the main table. The second field is an OLE jpg with the necessary text & table. I've put that field in my report. It's not elegant, but it works.


How to I get my report to insert a specific subreport instead?


Thanks!
Liz

This post has been edited by coreperformance: Apr 27 2012, 04:26 PM

Liz

It sounds like you've selected "subreports" as a solution to your situation. I don't know enough about your situation to tell if that's the most appropriate approach.

Are you saying that you want a different set of data displayed in the report, depending on the [MemberType]? Are you saying that the data displayed are the same for everyone in the same [MemberGroup] (e.g., everyone in MemberGroupType = 5 gets "abcdefg")? Or are you saying that members of type = 5 need to see their names/addresses, but members of type = 7 need to see their dates of birth and their marital status?

More info, please...

The information for different members isn't from the database at all.

"Retailers" need a paragraph that goes over their benefits, and a chart for dues levels.

"Manufacturers" will have a different paragraph, and a different chart.

Etc.

The information does not come from fields in the database, and I need to have control over how it is formatted and laid out on the page.

Just to clarify, these subreports I am hoping to include are just a part of the report. There are other fields and subreports (that are the same for everyone) that are also in the report.

So here's another thought about how you could get different material to show for different types of members ...

If you build one main report that had all the common information, then built each/all of the different member-type (sub-)reports, then added ALL of them to the main report, you'd only have to have the one member-type subreport control's .Visible property set to Yes. All the others would be .Visible = No.

Good luck!

Hi Jeff,

I'm a novice at VB, and this topic is applicable to what I wanted to do. That is, main page for a report and use the Visible property to print subreports based on checkboxes. A "check" would make the report visible for a print command.

Could you, or someone, give me an example of what the code would look like to change the visibility of subreports (located on a main report) based upon checkboxes on a main form?

Thank you in advance!

Conceptually, subreports 'reside' inside a container (control) on the main report (or main form). To make the subreport invisible, you'd make the control (on the main form) invisible.

When the report opens (i.e., OnOpen event) ... {or maybe, OnFormat event}, you 'inspect' the checkbox on the form, something like:

Forms!YourForm!YourCheckbox

Use that value to set the subreportcontrol.visible property.

(JOPO-just one person's opinion)

Hi Jeff,

Thank you for your reply and your explanation. You've described what I'd like to happen. Would you be willing to give me an example of what the code looks like for this?

I've named the main report "rptCoverPage" and I have data on that page that will always be printed. I am using page breaks to separate that data and each subsequent subreport. I have made each subreport invisible.

From here, I am not sure how to write the event in VB.

Here's one of the chkBoxes I am referencing: =[Forms]![frmMain]![frmPrint].[Form]![Check474]

and a subreports name is: "rptCommoCard" which is located on the main report: "rptCoverPage"

Could you give me an example the VB code to perform that inspection?

You mention a checkbox you want to reference ... and the expression you used suggests that the checkbox is on a subform. Is that the case? Is it necessary that the checkbox stay on the subform?

I wasn't suggesting that you make all the subreport controls invisible to start with... I was suggesting that you use, say, the OnFormat event of the report (open the report in design view, select the OnFormat event, build the procedure/code there) to determine which subreports to make invisible.

The code might look something like (untested, your mileage may vary):

Me!TheFirstSubReportControl.Visible = Not Forms!YourForm!YourCheckbox

(note that this would need to be modified for your objects' names, and, if the form is not bound, you'd need to use the .Value property)

(IMG:style_emoticons/default/thanks.gif) Worked the first time. I wish I could say that about the rest of the project! (IMG:style_emoticons/default/laugh.gif)

I appreciate your help.