Can I use the same form for multiple reports

[Sheila D]

I have several reports all of which now need to include records from a user
specified date range. The reports are all run from a menu selection.

Do I need to set up a different form as the front end for each report to
collect the date criteria or is there a way of selecting the report and
storing the report name so that the correct report runs after the date
criteria is input. I have a command button on the form to open the report but
can't see how to specify which report automatically. I'm not a great VB user
so all simple help much appreciated!

Sheila

 

[Ryan]

There are many ways to achieve what you are trying to do in vba code. I set
up one form that is my criteria form, and it opens up all of my reports. The
way I set it up is to have all my criteria fields and a drop down list of all
my reports that I name ReportSelection. I build the queries to accept the
criteria from the fields on this form, and then on the command button named
RunReport I have code that opens the report select in the drop down.

Private Sub RunReport_Click()
Dim ReportName As String
ReportName = Me!ReportSelection

If IsNull (Me!ReportSelection) Then
MsgBox "Please select a report from the drop down"
Exit Sub
Else
DoCmd.OpenReport ReportName
End If
End Sub

Hope this helps get you started in the right direction.

 

[fredg]

I have several reports all of which now need to include records from a user
specified date range. The reports are all run from a menu selection.

Do I need to set up a different form as the front end for each report to
collect the date criteria or is there a way of selecting the report and
storing the report name so that the correct report runs after the date
criteria is input. I have a command button on the form to open the report but
can't see how to specify which report automatically. I'm not a great VB user
so all simple help much appreciated!

Sheila

You can use just one form to do this.
1) It seems from your question, that the reports are NOT run, one
after the other in sequence, but randomly.

First, create a query for each report, that will display the fields
you wish to show in the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it a starting and ending date range that you need to use
as criteria on each report..

Next, make a new unbound form.

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code each Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code each Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

2) If the reports are always run together, in sequence, then just code
the first report's Open event to open the ParamForm, and code just the
last report's Close event to close the ParamForm.
The ParamForm will then remain open for all of the reports, and you
will not be prompted for the start or end dates. When the final report
is done, it will close the ParamForm.

 

[Sheila D]

This looks exactly what I need Ryan but how do I get all of my reports in a
drop down list? Do I just use a normal combo box with a Value List.....

Sheila

 

[Sheila D]

Thank you Fred, you're right it is random reports I want to run so I will try
this - it looks good.

Sheila

 

[Klatuu]

Create a table with three fields. One field for the name of the report
object, one for a description of the report that a person will understand,
and the other for name of the field in the report's record source you will
want to filter the report on.
Use a query on the table as the row source for your combo.
SELECT RptOjbName, RptDescr, RptDateFld FROM tblReportList;

Set the column widths property to 0"; 2" (the 0" hides the name of the
report, set the 2" to whatever you need to show the report description.

Then you will want two text boxes or date controls on your form to enter the
date range. And lastly, a command button to run the selected report. The
Click event code would be something like:

Dim strWhere As String

If IsNull(Me.txtToDate) Then
If IsNull(Me.txtFromDate) Then
MsgBox "No Dates Selected"
Exit Sub
Else
strWhere = Me.cboReport.Column(2) & " >= #" & Me.txtFromDate & "#"
End If
Else
If IsNull(Me.txtFromDate) Then
strWhere = Me.cboReport.Column(2) & " <= #" & Me.txtToDate & "#"
Else
strWhere = Me.cboReport.Column(2) & " BETWEEN #" & _
Me.txtFromDate & "# AND #" & Me.txtToDate & "#"
End If
End If

Docmd.OpenReport Me.cboReport, , , strWhere

The above logic gives you the flexibility to select a range of dates, any
date after a specific date, or any date before a specific date.

 

[Ryan]

Yes, use an unbound combo box, and just make sure that you name the values in
the value list exactly what the report names are.

 

[Klatuu]

Ryan,

Your answer is good and a value list would work, but there is one important
datbase concept:
"Data belongs in Tables"
A list of reports is data, and therefore the reports to run should be in a
table. One important reason is that when a new report is added, it only
requires an addition of a row to the table rather than a change to the form
and a redeployment of the front end.
Just a consideration for you.

 

[Sheila D]

Fred I've just tried this and it works great so thank you very much, also
thanks to everybody for their suggestions which I will try at some stage to
improve my knowledge
Sheila

 

[Ken Sheridan]

Sheila:

You might also be interested in how you can automatically fill a list box of
available reports without having to store their names in a table (Access
already stores them in the reports documents container). In this example
only reports which have had a Description assigned to them via the database
window and whose names begin with 'rpt' are listed. This excludes subreports
(whose names might begin with 'srpt' for instance) and allows you to give
users a list of plain English descriptions of the reports to select from
rather than cryptic names, e.g. 'Monthly budget report' rather than
'rptMonthlyBudget'. You could of course use different naming conventions for
different classes of report, e.g. financial reports might start 'rptfin',
personnel reports 'rptper' and so on. You can then list only financial
reports by amending the code in the function below to:

If Left(doc.Name, 6) = "rptfin" Then

The technique involves the use of a 'call-back' function. First you add the
function to the form's module (just copy and paste it from below; no need to
write the code)

Function ReportList(fld As Control, ID As Variant, row As Variant, col As
Variant, Code As Variant) As Variant

Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim ctr As DAO.Container
Dim varReturnVal As Variant
Dim n As Integer
Static intEntries As Integer
Static aData() As Variant


Select Case Code
Case acLBInitialize
Set dbs = CurrentDb
Set ctr = dbs.Containers("Reports")
intEntries = ctr.Documents.Count
ReDim aData(intEntries, 1)
For Each doc In ctr.Documents
If Left(doc.Name, 3) = "rpt" Then
aData(n, 0) = doc.Name
On Error Resume Next
aData(n, 1) = doc.Properties("Description")
If Err = 0 Then
n = n + 1
Else
intEntries = intEntries - 1
End If
On Error GoTo 0
Else
intEntries = intEntries - 1
End If
Next doc
varReturnVal = True
Case acLBOpen
varReturnVal = Timer
Case acLBGetRowCount
varReturnVal = intEntries
Case acLBGetColumnCount
varReturnVal = 2
Case acLBGetColumnWidth
varReturnVal = -1
Case acLBGetValue
varReturnVal = aData(row, col)
Case acLBEnd
Erase aData
End Select
ReportList = varReturnVal

End Function

The function is called by setting the list box's RowSourceType property to:

ReportList

Note that, unusually, no parentheses follow the function name when its
entered in the control's properties sheet. Also don't confuse the
RowSourceType property with the RowSource property; the latter is left blank.

Set the list box's ColumnWidths property to 0cm8cm (Access will
automatically convert to inches if you are not using metric units). You
don't need to change the ColumnCount property as the function sets this when
called.

If you set the list box's MultiSelect property to Simple or Extended (the
former allows multiple seleections by simple clicking one by one, the latter
by Ctrl+Click or Shift+Click in the usual Windows way) the user can select
more than 1 report to open. If you add a check box chkOpenAll then the user
can use this to select whether they want all the selected reports open at one
time in print preview, or opened successively in preview one after the other
as each is closed. The code for a button on the form to open the report(s)
would be:

Dim varItem As Variant
Dim rpt As Report

If lstReports.ItemsSelected.Count > 0 Then
For Each varItem In lstReports.ItemsSelected
DoCmd.OpenReport lstReports.ItemData(varItem), acViewPreview
If Not Me.chkOpenAll Then
' wait for report to be closed
' before opening next one if
' check box unchecked
Do While True
On Error Resume Next
Set rpt = Reports(lstReports.ItemData(varItem))
If Err <> 0 Then
Err.Clear
Exit Do
End If
DoEvents
Loop
End If
Next varItem
End If

where lstReports is the name of the multi-select list box.

If you want another button to print the reports the code is simpler:

Dim varItem As Variant

If lstReports.ItemsSelected.Count > 0 Then
For Each varItem In lstReports.ItemsSelected
DoCmd.OpenReport lstReports.ItemData(varItem)
Next varItem
End If

The form can of course include controls such as your date parameters for
restricting the report's results, in which case each the queries on which all
of the reports are based would reference as parameters the same controls on
the same form containing the list box. When using date parameters in a
query, however, I would advise that the parameters be declared as DateTime
data type. Otherwise there is a possibility that a date parameter in short
date format might be interpreted as an arithmetical expression not a date,
and give wrong results.

Ken Sheridan
Stafford, England

 

[Sheila D]

Thank you Ken, that's very comprehensive

Sheila

Sheila:

You might also be interested in how you can automatically fill a list box of
available reports without having to store their names in a table (Access
already stores them in the reports documents container). In this example
only reports which have had a Description assigned to them via the database
window and whose names begin with 'rpt' are listed. This excludes subreports
(whose names might begin with 'srpt' for instance) and allows you to give
users a list of plain English descriptions of the reports to select from
rather than cryptic names, e.g. 'Monthly budget report' rather than
'rptMonthlyBudget'. You could of course use different naming conventions for
different classes of report, e.g. financial reports might start 'rptfin',
personnel reports 'rptper' and so on. You can then list only financial
reports by amending the code in the function below to:

If Left(doc.Name, 6) = "rptfin" Then

The technique involves the use of a 'call-back' function. First you add the
function to the form's module (just copy and paste it from below; no need to
write the code)

Function ReportList(fld As Control, ID As Variant, row As Variant, col As
Variant, Code As Variant) As Variant

Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim ctr As DAO.Container
Dim varReturnVal As Variant
Dim n As Integer
Static intEntries As Integer
Static aData() As Variant


Select Case Code
Case acLBInitialize
Set dbs = CurrentDb
Set ctr = dbs.Containers("Reports")
intEntries = ctr.Documents.Count
ReDim aData(intEntries, 1)
For Each doc In ctr.Documents
If Left(doc.Name, 3) = "rpt" Then
aData(n, 0) = doc.Name
On Error Resume Next
aData(n, 1) = doc.Properties("Description")
If Err = 0 Then
n = n + 1
Else
intEntries = intEntries - 1
End If
On Error GoTo 0
Else
intEntries = intEntries - 1
End If
Next doc
varReturnVal = True
Case acLBOpen
varReturnVal = Timer
Case acLBGetRowCount
varReturnVal = intEntries
Case acLBGetColumnCount
varReturnVal = 2
Case acLBGetColumnWidth
varReturnVal = -1
Case acLBGetValue
varReturnVal = aData(row, col)
Case acLBEnd
Erase aData
End Select
ReportList = varReturnVal

End Function

The function is called by setting the list box's RowSourceType property to:

ReportList

Note that, unusually, no parentheses follow the function name when its
entered in the control's properties sheet. Also don't confuse the
RowSourceType property with the RowSource property; the latter is left blank.

Set the list box's ColumnWidths property to 0cm8cm (Access will
automatically convert to inches if you are not using metric units). You
don't need to change the ColumnCount property as the function sets this when
called.

If you set the list box's MultiSelect property to Simple or Extended (the
former allows multiple seleections by simple clicking one by one, the latter
by Ctrl+Click or Shift+Click in the usual Windows way) the user can select
more than 1 report to open. If you add a check box chkOpenAll then the user
can use this to select whether they want all the selected reports open at one
time in print preview, or opened successively in preview one after the other
as each is closed. The code for a button on the form to open the report(s)
would be:

Dim varItem As Variant
Dim rpt As Report

If lstReports.ItemsSelected.Count > 0 Then
For Each varItem In lstReports.ItemsSelected
DoCmd.OpenReport lstReports.ItemData(varItem), acViewPreview
If Not Me.chkOpenAll Then
' wait for report to be closed
' before opening next one if
' check box unchecked
Do While True
On Error Resume Next
Set rpt = Reports(lstReports.ItemData(varItem))
If Err <> 0 Then
Err.Clear
Exit Do
End If
DoEvents
Loop
End If
Next varItem
End If

where lstReports is the name of the multi-select list box.

If you want another button to print the reports the code is simpler:

Dim varItem As Variant

If lstReports.ItemsSelected.Count > 0 Then
For Each varItem In lstReports.ItemsSelected
DoCmd.OpenReport lstReports.ItemData(varItem)
Next varItem
End If

The form can of course include controls such as your date parameters for
restricting the report's results, in which case each the queries on which all
of the reports are based would reference as parameters the same controls on
the same form containing the list box. When using date parameters in a
query, however, I would advise that the parameters be declared as DateTime
data type. Otherwise there is a possibility that a date parameter in short
date format might be interpreted as an arithmetical expression not a date,
and give wrong results.

Ken Sheridan
Stafford, England