CrossTab query as RecordSource

[DZ]

I successfully used a CrossTab query as recordsource for a form.

The Crosstab query groups by month. There are multiple years and as data is
added the number of fields representing Year/Months grows.

The form had filter options for Year and Name. When the user selects a name
and year and clicks the Filter button cmdFilter, my code dynamically set the
control sources of the month text boxes that display data for every month of
the selected year.

Now i need to create a report with the same layout and recordsource, so I
grouped by year and then ported the code to the report format event and
changed the comboYear reference to me.Year.

I am getting an error message that ControlSource can't be set in
PrintPreview etc.

Does that means that its impossible to set the ControlSource of the month
text boxes in a report or is there another way of setting the controlSource
with code that I a,m not aware of.

or

Do i need to take a different approach entirely and dynamically create a
query somehow.

I'm trying to come up with an approach to this.

Any ideas welcome

DZ

 

[Allen Browne]

It should work as a report.

Year() is a VBA function, and I have seen Access confused about this.
Similarly, forms and reports have a Name property, and Access can get
confused and display the name of the report instead of the contents of the
Name field. Try renaming the fields.

 

[DZ]

Thanks for responding. I went into the CrossTab Query and renamed the fields
to Yr and Nm with Yr: Year and Nm: Name. Then I changed the references in the
Code to Yr and Nm.

I am still getting error message 2191

"You can't set the ControlSource property in print preview or after printing
has started."

Any more feedback is welcome.

 

[DZ]

I experimented a little and tried setting the ControlSource for the Jan
textBox with a simple statement in the Detail Format... I also tried it in
the Detail Print, YrHeader Print and Format

Me.Jan.ControlSource = "05, 2005"

and i still get the error message 2191

"You can't set the ControlSource property in Print Preview or after printing
has started."

It seems as though Access won't allow setting the controlSource in the Print
or Format events of the Detail or Group Headers.

If that is the case, i will have to come up with a completely different way
of displaying the data in the report from how I did it in the Form.

I WAS successful in setting the ControlSource property in the Report Open
event but that won't help me because i need the set the ControlSource
property based on the year with the following type of statemernt

Jan.ControlSource = "01, " & Me.Yr


To help clarify, here is a little more background information about the
Crosstab query.

'The field names generated by the crossTab query, are named as follows with
month represented by the 01, etc.
"01, 2005"
"02, 2005"
etc to "09, 2008"

 

[Allen Browne]

Detail_Format is too late to set the ControlSource.

Use Report_Open.

 

[DZ]

Yes

That seems to be the case. Set the CS on Report open

Thanks

Angelo