Feeding function arguments into crosstab query

Question

I have a subform that I am trying to alter based on multiple checkbox selections. For each checkbox I have written an after update event that calls a public function based on which boxes were checked/how many were selected. The first part of the function is as follows, where Home is the main form and "ALLProgressTracking" is the subform:

Public Function ProgressDisplay(argNoFields As Integer, Optional argField1 As String, Optional argField2 As String, Optional argField3 As String)

Dim strSQL As String

If argNoFields = 1 Then
strSQL = "TRANSFORM Sum([StatusTracking Query].CountofProjectID) AS CountOfProjects " & _
            "SELECT [StatusTracking Query]." & argField1 & " " & _
            "FROM [StatusTracking Query] " & _
            "GROUP BY [StatusTracking Query]." & argField1 & " " & _
            "PIVOT [StatusTracking Query].ValueDate;"

Forms!Home![ALLProgressTracking].SourceObject = "Form.IndProgressTracking"
Forms!Home![ALLProgressTracking].Form.RecordSource = strSQL

The issue is that I have the forms already built with one of the three possible fields bound as one of the row headings (it is a crosstab form/query) therefore when I try to alter the SQL with the code above for an alternate field I get #Name rather than the proper row headings. I am guessing it is a matter of altering the SQL statement to reference the function for the subform that I am calling as the source object but I am lost as to how to accomplish this.

For example, for the form IndProgressTracking I have the following SQL for the form:

TRANSFORM Sum([StatusTracking Query].CountOfProjectID) AS SumOfCountOfProjectID
SELECT [StatusTracking Query].Category
FROM [StatusTracking Query]
GROUP BY [StatusTracking Query].Category
PIVOT [StatusTracking Query].ValueDate;

And when I feed "Status" into the function the form will load with the correct numbers for project count however #Name as all the row headings.

Hope this all makes sense and someone is able to help!

Yes, I need the query/form to display the count of projects in each status/category/stage for each month over the course of around a year, therefore I thought a crosstab would be the best option. — madelina
Perhaps see if you can get the same query result using a SELECT query with counts and group totals. I've not used crosstabs much, but I would guess your problem is due to using a crosstab query as a form's recordsource and then trying to update it. — Matt Hall

Gustav Gustav · Accepted Answer · 2016-03-22T16:31:20

You could alias the field name(s) to be static:

TRANSFORM Sum([StatusTracking Query].CountOfProjectID) AS SumOfCountOfProjectID
SELECT [StatusTracking Query].Category As ArgField1
FROM [StatusTracking Query]
GROUP BY [StatusTracking Query].Category
PIVOT [StatusTracking Query].ValueDate;

Feeding function arguments into crosstab query

2 Answers