Dynamically create rpt controls from xtab query's outcome?

[rpochoda]

I need to create a rpt based on a cross-tab query. Since I have criteria on the field used for the column headings, I can't know or fix the column headings in the query design. (There are way too many possibilities to just list them all.) So, I can't know the rpt's field list in advance. Is there any way in a rpt's OnOpen (or any other event) to dynamically get the resulting column headings into the report design as bound controls? Any other ideas for handling this situaltion?

Simply printing the query as a datasheet almost works, but I don't have enough control over the header and footer.

BTW, this is not a sub-form design question. The XTab is not being used to show detail.

Thanks.

 

[SuryaF]

I guess you have a maximum possible number of columns?
If so, you can create the report with that many columns.
At Open you hide the controls for the unused columns.
You also scan the recordset and set the report's controls ControlSource property to the name of the field.
You can also change the header of the columns accordingly.

If you don't have a maximum number of columns then you'll have to create and set the controls at run time, it's not easy but not very difficult either. To do that, as far as I can remember, you'd have to:
1. open from code the report in design mode
2. delete all controls
3. recreate controls with the proper info
4. close the report with saving
5. reopen the report in preview.
Now, some of the controls you might not want to delete, so put something in the Tag property like "Keep" or whatever and when you delete the controls you just skip the ones with "Keep" in the Tag.

For Each c in rpt.Controls
    If c.Tag <> "Keep" Then
        DeleteReportControl RepName, c.name
    Else
Next c

Have fun!

 

[Remou]

A few extra notes:

Private Sub Report_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
'Starting at first data column
For i = 2 To rs.Fields.Count - 3
'Controls are labelled A1 to An and B1 to Bn
    Me("A" & i - 1).Caption = rs(i).Name
    Me("B" & i - 1).ControlSource = rs(i).Name
Next
End Sub

 

[Remou]

And
Loop though reports and insert a new object to it
thread703-1231555