AllReports, AllForms: How can I list Record Source? 1

[suedemick]

There is probably a very simple way to do this, but I haven't discovered it yet. I would like to create a list which shows each report name followed by the report's record source, which I can copy from the Immediate window and paste into Word (same for all the forms in my database). I've got the code to create the list of forms and the list of reports, butthe problem is that the only properties that AllReports and AllForms expose are Application, Count, Item, and Parent. How do I get the record source for each item to appear in the list as well?

Sub AllReports()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject

    For Each obj In dbs.AllReports
            Debug.Print obj.Name   ' Print name of obj.
    Next obj
End Sub

Thanks for any help!
Sue

 

[jitter]

hmmm...does not look like a super easy one for this.

You might check out article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q128879

It talks about what you want to do. Below is also a basically the code they are talking about.


Dim db As DAO.Database
Dim doc As Document

Set db = CurrentDb

Application.Echo False

For Each doc In db.Containers("forms&quot.Documents
Debug.Print "Form name: ", doc.Name
DoCmd.OpenForm doc.Name, acDesign
Debug.Print "Record source: ", Forms(doc.Name).RecordSource
Debug.Print
DoCmd.Close acForm, doc.Name
Next

For Each doc In db.Containers("reports&quot.Documents
Debug.Print "Report name: ", doc.Name
DoCmd.openreport doc.Name, acDesign
Debug.Print "Record source: ", Forms(doc.Name).RecordSource
Debug.Print
DoCmd.Close acreport, doc.Name
Next

Application.Echo True



sort of a bummer. it looks like you have to open each form or report to grab that info. but it is painless enough in a loop.

hope this helps....

 

[TrojanRabbit]

Alternative code for basically the same method:

a)
'Open all forms; get name & recordsource of each and close
Dim i As Long
For i = 0 to CurrentProject.AllForms.Count - 1
DoCmd.OpenForm CurrentProject.AllForms(i).Name
Next i
While Forms.Count > 0
Debug.Print Forms(0).Name
Debug.Print Forms(0).RecordSource
Debug.Print
DoCmd.Close acForm, Forms(0).Name
Wend

b)
'Close all open forms; with each form, open, get name & recordsource, and close
Dim i As Long
While Forms.Count > 0
Debug.Close acForm, Forms(0)
Wend
For i = 0 to CurrentProject.AllForms.Count - 1
DoCmd.OpenForm CurrentProject.AllForms(i).Name
Debug.Print Forms(0).Name
Debug.Print Forms(0).RecordSource
Debug.Print
DoCmd.Close Forms(0).Name
Next i

 

[suedemick]

Thanks, Jitter and TrojanRabbit,

I tried the code for the list of forms and it worked, but the data exceeded the limits of the Immediate window and I only got the info on the last 66 forms(I have way over 100 forms-this is the front-end database that ate Los Angeles). Is there a way to bypass the Immediate window and go right to Word or Notepad?

Thanks for your help,
Sue

 

[TrojanRabbit]

In Tables, link to the Word/Notepad document. The dialog will take you through it. You may need to add one record for it to work. If you use Notepad with comma as a delimiter, just type the name, comma, then the recordsource. Once the link is established, you can use a recordset object to add the info to it. Another alternative is to create an Access table, and export it to Word/Notepad after updating it.

At the beginning of the code, add:
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(&quot;<tblname>&quot;, dbOpenDynaset)

What you use in place of Debug.Print depends on how you want to work it. For form name and recordsource, use whichever code you decided to use above. Here are some ideas:

a) Empty the table, then add all the info.
rst.MoveFirst
While Not rst.EOF
rst.Delete
rst.MoveNext
Wend
...
'in place of Debug.Print
rst.AddNew
rst!<field1> = <form name>
rst!<field2> = <form recordsource>
rst.Update

b) Add the info if it isn't already there.
'in place of Debug.Print
rst.FindFirst &quot;<field1> = '&quot; & <form name> & &quot;'&quot;
If rst.NoMatch Then
rst.AddNew
rst!<field1> = <form name>
rst!<field2> = <form recordsource>
rst.Update
End If

 

[jitter]

not a problem. open a file for output and the print the info to the file instead of debug window.


Public Sub test()
Dim db As DAO.Database
Dim doc As Document
Dim intFreeFile As Integer

intFreeFile = FreeFile

Open &quot;c:\test\ObjectList.txt&quot; For Output As #intFreeFile

Set db = CurrentDb

Application.Echo False

For Each doc In db.Containers(&quot;forms&quot.Documents
Print #intFreeFile, &quot;Form name: &quot;, doc.Name
DoCmd.OpenForm doc.Name, acDesign
Print #intFreeFile, &quot;Record source: &quot;, Forms(doc.Name).RecordSource
Print #intFreeFile,
DoCmd.Close acForm, doc.Name
Next

For Each doc In db.Containers(&quot;reports&quot.Documents
Print #intFreeFile, &quot;Report name: &quot;, doc.Name
DoCmd.OpenReport doc.Name, acDesign
Print #intFreeFile, &quot;Record source: &quot;, Reports(doc.Name).RecordSource
Print #intFreeFile,
DoCmd.Close acReport, doc.Name
Next

Application.Echo True

Close #intFreeFile

End Sub