Best way to list all available reports?

[Guest]

In a database which contains a variety of possible reports is there a way to
create something like a list box (on a form) showing an updated list of all
available reports from which the user can select which report to view?

I really want something equivalent to a kind of scaled down version of the
Database Window, without actually having to allow the user to view the
Database Window itself, and I can't quite figure out how to approach this.

Is there a way to have a field in a table or query that is populated by all
available ReportNames (or ReportCaptions, better still)?

 

[Guest]

Thanks Jerry.

This successfully gives me a listbox listing all the reports by name, but
how would I extend the sql query to include a column that gives the report
Captions as well? The report names in my database are naming convention names
like "rptProposalsByCategory" and I'd like to show the captions instead which
are in plain English.

 

[Douglas J. Steele]

I use the following function. (You set the RowSourceType property to
ListReports, and leave the RowSource property blank)

Function ListReports( _
lstListbox As Control, _
varID As Variant, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer _
) As Variant

On Error GoTo Err_ListReports

Select Case intCode
Case acLBInitialize
ListReports = True
Case acLBOpen
ListReports = Timer
Case acLBGetRowCount
ListReports = CurrentDb().Containers("Reports").Documents.Count
Case acLBGetColumnCount
ListReports = 2
Case acLBGetColumnWidth
Select Case lngCol
Case 0
ListReports = 2800
Case 1
ListReports = 2800
Case Else
ListReports = 0
End Select
Case acLBGetValue
Select Case lngCol
Case 0
ListReports =
CurrentDb().Containers("Reports").Documents(lngRow).Name
Case 1
ListReports =
CurrentDb().Containers("Reports").Documents(lngRow).Properties("Description")
Case Else
ListReports = vbNullString
End Select
End Select

End_ListReports:
Exit Function

Err_ListReports:
' Error 3270: Property not found.
' This means that there isn't a description associated with the report.
' In that case, we want to return an empty string and continue.
If Err.Number = 3270 Then
ListReports = vbNullString
Resume Next
Else
Err.Raise Err.Number, "ListReports", Err.Description
Resume End_ListReports
End If

End Function

 

[Douglas J. Steele]

Oops, watch for word-wrap.

The lines

ListReports =
CurrentDb().Containers("Reports").Documents(lngRow).Name

should be a single line of text, as should

ListReports =
CurrentDb().Containers("Reports").Documents(lngRow).Properties("Description")--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in messageuse the following function. (You set the RowSourceType property toListReports, and leave the RowSource property blank)>> Function ListReports( _> lstListbox As Control, _> varID As Variant, _> lngRow As Long, _> lngCol As Long, _> intCode As Integer _> ) As Variant>> On Error GoTo Err_ListReports>> Select Case intCode> Case acLBInitialize> ListReports = True> Case acLBOpen> ListReports = Timer> Case acLBGetRowCount> ListReports = CurrentDb().Containers("Reports").Documents.Count> Case acLBGetColumnCount> ListReports = 2> Case acLBGetColumnWidth> Select Case lngCol> Case 0> ListReports = 2800> Case 1> ListReports = 2800> Case Else> ListReports = 0> End Select> Case acLBGetValue> Select Case lngCol> Case 0> ListReports =CurrentDb().Containers("Reports").Documents(lngRow).Name> Case 1> ListReports =CurrentDb().Containers("Reports").Documents(lngRow).Properties("Description")> Case Else> ListReports = vbNullString> End Select> End Select>> End_ListReports:> Exit Function>> Err_ListReports:> ' Error 3270: Property not found.> ' This means that there isn't a description associated with the report.> ' In that case, we want to return an empty string and continue.> If Err.Number = 3270 Then> ListReports = vbNullString> Resume Next> Else> Err.Raise Err.Number, "ListReports", Err.Description> Resume End_ListReports> End If>> End Function>>>> --> Doug Steele, Microsoft Access MVP> http://I.Am/DougSteele> (no e-mails, please!)>>> "David Newmarch" <[email protected]> wrote inmessage Thanks Jerry.>>>> This successfully gives me a listbox listing all the reports by name, but>> how would I extend the sql query to include a column that gives thereport>> Captions as well? The report names in my database are naming conventionnames>> like "rptProposalsByCategory" and I'd like to show the captions insteadwhich>> are in plain English.>>>> "Jerry Whittle" wrote:>>>>> SELECT MSysObjects.Name AS ReportName>>> FROM MSysObjects>>> WHERE (((MSysObjects.Type)=-32764))>>> ORDER BY MSysObjects.Name;>>> -->>> Jerry Whittle, Microsoft Access MVP>>> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.>>>>>> "David Newmarch" wrote:>>>>>> > In a database which contains a variety of possible reports is there away to>>> > create something like a list box (on a form) showing an updated listof all>>> > available reports from which the user can select which report to view?>>> >>>> > I really want something equivalent to a kind of scaled down version ofthe>>> > Database Window, without actually having to allow the user to view the>>> > Database Window itself, and I can't quite figure out how to approachthis.>>> >>>> > Is there a way to have a field in a table or query that is populatedby all>>> > available ReportNames (or ReportCaptions, better still)?>>

 

[Guest]

Thanks Douglas

The code works perfectly and gives me a listbox listing all the reports by
name, but is there a way to extend the code to include a column in the list
box that gives the report Captions as well, so that the reports are listed in
plain English?

 

[Douglas J. Steele]

Sorry, I should have been more explicit. That code assumes that each report
has a Description assigned to it, and it shows the Description.

Showing the Caption of the report is doable, but would be very slow. Under
the covers, you'd have to open each report (preferably in Design mode and
hidden), retrieve its caption, then close the report.

If you want code to take the Caption for each of your existing reports and
set it as the Description, something like the following untested air-code
should work:

Sub ReportCaptions()
On Error GoTo Err_ReportCaptions

Dim dbCurr As DAO.Database
Dim docCurr As Document
Dim rptCurr As Report
Dim prpNew As DAO.Property
Dim strCaption As String

Set dbCurr = CurrentDb
For Each docCurr In dbCurr.Containers("Reports").Documents
DoCmd.OpenReport docCurr.Name, acViewDesign
Set rptCurr = Application.Reports(docCurr.Name)
strCaption = rptCurr.Caption
If Len(strCaption) > 0 Then
rptCurr.Properties("Description") = strCaption
End If
DoCmd.Close acReport, docCurr.Name, acSaveNo
Next docCurr

End_ReportCaptions:
Exit Sub

Err_ReportCaptions:
Select Case Err.Number
Case 3270
Set prpNew = docCurr.CreateProperty( _
"Description", dbText, strCaption)
docCurr.Properties.Append prpNew
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_ReportCaptions
End Select

End Sub

 

[Jeff Boyce]

David

An alternate approach to that offered by Jerry and Douglas would be to
create a new table listing your reports. You could use this approach to
include fields that hold a "user-friendly" title, a description, etc.

This does require that YOU maintain the list of available reports. One way
to simplify taking a report "out of service" would be to include a "OK to
Show?" field (Yes/No).

Your listbox on your form would then be a simple query against this new
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

A whole lot of good suggestions! Thanks very much.

I'm beginning to think that Jeff's suggestion about a separately maintained
table listing the reports might make more sense, since I suppose it could
well be that I don't want some reports included in the list. But having got
this far with Douglas's coding (I have now given each report a Description)
there is one more thing I'd like to get right. I now get two columns in the
list box, with the report name in the first and the report description in the
second. I want to set the column width for the list box to 0cm so that the
report name is hidden, leaving only the second column visible, showing the
Description. But I can't seem to hide the first column in the normal way by
setting the Column Widths property to 0cm. Does it have to be done in the
code somewhere?

 

[Douglas J. Steele]

See that section of code:

Case acLBGetColumnWidth
Select Case lngCol
Case 0
ListReports = 2800
Case 1
ListReports = 2800
Case Else
ListReports = 0
End Select

That overrides whatever you may have set through the Properties.

It's saying to make both columns almost 2 inches wide (widths in VBA are
measured in twips, where a twip is equal to 1/20 of a point, or 1/1440 of an
inch.)

Try

Case acLBGetColumnWidth
Select Case lngCol
Case 0
ListReports = 0
Case 1
ListReports = 2800
Case Else
ListReports = 0
End Select

Play with the 2800, too, if you're not happy with that width.

 

[Guest]

Thanks Douglas, I've now added twips to my repertoire, and the list box
displays just right. But my next difficulty is that I'm finding it difficult
to figure out the code for referring to the value selected in the text box. I
want that value (would it be a text value?) to be the report name for a
command button on the form to open and I'm not sure how to refer to that
value.

 

[Douglas J. Steele]

Is this a multi-select listbox? If so, you need to loop through the
ItemsSelected collection, and look at the 1st column of each select row.
Remember that the Column collection starts counting at 0, so the 1st column
is Column(0):

Dim varSelected As Variant

For Each varSelected In Me.MyListbox.ItemsSelected
Debug.Print Me.MyListbox.Column(0, varSelected)
next varSelected

 

[Guest]

Not multi-select. It's just for choosing a single report to open, and my real
problem is that I don't know enough VBA to get the code right.

Thanks for the code you have suggested, which will come in handy later on,
but I'd like to know how to tweak it for a single-selection list box.

 

[Douglas J. Steele]

What's the Bound Column of the listbox? You'd want it to be 1.

 

[Keith Wilby]

A whole lot of good suggestions! Thanks very much.

I'm beginning to think that Jeff's suggestion about a separately
maintained
table listing the reports might make more sense,

That's the method I use and you can also add fields to store data such as
opening arguments (for use in OpenArgs) and sorting order.

Keith.
www.keithwilby.com

 

[Douglas J. Steele]

That's the method I use and you can also add fields to store data such as
opening arguments (for use in OpenArgs) and sorting order.

Yeah, but you need to ensure that you remember to update the table whenever
you add new reports.

OTOH, it does have the advantage that you can easily exclude reports you
don't want to have in the list of available reports (such as reports which
exist solely to be used as subreports)