Crosstab subreports in main report problem

[Steve]

Ok, I've gone through google and I haven't found a solution to this, so I
hope you folks can help.

I'm trying to make a report that will be a price list for my companies
products. The products are complex, and are sold in a variety of
configurations. Because of this each product has two sections in the price
book, and each section is a crosstab of the underlying tables. My plan was
to make a main report for the whole book, and then for each product have two
subreports for the two sections needed.

So, pulling some code out of the Access 2000 Developer's Handbook I've got
both subreports working ok independently, but when I put them in the main
report I get an empty report after receiving two error boxes telling me that
pass-thru queries aren't allowed on subreports.

I figure I need to unbind the subreports, and make some code to feed them
properly, but I don't know how to do that. I have little VBA experience,
although I am learning rapidly, but time for this project is running out ;-)

Does anyone know how to do what I am describing? Is there a good reference
for this anywhere? I saw a reference to dynamic crosstabs being modified
from the Northwinds sample database, but neither the northwinds I have nor
the one I just got off of Microsoft's site have the reports mentioned in the
reference I found.

HELP!

Thanks!

Steve

 

[Duane Hookom]

There are some samples of crosstab and multi-value crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. Are you able to specify
the column headings property in your crosstab querys?

 

[Steve]

Thanks for the link - I'll take a look...

Each instance of each subreport may have different columns than another
instance. This may be impossible to do, but I haven't found anything
conclusive so I'm going to keep trying to figure it out.

An alternative might be to make a macro or code to query and run the
individual instances in an order set in a table, but the problem with that
is that the final reported result (i.e. the price book) needs to be
distributed (not just printed) and I don't know how to concatenate the
various reports. The other problem is that ultimately I will have 30 or more
pricebooks, so stitching them together by hand isn't very appetizing...

 

[Duane Hookom]

One of the crosstab demos has different columns that are aliased.

 

[Steve]

Ok, is that the one titled rptCrossTabWithMultiColumn? Looking at srptDates,
I see that ControlSource = 1. I don't understand this...

 

[Duane Hookom]

The report that aliases the columns so they remain consistant is in the
Crosstab.mdb. You can pre-define the number of columns and the columns are
aliased as A, B, C, ...