Iterate Through Forms Collection To Get Recordset (1 Viewer)

[Dugantrain]

Iterate Through Forms Collection To Get Recordsources

Hi all,
I've been charged with redeveloping a huge (several 100+ tables, forms, etc.) database which was brought over from another company. I'm trying to iterate through the forms to get their RecordSources, but I can't quite get it. This doesn't seem to work as it doesn't recognize the RecordSource Property:

Public Function fFind_Stuff()
Dim frm As AccessObject
For Each frm In CurrentProject.AllForms
    Debug.Print frm.RecordSource
Next
End Function

Any help?

 

[Kodo]

if you go to tools, analyze, documentor..it will document the forms (and other objects) for you and I believe it includes the record sources as well.

 

[Dugantrain]

Thank you, Kodo, that does work; sort of. It's actually about 900 pages that I'd have to wade through; maybe there's a Sys table that holds this info?

 

[Kodo]

well, you can select only the data properties for the forms in the documentor (click on options, then uncheck all but the properties in the top three boxes. Then hit the properties button and deselect all but the data property).. it would reduce the page count significantly. Other than that, I don't know. Perhaps a more experienced member could answer this question better than I.

 

[Mile-O]

A suggestion although I've not tried it:

Public Function GetRecordSource()

    Dim strForms() As String
    Dim intForms As Integer
    Dim intCounter As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MSysObjects")

    rs.MoveFirst
    
    Do While Not rs.EOF
        If rs.Fields("Type") = -32768 Then
            ReDim Preserve strForms(intCounter)
            strForms(intCounter) = rs.Fields("Name")
            intCounter = intCounter + 1
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    db.Close

    For intCounter = LBound(strForms()) To UBound(strForms())
        DoCmd.OpenForm Forms(strForms(intCounter)), , , , , acHidden
        Debug.Print Forms(strForms(intCounter)).RecordSource
        DoCmd.Close acForm, Forms(strForms(intCounter))
    Next intCounter
    
    Set rs = Nothing
    Set db = Nothing

End Function

 

[Dugantrain]

I do thank you for the advice, I was able to get it down to about 50 pages. I would, however, like to build a small VBA function so that, whenever I need to, I could just pass in a tabledef or querydef argument and return all the forms that use this object as a RecordSource. If anyone has any experience with this, it'd be much appreciated.

 

[dcx693]

I'm pretty sure a form needs to be open before you can get at it's RecordSource property, so you could open each form and then print out the RecordSource then close it. Just be aware that as each form opens, some form code may run (such as in the Open and Load events).

 

[Dugantrain]

Mile, I see what you're doing here, I could probably alter to suit. Thanks all.
*edit* although this function as well as DCX's advice would entail opening and closing the form which would be dangerous as I really don't know what's going on here. But, you know, if it can't be done, it can't be done.

 

[Mile-O]

I've done it.

Public Function GetRecordSource()

    Dim strForms() As String
    Dim strName As String
    Dim intForms As Integer
    Dim intCounter As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MSysObjects")

    rs.MoveFirst
    
    Do While Not rs.EOF
        If rs.Fields("Type") = -32768 Then
            ReDim Preserve strForms(intCounter)
            strForms(intCounter) = rs.Fields("Name")
            intCounter = intCounter + 1
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    db.Close

    For intCounter = LBound(strForms()) To UBound(strForms())
        strName = strForms(intCounter)
        DoCmd.OpenForm strName, , , , , acHidden
        Debug.Print Forms(strName).RecordSource
        DoCmd.Close acForm, strName
    Next intCounter
    
    Set rs = Nothing
    Set db = Nothing

End Function

 

[Dugantrain]

*edit* although this function as well as DCX's advice would entail opening and closing the form which would be dangerous as I really don't know what's going on here. But, you know, if it can't be done, it can't be done.

Wait, I can just open it in Design View! Yeah, that works. Thanks again.

 

[elpenguino]

Thanks Mile-O, your expertise helped me a lot.
Coupled with 'open in design view', it is "mission accomplished".