How to enumerate Reports in an ADP?

[Richard Schaefer]

We're creating an ADP for an end-user to use to create ad-hoc reports against
a small SQL DB. We're hiding all the usual tools in favor of our own
"dashboard". We've been able to build queries and build reports from the
dashboard, but can't figure out how to present a list of existing reports. We
have some code that works in an MDB but it doesn't work in the ADP.

Any ideas?

 

[Sylvain Lafontaine]

Not sure what you mean exactly by « We have some code that works in an MDB
but it doesn't work in the ADP ».

Do you mean that you have build a dashboard for displaying a list of
reports - along with their parameters - for previewing / printing them but
that this dashboard doesn't work when you try to use it with an ADP project?

 

[Richard Schaefer]

When we couldn't figure out how to list the existing reports that had already
been built, saved and closed in the ADP we tried it in a matching MDB. We
were able to find the reports in the MDB, but the same code doesn't work in
the ADP. I don't have the exact code and the developer is gone for the day. I
can post code tomorrow, but if you know of an Access object or "system table"
that contains a collection of saved reports that you can point me to we might
be able to figure it out from there.

 

[Sylvain Lafontaine]

Your best option would be to create a table with the name of reports. Not
only this gave you the possibility of displaying them with a friendly name
but gave you an easy way to implement a lot of other functionalities:
displaying only a list of useful reports, restrictions, grouping (printing
multiple reports with a single launch), list of relevant controls, etc.

However, you can also enumerate the report in an ADP project by looking at
the AllReports collection. Here an example that I've used last year to make
sure that the Record Source Qualifier of all reports was set to dbo:


Public Function ADP_Rapports_RecordSourceQualifier_dbo()

Dim bASauvegarder As Boolean
bASauvegarder = False

Dim i As Integer
i = 0

Dim objDAP As AccessObject

Dim f As Report
Dim c As Control
Dim ct As Long ' Pour ControlType.
Dim s As String

'''' For Each objDAP In CurrentProject.AllForms

For Each objDAP In CurrentProject.AllReports

'''' Debug.Print "The '" & objDAP.name & "' is located at: " &
objDAP.FullName

If (True) Then
i = i + 1
DoCmd.OpenReport objDAP.name, acDesign

Set f = Reports(objDAP.name)
Debug.Print Now() & ": " & objDAP.name

If ((f.RecordSource & "") <> "") Then
If (f.RecordSourceQualifier <> "dbo") Then
bASauvegarder = True
f.RecordSourceQualifier = "dbo"
' Debug.Print Now() & ": " & objDAP.name & ": Modifiée"
End If
End If

For Each c In f.Controls
' Debug.Print ctrl.Name
' If (TypeOf ctrl Is TextBox) Then

ct = c.ControlType

' 1) Liste des objets pouvant avoir un ControlSource.

' Note: les acOptionButton peuvent également avoir un
RecordSource
' si leurs parents n'est pas un acOptionGroup.

If (False And ct = acCheckBox Or _
ct = acComboBox Or _
ct = acListBox Or _
ct = acOptionGroup Or _
ct = acTextBox Or _
ct = acToggleButton Or _
ct = acBoundObjectFrame) Then

If (False) Then
bASauvegarder = True
End If
End If

' 1) Liste des objets pouvant avoir un RowSource.

If (ct = acComboBox Or ct = acListBox) Then

' Debug.Print Now() & ": " & c.RowSource
' Debug.Print Now() & ": " & c.RowSourceType

If (c.RowSourceType & "" = "Table/View/StoredProc") Then
s = c.RowSource & ""

If (Left(s, 4) <> "dbo.") Then
c.RowSource = "dbo." & s
bASauvegarder = True
End If
End If
End If
Next

If (bASauvegarder = True) Then
bASauvegarder = False
DoCmd.Close acReport, objDAP.name, acSaveYes
Else
DoCmd.Close acReport, objDAP.name, acSaveNo
End If
End If

Next objDAP

End Function