Subreports

[jj22171]

My access main report looks like the following, where revenue and expenses portions are subreports with page break by Province: (all reports are using the same dataset/pass-thru query)

Province: BC
Rooms Revenue xxxx
F&B revenue xxxx
Others xxxx
Total revenue xxxx
Rooms expenses xxxx
F&B expenses xxxx
Others expenses xxxx
Total revenue xxxx

For the two subreports, I used GroupFooters to sum revenues (and expenses) by type (Rooms, etc) and by province (BC, AB, SK). When opened, the report doesn't work. The best result I got was a page by province but with the same set of figures which are the sum for all provinces. Thanks for whatever help/tips you can share with me.

 

[dhookom]

Subreports have no page breaks. Have you tried using the Link Master/Child properties with the Province field?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jj22171]

Yes, Link Master/Child properties been set but I'm not sure if done correctly. Link Child Field=txtMainRptProv; Link Master Field= ProvID, but it's giving out error 2191, "You can't set the ControlSource property in print preview or after printing has started". Maybe if I report one province at time (ie., taking away pagebreaks), it will work.

Thanks for telling me "Subreports have no page breaks". It looks like I've spent almost two days trying to do something's not possible on the first place.

appreciate your help.

 

[dhookom]

I'm not sure what txtMainRptProv is. This link master/child is much like the link master/child of forms and subforms. I would expect your main report has one record per province. The subreport should be in the detail section and ProvID should be in both recordsources and be used for linking.


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jj22171]

The txtMainRptProv is a textbox in the maint report with controlsource = ProvID. Since both main & subreports use the same datasource, there are multiple records per province. In main report, there's grouping by ProvID. The group header has the province ID & full name while the group footer has the subreports.

The subreports has groupings by ProvID and AcctType. The ProvID footer is used for the subtotal (revenue or expenses by Prov) and the AcctType footer to sum by AcctType (rooms revenue or expenses; f&b revenue or expenses, etc).

I'll try creating a separate datasource for ProvID with one record per province and see if it works. Or, maybe you have a better suggestion.
Thanks again.

 

[dhookom]

You should be able to keep the subreport in the ProvID group footer. I would just set the links to the field name.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jj22171]

Even with a separate datasource for the main report with a single record per province, it didn't work. The error message is "You can't set the ControlSource property in print preview or after printing has started". It looks like Access won't allow changes in subreports data.

Maybe it's Access limitation or I'm just missing something.

 

[dhookom]

Are you actually attempting to set these properties with code? What are the exact values you are using in the Link Master/Child properties?

Your issue could be caused if your properties use
=ProvID
rather than
ProvID

Duane MS Access MVP
Now help me support United Cerebral Palsy