Took less time than I figured - then again, I didn't look at everything because there's a few critical flaws.
Re: qdf field count: Never done this; research shows that you have to use a recordset based on the qdf and count the rs fields. This block
* For Each FieldName In qdf.Fields
doesn't even run.
* Thus FieldList is always "", iCountFieldNames is always 0, iCountFields is always 0 (loop always is 0 to 20)
You build the sql (which always has 20 fields due to the foregoing) and assign it to the qdf, then destroy the qdf before the report finishes opening??
Then the report finishes opening and its record source, which is specified as qryTestResultsPivotQuery in the property sheet, doesn't exist at all (at least not in what you posted). If it was in the Nav pane and you try to create it again, I think you'd generate an error anyway.
Shouldn't the report record source become the newly fashioned qdf?
Shouldn't the procedure that builds this qdf be a function so it can return something to the report opening?
- I'd build the sql, forget the qdf, and pass the sql back to the report during the report open event, as in
Me.RecordSource = CreateReportSql
( FYI: Call keyword not req'd when parameters are not being passed, but not req'd here anyway as I'm assigning it to the RecordSource in one step using the line above)
The open event would branch to the sql creating function
Function CreateReportSql () As String <-- As String defines the output as a string data type
Dim stuff...
create the sql
CreateReportSql = strSql <--assigning the string to the function "passes" the sqll to Me.RecordSource
End Function
First, you need to address the * items. Perhaps go back to where you got the code from and see if you missed something. Maybe look here too
https://access-programmers.co.uk/for...ad.php?t=40862
Not much, but all I could find was a few answers that indicate you have to build a recordset from a query or sql and count those fields. Puzzling, because a qdf seems to have a Fields collection.
That's all for now, I guess; except I always tried to avoid creating reports from crosstabs. Can't recall what happens when a report with 10 controls on it only gets 9 fields from a volatile crosstab.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.