Display crosstab query in subform

[Jesper Fjølner]

I need to display the result of a crosstab query in a subform control.
I used a regular subform but since my query will have a variable number of
columns this doesn't work as the form that goes into the subform only has a
set number of fields.
What I need is something that looks like a datagrid but accept a variable
number of columns generated by a dynamic crosstab query (the number of
columns changes everytime the code runs).
I've tried creating a querydef and setting the sourceobject of the subform
to the querydef but that doesn't work. I think the sourceobject can only be
a form/report
Is it possible to get this gridlike behaviour with a variable number of
columns?
Thanks for any input.

 

[Paul Overway]

You'll need to fix the number of columns somehow, or find a grid control to
use.

 

[Jesper Fjølner]

Think I may have found something that works.
The first time it runs I create a crosstab query "qryTemp" and save it as an
object in the database.
When I run the code subsequently I modify that qryTemp with
Dim qry as querydef
set qdf = currentdb.querydefs("qryTemp")
qdf.SQL = MyDynamicCrosstabSQLstring
Me!subform.sourceobject = "Query.qryTemp"
It allows for alterations of the number of columns and looks like a grid.
Works fine.

 

[Dale Fye]

Jesper,

I did this for a project a while ago. It is cumbersome, but can be done.
Unfortunately, I did all the work in a classified environment, so am not
able to copy my code and redistribute without going through a bunch of
hassles. Hope this explaination will give you an idea where to start. It
actually ran relatively quickly.

1. Generate your query.

2. Open a recordset based on the query (SELECT * FROM qry_YourQueryName
WHERE False). By using this query, you will get the column headings, but
none of the associated data.

3. Create a new form (application.createform) and open it in design mode
(through code), set the mode to hidden. Set the RecordSource for the Form
to your query. Set the forms defaultView to datasheet.

4. With your recordset, loop through the fields of your recordset and add a
text box (Application.CreateControl) to this form for each field in the
recordset. Change the top property of each successive text box by some
appropriate value so you can look at the form in design view and make sense
of it. Make sure you set the control source of each successive text box.

5. Close the form, with acSaveYes

6. Use this new form as the source object of your subform.

Dale

 

[Paul Overway]

That doesn't appear to be documented anywhere. Works for Table.TableName
too. Cool trick....I'll tuck that one away.

 

[Ron Weiner]

Jesper

As long as you can live with a read only form you can do this by dynamically
Hiding/Unhiding textboxes on your form (as a Datasheet) based on the columns
returned by your query as you open/re-query the form.

Create a form with a bunch of text boxes named something clever like text1,
text2, text3,... The text boxes will need attached labels which could be
named label1, label2, label3,... Create as many text boxes and labels as
you think you will *EVER* need. Make the forms DefaultView a Datasheet.

When you need the form open it and set its Record Source to your canned
CrossTab query or a sql statement that you generate on the fly.

In the OnLoad or OnCurrent event of the form you will need to run some code
that looks at your forms' recordset and sets the all of the TextBoxes Source
and the associated Label Captions to the Field names of the reordset. It
will also have to hide any textboxes that are not used. The code might look
like:

Set rst = Me.Form.RecordsetClone
For i = 0 To rst.Fields.Count - 1
If i < 30 Then
Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
Me("Label" & i + 1).Caption = rst.Fields(i).Name
Me("Text" & i + 1).ColumnHidden = False
Me("Text" & i + 1).ColumnWidth = 1500
End If
Next
For i = i + 1 To 30
Me("Text" & i).ColumnHidden = True
Next
Set rst = Nothing

In this case I planed for a max of thirty fields. Done this way the data in
the form is Read Only. As long as you can live with this you are good to
go. If you want to allow the users to update the data then you will have to
do a LOT more work, using at least one temp table (I have typically used two
temp tables one for the data and the other for the meta data). You will be
completely responsible for writing the code that inserts/updates the data in
your normalized tables from any changed data in your form.

Ron W