Private Sub Command38_Click()
Dim sql As String
sql = "PARAMETERS [Forms]![JobQuote]![JobID] Short; " & _
"TRANSFORM First(ContractorCountQry.Count) AS FirstOfCount " & _
"SELECT ContractorCountQry.TypeName, Sum(ContractorCountQry.Count) AS [Total Of Count] " & _
"FROM ContractorCountQry " & _
"GROUP BY ContractorCountQry.TypeName " & _
"PIVOT ContractorCountQry.Contractor;"
CreateAutoReport sql
End Sub
Public Sub CreateAutoReport(strSQL As String)
Dim rptReport As Access.Report
Dim strCaption As String
Dim rpt As Access.Report
CurrentDb.QueryDefs("qryDummy").sql = strSQL
' Open dummy query to invoke NewObjectAutoReport command on it
' Put the report created to design view to make properties editable
With DoCmd
.OpenQuery "ContractorCountQry_Crosstab", acViewNormal
.RunCommand acCmdNewObjectAutoReport
.Close acQuery, "ContractorCountQry_Crosstab"
.RunCommand acCmdDesignView
End With
' Get reference to just created report
For Each rpt In Reports
If rpt.Name Like "ContractorCountQry_Crosstab*" Then Set rptReport = rpt
Next
With rptReport
' Create timestamp on footer
CreateReportControl .Name, acLabel, _
acPageFooter, , Now(), 0, 0
' Create page numbering on footer
With CreateReportControl(.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
.Width - 1000, 0)
.SizeToFit
End With
' Detach the report from dummy query
'.RecordSource = strSQL
' Set the report caption to autogenerated unique string
strCaption = GetUniqueReportName
If strCaption <> "" Then .Caption = strCaption
End With
DoCmd.RunCommand acCmdPrintPreview
Set rptReport = Nothing
End Sub
Public Function GetUniqueReportName() As String
Dim intCounter As Integer
Dim blnIsUnique As Boolean
For intCounter = 1 To 256
GetUniqueReportName = "rptAutoReport_" & Format(intCounter, "0000")
blnIsUnique = True
For Each rpt In CurrentProject.AllReports
If rpt.Name = GetUniqueReportName Then blnIsUnique = False
Next
If blnIsUnique Then Exit Function
Next
GetUniqueReportName = ""
End Function
