please help

[Bobby.Dannels]

I created a list box called lstReports. I am trying to have the list
box display all reports in the database. There are two command
buttons, chkPreview and cmdPrintReport. All reports are pulled from
the same table called Data Table or querries that pull the info from
that table. There are also two Option Buttons (optAll and optSection)
with at combo box to filter the reports by a column called Section.
How can I get this setup to work correctly? I am totally lost.

 

[Ken Sheridan]

As I understand things you have a table called Data Table with columns for
the report name, Report Name say, and a column Section. You have a combo
box, cboSection say, from which to select a Section to restrict the list box
to reports in that section. You also have an option group with two buttons;
lets assume the option group is called frameRestrictList and its optAll value
is 1 and its optSection value is 2, one of these being its default value.

The RowSource property of the combo box would be:

SELECT Section FROM [Data Table] ORDER BY Section;

The RowSource property of the list box would need to reference both the
combo box and the option group:

SELECT [Report Name] FROM [Data Table] WHERE (Section = Form!cboSection AND
Form!frameRestrictList = 2) OR Form!frameRestrictList = 1 OR Form!cboSection
IS NULL ORDER BY [Report Name];

In the AfterUpdate event procedures of both the cboSection control and the
frameRestrictList control requery the list box:

Me.lstReports.Requery

To print the report the code for the button's Click event procedure would be
something like this:

Const conMESSAGE = "No report selected."
Dim varReport as Variant

varReport = Me.lstReports

If Not IsNull(varReport) Then
DoCmd.OpenReport varReport
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

The code for the button to preview the report would be the same but for one
line:

DoCmd.OpenReport varReport, View:=acViewPreview


Ken Sheridan
Stafford, England

 

[Keith Wilby]

I created a list box called lstReports. I am trying to have the list
box display all reports in the database. There are two command
buttons, chkPreview and cmdPrintReport. All reports are pulled from
the same table called Data Table or querries that pull the info from
that table. There are also two Option Buttons (optAll and optSection)
with at combo box to filter the reports by a column called Section.
How can I get this setup to work correctly? I am totally lost.

Set the row source of the (unbound) list box to the table (or query) that
returns the list of reports. The way I do it is to have a report
description field and a report name field. I use code thusly on the preview
button's click event:

DoCmd.OpenReport Me.lstReport.Column(1), acViewPreview

I set the list box column widths such that only the description is visible.
As you can see, the report name is in the second column.

HTH - Keith.
www.keithwilby.com

 

[Ken Sheridan]

Keith:

You can also do that dynamically by using a call-back function as the
RowSourceType (NB not the RowSource which is left blank) property of the list
box. This list all reports which have Description properties (entered via
the database window). The following function is added to the form's module:

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 RowSourceType property of the list box is set to ReportList; note that
for this no parentheses follow the function name as is usually the case. You
could of course limit the list to particular categories of report by adopting
some naming convention for them and amending the code slightly so that it
only returns a particular set of reports. e.g. where the name ends "_Sales"
for all sales reports, or "_Budget" for all budgetary reports. In the above
case the list is limited to those beginning "rpt" to distinguish them from
subreports, which I prefix "subrpt".

With later versions of Access its now also possible to iterate through the
AllReports collection to list the reports of course rather than using a
call-back function as above.

If a multi-select list box is used then multiple reports can be selected and
opened with one button click using the following in the button's Click event
procedure:

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

The reports can be viewed one by one by unchecking the chkOpenAll check box
on the form, or all together in separate windows if its checked, the default
in my case.

Ken Sheridan
Stafford, England

 

[Keith Wilby]

Keith:

You can also do that dynamically by using a call-back function as the
RowSourceType (NB not the RowSource which is left blank) property of the
list
box. This list all reports which have Description properties (entered via
the database window). The following function is added to the form's
module:

<snip>

That's an excellent piece of information, thanks Ken.

Keith.

 

[Keith Wilby]

Just one question Ken, in my reports table I have an "argument" field
(Boolean) which I use to set the Enabled property of a couple of combo boxes
on the report selection form. Can your method handle this scenario?

Thanks.

Keith.

 

[Keith Wilby]

Can your method handle this scenario?

I think I've just answered my own question. They way I'd do it would be to
include a string (eg "TRUE") in the report name. Any comments?

 

[Keith Wilby]

Sorry about the multiple posts. I've just discovered a run-time error.
When I click on an item in the list box I get "Subscript out of range" at
this line:

varReturnVal = aData(row, col)

 

[Ken Sheridan]

Firstly using a naming convention is the right way to identify the reports
with the True argument values.

As regards the error my guess would be that the array might not be being
redimensioned correctly in the code. Post back the complete code for the
call-back function as you are using it.

Ken Sheridan
Stafford, England

 

[Keith Wilby]

Firstly using a naming convention is the right way to identify the reports
with the True argument values.

As regards the error my guess would be that the array might not be being
redimensioned correctly in the code. Post back the complete code for the
call-back function as you are using it.

Thanks Ken, my fault, another part of my code was interfering with the
function. All working now. Many thanks.

Keith.