Dynamically creating crosstab queries and displaying the results

[Mark]

I have about 100 queries that slice and dice one large
table, and I would like to create a form that would allow
me to pick and choose fields to display in a crosstab
query and set criteria for from Pull Down lists.

Most of this, I think I can handle doing, I do have some
questions before I begin. I've tried creating a datasheet
form using the wizard from a Xtab before, and received an
error message, so I tried creating with the record source,
but had troubles with that too.

I know I can create a query def, and open it using
docmd.openquery, but how can I programatically delete or
close that window?

Thanks in advance.

Mark

 

[Ron Weiner]

Mark

You do not report wat the errors were when you attempted to use a form in
Datasheet view with a crosstab as it's record source so I can not give any
direct advice on how to fix the problem. I can assure you this is possible,
but takes a little planning and some code on the Form OnLoad event to make
it all happen.

It sounds as though you should be able to live with a read only form in
datasheet view, which makes it MUCH easier to implement. Below is a quick
outline of what you need to do:

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

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

In the OnLoad 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