473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Flexible Reports

Flexible Reports

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!cbxCosti ngReports.Value)
Then
lblSavedReportName.Caption =
Forms!frmSelectCostingReportFields!cbxCostingRepor ts.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(lng I)).Properties("Caption")
= ReportColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).Properties("Visible")
= True
MaxWidthNumber(lngI) = ActualMaxWidth(lngI)
Report_rptFlexCostingLetter.Controls(TextBoxName(l ngI)).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(l ngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TotalBoxName( lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TextBoxName(l ngI)).Properties("Left")
= LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).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 ja******@oakland.edu Disclaimer: Not OU opinions
Nov 12 '05 #1
1 2530
We also allow up to 10 user-defined grouping levels. The report has 10
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" <ja******@oakland.edu> wrote in message
news:a6**************************@posting.google.c om...
Flexible Reports

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!cbxCosti ngReports.Value) Then
lblSavedReportName.Caption =
Forms!frmSelectCostingReportFields!cbxCostingRepor ts.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(lng I)).Properties("Caption") = ReportColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).Properties("Visible") = True
MaxWidthNumber(lngI) = ActualMaxWidth(lngI)
Report_rptFlexCostingLetter.Controls(TextBoxName(l ngI)).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(l ngI)).Properties("Width") = Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).Properties("Width") = Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TotalBoxName( lngI)).Properties("Width") = Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TextBoxName(l ngI)).Properties("Left") = LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lng I)).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 ja******@oakland.edu Disclaimer: Not OU opinions

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Boštjan Jerko | last post by:
Is there a way to pass list with flexible length to C extension? I want to pass flexible length list of floats to method and just can't get info if it is possible and how to do it. Thanks, B.
5
by: BStorm | last post by:
I have a transaction log file where the DataSet table's Description column is actually delimited into "subcolumns" based upon the transaction id. I would like to parse these into separate fields...
10
by: Adam Warner | last post by:
Hi all, With this structure that records the length of an array of pointers as its first member: struct array { ptrdiff_t length; void *ptr; };
2
by: Christopher Benson-Manica | last post by:
Is the following program conforming under C99? #include <stdio.h> typedef struct foo { int bar; int baz; } foo; foo foos={
2
by: DevarajA | last post by:
Can someone help me understand what flexible array members exactly are, how they behave and how could them be implemented by a i386? Also I didn't understand the two exceptions that the standards...
0
by: athos | last post by:
Hi guys, Got a problem now :( please help... now we got a project handling records saved in a table in a sql 2000(will upgraded to 2005 soon) server. every month around a million records will...
8
by: ulyses | last post by:
I'm trying to put pointer to flexible array of structures in other structure. I want to have pointer to array of pixels in screen structure. Here is mine code, but I think it isn't quite all right:...
20
by: mechanicfem | last post by:
I thought (as they're in c99) that flexible arrays were there for a number of reasons - but I also thought they'd be great for stepping into structures (say) that were aligned in memory after (say)...
3
by: Hallvard B Furuseth | last post by:
to find the required alignment of a struct, I've used #include <stddef.h> struct Align_helper { char dummy; struct S align; }; enum { S_alignment = offsetof(struct Align_helper, align) };
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.