Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler
'===================================================
'Dynamic report designed to be used with Crosstab Queries
'This code is especially "tuned" for crosstab queries.
'As I like to have control over the layout, thus I have the lay-out designed first with "coded" controls.
'Then the dynamic filling becomes very easy. The raw text I use to help with this is:
'Making the column header and detail data flexible is possible,
'but needs some VBA code in the OpenReport event.
'To start doing this you need to place the fields "coded" in the report.
'The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
'The "detail" fields should be called "Col1", "Col2", "Col3", etc.
'The report query has two row header columns and a Total column,
'therefore the first field is effectively column 4 (count starts at 0 so I used intI=3)
'but this could differ for you.
'The report has 36 columns so can handle up to 36 file types
'If number of file types > 36, error 2465 occurs & the report exits.
'=====================================================
Dim intI As Integer
Dim Rs As DAO.Recordset
strSQL = "TRANSFORM First(tblExtPropsFileType.ExtPropValue) AS FirstOfExtPropValue" & _
" SELECT tblExtPropsFileType.ID, tblExtPropsFileType.ExtProperty" & _
" FROM tblExtPropsFileType" & _
" GROUP BY tblExtPropsFileType.ID, tblExtPropsFileType.ExtProperty" & _
" ORDER BY tblExtPropsFileType.ID" & _
" PIVOT UCase(tblExtPropsFileType.FileExtension);"
'Debug.Print strSQL
Set Rs = CurrentDb.OpenRecordset(strSQL)
'Populate headers - start at column 2
For intI = 2 To Rs.Fields.Count - 1
Me("Lbl" & intI - 1).Caption = Rs.Fields(intI).Name
Me("Lbl" & intI - 1).Visible = True
Next intI
'Populate checkbox controls
For intI = 2 To Rs.Fields.Count - 1
Me("chk" & intI - 1).ControlSource = Rs.Fields(intI).Name
Me("chk" & intI - 1).Visible = True
Next intI
'Populate totals
'each filled value = -1 (true) so do -SUM to get total
For intI = 2 To Rs.Fields.Count - 1
Me("Tot" & intI - 1).ControlSource = "=-SUM([" & Rs.Fields(intI).Name & "])"
Me("Tot" & intI - 1).Visible = True
Next intI
Exit_Handler:
Exit Sub
Err_Handler:
If Err = 2465 Then 'too many data columns for report (max = 36)
N = DCount("FileExtension", "tblExtPropsFileType", "ExtProp='Name'")
If N > 36 Then
FormattedMsgBox "Extended property summary data has been saved for " & N & " file types " & _
"@The summary report can only show results for the first 36 file types " & vbNewLine & vbNewLine & _
"Delete unwanted data from the table 'tblExtPropsFileType' before running this report @", vbCritical, "Too much data!!"
Cancel = True
Exit Sub
End If
Else
strProc = "Report_Open"
MsgBox "Error " & Err.Number & " in " & strProc & " procedure: " & Err.Description
Resume Exit_Handler
End If
End Sub
