Referencing reports to underlying queries (and vice versa)

  • Thread starter Thread starter Colin Gordon
  • Start date Start date
C

Colin Gordon

I have a form that, when a button is pressed, either lists all reports or
queries within my database in a combo box. If a report name is selected I
want to be able to display in a list box the underlying query (name); if a
query is selected I want to be able to display all reports (names)that may
use the selected query in the list box. I've been banging my head against my
desk for over a week now trying to figure this out (using report/query
objects etc.) and then thought I'd cracked it with containers (except that
objects need to be open for them to be in the container, which is
unacceptable for my purposes). Does anyone know how this can be done?
Ideally (if it can be done) I'd like to take this further and be able to
list tables used in a query too. All help/suggestions gratefully received
with thanks!
 
Colin:

Below is some code from a form that uses two combo boxes, one for reports
(cmbReports) and one for queries (cmbQueries). I used a textbox to show the
report record source (txtRS), and a listbox for displaying reports that have
the query as a record source. I used the change event for each combo box
rather than a button. You will need to set the Row Source Type property of
the listbox to "Value List."


Private Sub cmbQueries_Change()
Dim rpt As AccessObject
Dim iCount As Integer
Dim i As Integer

iCount = lstReports.ListCount
For i = 0 To iCount - 1
lstReports.RemoveItem 0
Next i

For Each rpt In CurrentProject.AllReports
DoCmd.OpenReport rpt.Name, acViewDesign, , , acHidden
If Reports(rpt.Name).RecordSource = cmbQueries Then
lstReports.AddItem rpt.Name
End If
DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt

Set rpt = Nothing

End Sub

Private Sub cmbReports_Change()
DoCmd.OpenReport cmbReports, acViewDesign, , , acHidden
txtRS = Reports(cmbReports).RecordSource
DoCmd.Close acReport, cmbReports, acSaveNo

End Sub

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a form that, when a button is pressed, either lists all reports or
queries within my database in a combo box. If a report name is selected I
want to be able to display in a list box the underlying query (name); if a
query is selected I want to be able to display all reports (names)that may
use the selected query in the list box. I've been banging my head against my
desk for over a week now trying to figure this out (using report/query
objects etc.) and then thought I'd cracked it with containers (except that
objects need to be open for them to be in the container, which is
unacceptable for my purposes). Does anyone know how this can be done?
Ideally (if it can be done) I'd like to take this further and be able to
list tables used in a query too. All help/suggestions gratefully received
with thanks!
 
Colin:

Below is a way to do this without opening the underlying objects. For the
report record source the following code only looks at queries, which is what
you specified. You could easily extend this to iterate through the
TableDefs collection to capture any reports that use a table as the record
source. Reports that have an SQL statement as the record source will not
return a value, of course.

Private Sub cmbQueries_Change()
Dim rpt As AccessObject
Dim iCount As Integer
Dim i As Integer

iCount = lstReports.ListCount
For i = 0 To iCount - 1
lstReports.RemoveItem 0
Next i

For Each rpt In CurrentProject.AllReports
If rpt.IsDependentUpon(acQuery, cmbQueries) Then
lstReports.AddItem rpt.Name
End If
Next rpt

Set rpt = Nothing

End Sub

Private Sub cmbReports_Change()
Dim rpt As AccessObject
Dim di As DependencyInfo
Dim qdf As QueryDef

txtRS = ""

Set rpt = CurrentProject.AllReports(cmbReports)
For Each qdf In CurrentDb.QueryDefs
If rpt.IsDependentUpon(acQuery, qdf.Name) Then
txtRS = qdf.Name
End If
Next qdf

End Sub


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a form that, when a button is pressed, either lists all reports or
queries within my database in a combo box. If a report name is selected I
want to be able to display in a list box the underlying query (name); if a
query is selected I want to be able to display all reports (names)that may
use the selected query in the list box. I've been banging my head against my
desk for over a week now trying to figure this out (using report/query
objects etc.) and then thought I'd cracked it with containers (except that
objects need to be open for them to be in the container, which is
unacceptable for my purposes). Does anyone know how this can be done?
Ideally (if it can be done) I'd like to take this further and be able to
list tables used in a query too. All help/suggestions gratefully received
with thanks!
 
Back
Top