A chunk of code off this list a while ago...maybe it could get you started?
??
Creating an Access Report at run time Originally sent be David Chapman [
luckychap@b...]
Subject: [access] RE: Creating an Access Report at run time
" The code below creates a report where the number of colums is dependant on
unique values in a table. It executes this sql, and from the resulting
table, it generates the report, and further SQL as the record source. It has
worked well for three years, the only time it fell over was because they
entered data that caused illegal column names. You can't allow data to
become variable names.
Note it is a function, it returns the name of the new report, it is executed
from the onclick event of a "Print Report" button. eg docmd.openreport
function(parms). "
Function CreateVisitsDetail(StartDate As Date, EndDate As Date) As Variant
Dim rpt As Report, CTL As Control
Dim RS As Recordset
Dim DB As Database
Dim statname
Dim SQLstr
Dim CtlTop As Integer, CtlHeight As Integer
' Calculate the width of columns in the report
' Process a dynamic table of distinct Stats catagories
Set DB =3D CurrentDb
Set RS =3D DB.OpenRecordset("SELECT DISTINCT Visits.SC " & _
"FROM Visits " & _
"WHERE ((Visits.SC Is Not Null) " & _
"AND (Visits.VisitDate>=3D#" &
Format([StartDate], "m/d/yyyy") & "#) " & _
"AND (Visits.VisitDate<=3D#" &
Format([EndDate], "m/d/yyyy") & "#));")
' Find out how many columns are
required - qryStats
If RS.RecordCount =3D 0 Then
MsgBox "No data was produced for the period requested, Please check
the dates"
CreateVisitsDetail =3D ""
RS.Close
Exit Function
End If
' Calculate a default column width, CtlWidth is a public long in twips
CtlWidth =3D PageWidth / (RS.RecordCount * 2 + 4)
' Create the report and set defaults for the controls
Set rpt =3D CreateReport("", "rptStatsTemplate")
With rpt.DefaultControl(acTextBox)
.Width =3D CtlWidth
.Height =3D 0.5 * TPC
.FontName =3D "Times New Roman"
.FontItalic =3D False
.FontSize =3D 10
.TextAlign =3D 3
End With
With rpt.DefaultControl(acLabel)
.Width =3D CtlWidth
.Height =3D 1 * TPC
.FontName =3D "Times New Roman"
.FontItalic =3D True
.FontSize =3D 10
.FontWeight =3D 700
.TextAlign =3D 2
End With
' Create the main heading
Set CTL =3D CreateReportControl(rpt.Name, acLabel, acHeader, "", "", 0,
0,
26 * TPC, 0.9 * TPC)
With CTL
.Caption =3D "OH&&S Detail Report From " & Format(StartDate, "Sho
rt
Date") & " to " & Format(EndDate, "Short Date") & " Health Centre
Attendances"
.FontItalic =3D False
.FontSize =3D 20
.TextAlign =3D 1
End With
' Line below Heading
Set CTL =3D CreateReportControl(rpt.Name, acLine, acPageHeader, "", "",
0,
1.1 * TPC, PageWidth, 0.026 * TPC)
CTL.BorderWidth =3D 1
' Top line of footer above totals
Set CTL =3D CreateReportControl(rpt.Name, acLine, acFooter, "", "", 0,
0,
PageWidth, 0.026 * TPC)
CTL.BorderWidth =3D 1
' Bottom Line in Footer below totals
Set CTL =3D CreateReportControl(rpt.Name, acLine, acFooter, "", "", 0,
1 *
TPC, PageWidth, 0.026 * TPC)
CTL.BorderWidth =3D 1
' Date at bottom of Page
CtlTop =3D 0.2 * TPC
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acPageFooter, "",
"",
0, CtlTop, 6 * TPC, 0.4 * TPC)
With CTL
.Name =3D "Today"
.FontItalic =3D True
.FontSize =3D 8
.Format =3D "Long Date"
.ControlSource =3D "=3Dnow()"
.TextAlign =3D 1
End With
' Page number at bottom of Page
CtlTop =3D 0.2 * TPC
CtlHeight =3D 0.4 * TPC
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acPageFooter, "",
"",
PageWidth - (4 * TPC), CtlTop, 4 * TPC, CtlHeight)
With CTL
.Name =3D "PageNos"
.FontItalic =3D True
.FontSize =3D 8
.Format =3D "Long Date"
.ControlSource =3D "=3D'Page ' & [Page] & ' of ' & [Pages]"
.TextAlign =3D 3
End With
' Division Heading
CtlLeft =3D 0
CtlTop =3D 0
Set CTL =3D CreateReportControl(rpt.Name, acLabel, acPageHeader, "", "",
CtlLeft, CtlTop, 4 * CtlWidth)
CTL.FontSize =3D 16
CTL.Caption =3D "Division"
CTL.TextAlign =3D 1
' Division Text
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acDetail, "",
"Division", CtlLeft, CtlTop, 4 * CtlWidth)
CTL.TextAlign =3D 1
CtlLeft =3D CtlLeft + (4 * CtlWidth) ' Reset ctlleft for
next control
' Start the new SQLstr
SQLstr =3D "SELECT Visits.Division, "
' Put the controls on the report
Dim cc
cc =3D 0 ' CC is a counter in
the
while loop.
RS.MoveFirst ' It is used to create
unique column names
While Not RS.EOF
cc =3D cc + 1
CtlTop =3D 0
' Heading
Set CTL =3D CreateReportControl(rpt.Name, acLabel, acPageHeader, "",
"", CtlLeft, CtlTop, CtlWidth * 2)
CTL.Caption =3D EditHead(RS!SC)
' Text
CtlTop =3D 0
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acDetail, "",
"Col" & cc, CtlLeft, CtlTop, CtlWidth)
CTL.Name =3D "Col" & cc
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acDetail, "",
"Rv" & cc, CtlLeft + CtlWidth, CtlTop, CtlWidth)
CTL.Name =3D "Rv" & cc
CTL.TextAlign =3D 1
CTL.Format =3D "###;(##)"
'Totals
CtlTop =3D 0.2 * TPC
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acFooter, ,
"=3Dsum(val([Col" & cc & "]))", CtlLeft, CtlTop, CtlWidth)
Set CTL =3D CreateReportControl(rpt.Name, acTextBox, acFooter, ,
"=3Dsum(val([Rv" & cc & "]))", CtlLeft + CtlWidth, CtlTop, CtlWidth)
CTL.FontWeight =3D 500
CTL.TextAlign =3D 1
CTL.Format =3D "###;(##)"
' Create the SQL for the source of the report
SQLstr =3D SQLstr & _
"Sum(IIf([Oc]< 0 and [SC]=3D'" & RS!SC & "',1,0)) AS " & _
"Col" & cc & ", "
SQLstr =3D SQLstr & _
"Sum(IIf([Oc]< 0 and [SC]=3D'" & RS!SC & "' and [Rv],-1,0)) AS " & _
"Rv" & cc & ", "
RS.MoveNext
' Reset ctlLeft to the column of the next set of controls
CtlLeft =3D CtlLeft + (2 * CtlWidth)
Wend
RS.Close
SQLstr =3D SQLstr & _
"Sum(IIf([Oc]=3D0,1,0)) AS NonOc, " & _
"Sum(IIf([Oc]=3D0 And [Rv],-1,0)) AS rptNonOc, " & _
"Sum(1) AS Tot, Sum(Visits.Rv) AS rptTot " & _
"FROM Visits " & _
"WHERE (Visits.VisitDate>=3D#" & Format([StartDate], "m/d/yyyy") &
"#)
" & _
"AND (Visits.VisitDate<=3D#" & Format([EndDate], "m/d/yyyy") & "#
) "
& _
"GROUP BY Visits.Division;"
rpt.RecordSource =3D SQLstr
CreateVisitsDetail =3D rpt.Name
End Function
View Cart
