CrossTab Query

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

Is there a way to set through a form the columns to use in a crosstab query?

I am trying to come up with a form to do anaylisis against different
vehicles usages.
 
I have had to do this before. Basically what I did was to add a bunch of
text boxes to a form. I'd name the text boxes like txt1, txt2, txt3 etc. I
also named the attached labels lbl1, lbl2, lbl3, well you get the idea. Add
as many as you think you will ever need.

In the form load event I'd hide all of the fields with some code like

For i = 1 To intMaxFields
Me("txt" & i).ColumnHidden = True
Next

Every time the data source changed I call a routine that would Bind the
textboxes to the fields and change the label captions. It looks like this

Dim rst As DAO.Recordset
Dim i As Integer, j As Integer
Set rst = Me.Form.RecordsetClone
j = 0
For i = 0 To rst.Fields.Count - 1
If j < intMaxFields Then
Me("txt" & j + 1).ControlSource = rst.Fields(i).Name
Me("Lbl" & j + 1).Caption = rst.Fields(i).Name
Me("txt" & j + 1).ColumnHidden = False
Me("txt" & j + 1).ColumnWidth = 1500
j = j + 1
Else
pblnFieldOverflow = True ' Warn user some fields are not
displayed
End If
Next
For i = j + 1 To intMaxFields
Me("txt" & i).ColumnHidden = True
Next
Set rst = Nothing

That ought to pretty much do it for you.

Ron W
 
Ron,
I think that, not for sure, but I believe this would be part 2 that I didnt
post yet, but thanks for answering my thought in advance. What I meant by
this was, Is there a way to specify what 2 vehicles to use for the columns
in a cross tab query, so that they can be compared against each other.
Thanks
David
 
Back
Top