Report from Crosstab Query

[Ima Lostsoul]

Is there a way to mak ea dynamic report from a cross tab query?
Basically the query may generate a different number of columns of data
depending on the conditions that are set at run time. For example....
Assume:
1) The rows lists managers.
2) The columns list employees.
3) The 'grid' lists a count of tasks.

Sitaution:
1) The query and report will be generated for only a single manager.
2) Employees with no tasks should not show up in the report.
3) The number and combination of employees who have tasks will change.

Desired Result:
A query driven report that can list in crosstab format a listing of
employees and there task count by manager.
All I can see in building a report from crosstab is where you have to
manually build it each time you 'columns' change.

[Rich P]

Add a bunch of textboxes to your report detail section. Add more
textboxes than what you think you would need. Name them like txt0,
txt1, txt2, txt3, txt4...txt50. Set the visible property of each
textbox to false. Then in VBA code you set the recordsource property of
the report to your crosstab query. You count how many fields are in the
resulting query. Say the query returns 20 columns, you loop through
your report controls collection and set the visible property to true for
20 of your textboxes and simultaneously set the control source for each
textbox.

Private Sub Report_Open(...)
Dim i As Integer, RS As Recordset
Me.Recordsource = "Select * From qry1"
Set RS = CurrentDB.Openrecordset("qry1")
For i = 0 to RS.Fields.Count - 1
Me.Controls("txt" & i).Vislble = True
Me.Controls("txt" & i).ControlSource = RS(i).Name
Next
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!