Set ColumnHeadings Property of Crosstab Query for Subreport with VBA? 1

[Carma]

Does anyone know how to define the Column Headings property in a crosstab query with VBA in Access 2000? The code would need to change the column headings everytime a report is run so the included headings are only those that have data, according to the criteria that is set at the time.

I have a report set up which includes two sub-reports, one showing contract budgets for different cities, and one showing contract expenses for each city. The main report then gives a balance remaining for each city and in total.

The sub-reports show each city as a column heading. The cities on each contract vary, and there are possibly 20 or more to select from. I have been trying to change it to show the budget and expenses in crosstab format, with dynamic headings, like the following example:

Contract Name
Contract Number
--------------
Subreport 1
Budgets:
City 1 City 2 City 3 Total
Date 1 Description $$$$$ $$$$$ $$$$$ $$$$$
Date 2 Description $$$$$ $$$$$ $$$$$ $$$$$
----- ----- ----- -----
Totals $$$$$ $$$$$ $$$$$ $$$$$

---------------
Subreport 2
Expenses:
City 1 City 2 City 3 Total
Date 1 Description $$$$$ $$$$$ $$$$$ $$$$$
Date 2 Description $$$$$ $$$$$ $$$$$ $$$$$
----- ----- ----- -----
Totals $$$$$ $$$$$ $$$$$ $$$$$
----------------
City 1 City 2 City 3 Total
Balance $$$$$ $$$$$ $$$$$ $$$$$

When I run the report, it says "You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

I am wondering if there is anyway to feed column headings to the crosstab query through code, and if this might solve the problem. I have too many cities to set the ColumnHeadings property manually. The headings need to be dynamic.

I have downloaded the Solutions database for Access 2000 from the Microsoft website, and have used it to set up this report. However, it doesn't seem to address this issue.

Any help is much appreciated.

Thanks!

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmm,

Now that I know a LOT more than really wanted to ...

A different -but more useful approach- is to set up your process to extract the column heading names which you will want/need/use from the data. Just do a select unique query on the data to get this. Use this query as the "Lefty" in a left outterr join back to the original query/recordset and use the 'new' column ass the column headings field.

Now, YOU can know a little bit more than you reall wanted to.




MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Carma]

I went ahead and modified the query. I set the PIVOT clause of the query in SQL view to the new column (PIVOT tablename.[fieldname]). However, I'm still getting the same error.

Is there somewhere I need to use this new column as the column heading field that I haven't tried?

Thanks for your help!

Carma

 

[MichaelRed]

Hmmmmmmmmmmmmmmm (more? again?)

"You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport.

Haven't seen this. Didn't notice it originally (? me bad?) ...

Alternate approach not useful unless you want to ALWAYS have ALL loccales (cities?) in the headings. CrossTab wuery properties, ColumnHeadings, set to fixed list.

Next alternate, go back to the "fixed crosstab" queries. DO a MakeTable query for the subreport data and bas the subreports on the MakeTable results. In this vein, inject some peace / quietitude with set warnings off -then back on IMMEDIATLY after the make tables, otherwise lowly users ge confused/upset/miffed with messages.

I would certainly go for the second (e.g. "Next alternate&quot - not sure of your level of comfort w/ some of this. You advertise as "Tech User", this might be near end of range for that category. Reachable by most, but nerve wracking for many,

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Carma]

I got it to work! The only problem is I had to set up 7 queries, 5 of which create tables, to get it to do what I wanted.

The unique query did help to get my Columns on both of the subreports to stay the same, and I set up a macro to first delete the existing temporary tables and then run the queries, to avoid all the warnings.

I don't know if there is a simpler process or not, but it works great for now! Thanks!