Getting Headings For a Crosstab Query

[whitsendstudios]

Hi all,

I have a crosstab query that I want to use as the source for filling
in data on a form to display to the user via code. I have had to fill
a form with control text boxes to allow me to display the data in the
required format, so I need a way to programmatically fill in the text
boxes.

I can get it to display the returned record information for the Column
headers that are static (which are the crosstab Row Headers), but the
column headers I can't for the dynamic headers (the crosstab Column
Headers).

I need to return the name of the 3rd column onwards in my query so I
can then retreive the values in that column. Below is the code I have
so far and the line starting >>>>>> is where my problem is.

Dim db As Database
Dim qry As QueryDef
Dim rstQuery As DAO.Recordset

Set db = CurrentDb()
'Query2 is my crosstab query
Set qry = db.QueryDefs("Query2")

'the following are the user definable parameters to filter the records
returned by the query
qry("jobno:") = Me.Combo0
qry("originator:") = Me.Combo6
qry("group:") = Me.Combo8

Set rstQuery = qry.OpenRecordset()

With rstQuery
.MoveFirst
'the offset is there as I need to create my own scrollbars to
allow the user to scroll through the results
If Me.txtOffsetY > 0 Then .Move 10 * Me.txtOffsetY
intDisplay = 1
Do
'the following creates the control names for the textboxes
with appropriate numbering
If intDisplay < 10 Then
strControl1 = "txtDrgNo0"
strControl2 = "txtTitle0"
strControl3 = "txtDate0"
Else
strControl1 = "txtDrgNo"
strControl2 = "txtTitle"
strControl3 = "txtDate"
End If

'fill in the drawing numbers
strControl1 = strControl1 & Str(intDisplay)
strControl1 = Replace(strControl1, " ", "")
Forms!frm_drawings_register.Controls(strControl1) = rstQuery!
number

'fill in the drawing names
strControl2 = strControl2 & Str(intDisplay)
strControl2 = Replace(strControl2, " ", "")
Forms!frm_drawings_register.Controls(strControl2) = rstQuery!
title

'fill in the date drawing received
strControl3 = strControl3 & Str(intDisplay)
strControl3 = Replace(strControl3, " ", "")
intDisplay = intDisplay + 1
.MoveNext
Loop Until rstQuery.EOF
.Close
End With

Set db = Nothing
Set qry = Nothing
Set rstQuery = Nothing

 

[Mr B]

I have a project that I am currently completing that has a simular situation.
I am returning a specific number of columns each time the crosstab query is
run.

I solved my problem by first creating a table where I can append the results
of the crosstab query This table has predefined row names for each of the
columns of info that will be returned. I then dynamically create the
crosstab query thru VBA code (my columns are dates for the past 7 days). I
then dynamically create the append query that appends the crosstab query to
my table by using variables for each of the dates that I know I will be
returning. The variables allow me mapped to each of the columns in the
crosstab query to the correct fields in the table in the append query. When
the append query is executed it writes the data to predefined fields. From
these fields in the temporary table I then present the data in my form.

This is the only way that I was able to come up with that would allow me to
use the results of my crosstab query as the source for my form. There is a
lot of code involved in my version of this but this is primarily due to the
fact that the form is a dynamic filtering form which allows the user to
specify the criteria for the crosstab query.

 

[Wolfgang Kais]

Hello whitsendstudios.

Hi all,

I have a crosstab query that I want to use as the source for
filling in data on a form to display to the user via code.
I have had to fill a form with control text boxes to allow me
to display the data in the required format, so I need a way to
programmatically fill in the text boxes.

I can get it to display the returned record information for the
Column headers that are static (which are the crosstab Row
Headers), but the column headers I can't for the dynamic headers
(the crosstab Column Headers).

I need to return the name of the 3rd column onwards in my query so
I can then retreive the values in that column. Below is the code I
have so far and the line starting >>>>>> is where my problem is.

Dim db As Database
Dim qry As QueryDef
Dim rstQuery As DAO.Recordset

Set db = CurrentDb()
'Query2 is my crosstab query
Set qry = db.QueryDefs("Query2")

'the following are the user definable parameters to filter the
'records returned by the query
qry("jobno:") = Me.Combo0
qry("originator:") = Me.Combo6
qry("group:") = Me.Combo8

Set rstQuery = qry.OpenRecordset()

With rstQuery
.MoveFirst
'the offset is there as I need to create my own scrollbars to
'allow the user to scroll through the results
If Me.txtOffsetY > 0 Then .Move 10 * Me.txtOffsetY
intDisplay = 1
Do
'the following creates the control names for the textboxes
'with appropriate numbering
If intDisplay < 10 Then
strControl1 = "txtDrgNo0"
strControl2 = "txtTitle0"
strControl3 = "txtDate0"
Else
strControl1 = "txtDrgNo"
strControl2 = "txtTitle"
strControl3 = "txtDate"
End If

'fill in the drawing numbers
strControl1 = strControl1 & Str(intDisplay)
strControl1 = Replace(strControl1, " ", "")
Forms!frm_drawings_register.Controls(strControl1) = _
rstQuery!number

'fill in the drawing names
strControl2 = strControl2 & Str(intDisplay)
strControl2 = Replace(strControl2, " ", "")
Forms!frm_drawings_register.Controls(strControl2) = _
rstQuery!title

'fill in the date drawing received
strControl3 = strControl3 & Str(intDisplay)
strControl3 = Replace(strControl3, " ", "")
rstQuery!Column(3)

intDisplay = intDisplay + 1
.MoveNext
Loop Until rstQuery.EOF
.Close
End With

Set db = Nothing
Set qry = Nothing
Set rstQuery = Nothing

So how about this (only your With block)::
With rstQuery
.MoveFirst
'the offset is there as I need to create my own scrollbars to
'allow the user to scroll through the results
If Me.txtOffsetY > 0 Then .Move 10 * Me.txtOffsetY
intDisplay = 1
Do Until .EOF
'the following creates the control names for the textboxes
'with appropriate numbering
strControl1 = "txtDrgNo" & Format$(intDisplay, "00")
strControl2 = "txtTitle" & Format$(intDisplay, "00")
strControl3 = "txtDate" & Format$(intDisplay, "00")

With Forms!frm_drawings_register
'fill in the drawing numbers
.Controls(strControl1) = rstQuery!number
'fill in the drawing names
.Controls(strControl2) = rstQuery!title
'fill in the date drawing received
.Controls(strControl3) = rstQuery.Fields(2).Name
End With

intDisplay = intDisplay + 1
.MoveNext
Loop
.Close
End With

 

[whitsendstudios]

Wolfgang,

That worked perfectly - you are a hero!