Re: Best way to list all available reports?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"David Newmarch" <DavidNewmarch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6A55B0FB-DAC4-483F-A224-4591B732B681@xxxxxxxxxxxxxxxx
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" wrote:

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



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"David Newmarch" <DavidNewmarch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:409F114A-9AC8-4A5E-8A0A-06993D3C2CEA@xxxxxxxxxxxxxxxx
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.

"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
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)?






.

Relevant Pages

  • Re: Filll listbox with the Caption of all the reports in a database
    ... > I have a form with a listbox, which I want to populate with the Name of the ... > run the report. ... > but I have not been able to figure out get the Caption property assigned to ... Dim db As DAO.Database ...
    (microsoft.public.access.formscoding)
  • Re: Best way to list all available reports?
    ... but is there a way to extend the code to include a column in the list ... ListReports, and leave the RowSource property blank) ... Case acLBGetRowCount ... ' This means that there isn't a description associated with the report. ...
    (microsoft.public.access.gettingstarted)
  • Re: Best way to list all available reports?
    ... ItemsSelected collection, and look at the 1st column of each select row. ... Doug Steele, Microsoft Access MVP ... want that value to be the report name for a ... ListReports = 2800 ...
    (microsoft.public.access.gettingstarted)
  • Re: Filll listbox with the Caption of all the reports in a database
    ... and with the caption that is assigned to the ... >> The user will be able to select one or more reports from the listbox, ... >> run the report. ... > Dim db As DAO.Database ...
    (microsoft.public.access.formscoding)
  • Re: Best way to list all available reports?
    ... Thanks Douglas, I've now added twips to my repertoire, and the list box ... want that value to be the report name for a ... ListReports = 2800 ...
    (microsoft.public.access.gettingstarted)