generic (user-created) crosstab

[scubadiver]

hello,

how can I construct an SQL that will allow a user to create their own
crosstab queries in a form and place the result in a subform?

thanks

 

[scubadiver]

I've made progress. I put the following in the "click" event of a button:


Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("myQuery")
qdf.SQL = "TRANSFORM Sum(Query8.FaceVal) AS SumOfFaceVal" & vbCrLf & _
"SELECT " & [Forms]![crosstabs]![row] & vbCrLf & _
"FROM query8" & vbCrLf & _
"GROUP BY " & [Forms]![crosstabs]![row] & vbCrLf & _
"PIVOT " & [Forms]![crosstabs]![col] & ";"
Me.subformctrl.SourceObject = "query.myquery"
Set qdf = Nothing
Set db = Nothing
Exit Sub

 

[scubadiver]

I have a new problem. I have managed to choose a row and column and insert
it into the subform. If I then try to choose a new query, I can't delete the
previous one because it is open.

How can I overcome this?

 

[scubadiver]

I deleted the source object and then deleted the query before inserting the
new one. I wish I thought things through without posting!


Me.subformctrl.SourceObject = ""

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "myquery"
DoCmd.SetWarnings True