Flexible Reports
James Fortune
I only did this because a client required this functionality. No
guarantees or rights are implied by the code shown here. It is
provided mostly for those with extra time on their hands.
Suppose a base table has many fields. It may be desirable to allow
the user to
select any fields (say up to 10 fields) in any order and have a report
set up headings, values and totals for the selected fields.
To do this I created a field selection form. This form has a check
box for every field in the base table along with a listbox to show the
fields in the order selected by the user. I also have a command
button to clear all the columns and a combobox to select and fill
saved report configurations. When a checkbox is unchecked it takes
the field out of the list. This form interacts with the following
table:
tblCostingReportColumnOrder:
ColumnName T50 --The name of the field in the base table
ListOrderNumber Int --All 0's except for 1, 2, 3, etc. of selected
fields
ReportColumnName T50 --Aliases for the ColumnName heading
ValsIndex Int --Used to order the ColumnName values
FieldType T50 --Double, Date, Text, Currency, etc.
MaxWidth Int --For text it's the field size, Y/N is 2, Currency is 10
ActualMaxWidth Int --Value computed from qryFillFlex
The 'Print Report' command button on the same form creates a SQL
string that selects fields whose ListOrderNumber > 0. This SQL string
is used to calculate the maximum characters used by each field (Note:
ActualMaxWidth >= Heading Width). The sum of these maximums is used
to decide whether to open a report in LetterPortrait, LetterLandscape,
LegalLandscape or LegalLandscapeVariableFont (shrink font to fit
page).
The report has a RecordSource of qryFillFlex. The report has all the
headings invisible and stacked on top of one another in the Page
Header. The text boxes with corresponding Control Sources are in the
Detail Section. The text boxes for totals are in the Report Footer.
Note that these must have names that look like lbl<FieldName>,
txt<FieldName> and txtTotal<FieldName> for the way it was implemented
here.
Note: This report is usually used from a search form that calls the
field selection form and also creates qryFillFlex to limit the records
to those shown on the search subform.
The Report_Open code for LetterPortrait looks like:
------------------------------------
Private Sub Report_Open(Cancel As Integer)
Dim MyDB As Database
Dim CRS As Recordset
Dim ColumnName(10) As String
Dim ReportColumnName(10) As String
Dim ActualMaxWidth(10) As Integer
Dim LabelName(10) As String
Dim TextBoxName(10) As String
Dim TotalBoxName(10) As String
Dim MaxWidthNumber(10) As Long
Dim LeftNumber(10) As Long
Dim FieldWidth(10) As Long
Dim strSQL As String
Dim lngCount As Long
Dim lngI As Long
Dim TotalWidthInTwips As Long
Dim TotalCharacters As Long
Dim Response As Variant
Dim strPrompt As String
Dim strTitle As String
strTitle = "Get Report Title"
strPrompt = "Click OK or Type in a Report Title"
Response = InputBox(strPrompt, strTitle, "Costing Report")
lblTitle.Caption = Nz(Response, "")
If IsFormOpen("frmSelectCostingReportFields") Then
If Not IsNull(Forms!frmSelectCostingReportFields!cbxCostingReports.Value)
Then
lblSavedReportName.Caption =
Forms!frmSelectCostingReportFields!cbxCostingReports.Value
End If
End If
'Obtain chosen fields and ActualMaxWidth values so that
'appropriate spacing can be chosen
'Select only the first 10 fields chosen
'Report_rptFlexCostingLetter.RecordSource =
GetstrFlexCostingReportSQL()
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblCostingReportColumnOrder WHERE "
strSQL = strSQL & "[ListOrderNumber] > 0 AND [ListOrderNumber] < 11 "
strSQL = strSQL & "ORDER BY ListOrderNumber DESC;"
Set CRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
'Get ActualMaxWidth and Names of fields chosen
If CRS.RecordCount > 0 Then
CRS.MoveLast
lngCount = CRS.RecordCount
CRS.MoveFirst
TotalWidthInTwips = Int(9.9 * 1440) '9.9 allows 1" margins for
letter portrait
For lngI = 1 To lngCount
ColumnName(lngI) = CRS("ColumnName")
ReportColumnName(lngI) = Nz(CRS("ReportColumnName"), "")
ActualMaxWidth(lngI) = CRS("ActualMaxWidth")
If ActualMaxWidth(lngI) < Len(Nz(ReportColumnName(lngI), "")) Then
ActualMaxWidth(lngI) = Len(Nz(ReportColumnName(lngI), ""))
'Require each field to occupy at least 10 characters
If ActualMaxWidth(lngI) < 10 Then ActualMaxWidth(lngI) = 10
LabelName(lngI) = "lbl" & ColumnName(lngI)
TextBoxName(lngI) = "txt" & ColumnName(lngI)
TotalBoxName(lngI) = "txtTotal" & ColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Caption")
= ReportColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Visible")
= True
MaxWidthNumber(lngI) = ActualMaxWidth(lngI)
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Visible")
= True
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Visible")
= True
If lngI <> lngCount Then CRS.MoveNext
Next lngI
TotalCharacters = 0
For lngI = 1 To lngCount
TotalCharacters = TotalCharacters + ActualMaxWidth(lngI)
Next lngI
For lngI = 1 To lngCount
FieldWidth(lngI) = MaxWidthNumber(lngI) * TotalWidthInTwips /
TotalCharacters
Next lngI
LeftNumber(1) = 0
If lngCount >= 2 Then
For lngI = 2 To lngCount
LeftNumber(lngI) = LeftNumber(lngI - 1) + FieldWidth(lngI - 1)
Next lngI
End If
For lngI = 1 To lngCount
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Left")
= LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Left")
= LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Left")
= LeftNumber(lngI)
Next lngI
End If
CRS.Close
Set CRS = Nothing
Set MyDB = Nothing
End Sub
-------------------------------------
James A. Fortune jafo...@oakland.edu Disclaimer: Not OU opinions
Bradley
levels defined and we plug in the field value at runtime and turn off the
levels we don't use (on the OnFormat event of each level).
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response
"James Fortune" <jafo...@oakland.edu> wrote in message
news:a6ed3ce7.04022...@posting.google.com...