List Access From Modules

Looking for VBA to list all Form Modules in an Access 2003 MDB.
Reason: I believe there is an orphaned form module tied to a deleted form as it trashes the mdb occasionally when working in any module, compile or decompile. Normal corruption fix routines do not work.  Also copying all objects into a blank mdb crashes too.
I want to compare a list of forms vs all form modules to find the orphan, recreate the form and then delete it hoping it takes the form module with it.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Can't you just see them in the VB window?  At any rate, here is the code to iterate through the Forms containers and identify all of the form modules
Dim intLoop as integer

For intLoop = 0 to currentdb.containers("Forms").documents.count
    debug.print currentdb.containsers("Forms").documents(intLoop).Name

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There is no way to return that.  The modules behind forms are attached to the forms themselves.

You can get a listing of all standard and class modules like this:

Public Sub ListModules()

Dim ctr As DAO.Container
Dim doc As DAO.Document
Set dbs = CurrentDb()

    ' Enumerate Containers collection.
    Set ctr = dbs.Containers("Modules")
    For Each doc In ctr.Documents
        Debug.Print doc.Name
    Next doc
End Sub

 But that won't list the report or form class modules.

 My suggestion would be to build a clean DB, but not importing the forms.   Then use the un-documented SaveAsText and LoadFromText to move the forms to the new DB.

 If the code attached to them is good, it will come along with it in the file.

 Looks like this:

        Application.SaveAsText acForm, strFormName, strExportTo & "Form_" & strFormName & ".txt"

        Application.LoadFromText acForm, "<form name>", "C:\Temp\Form_<form name>.txt"

  You can use the ListModules above and modify it to list all the forms and save them using this method (just use "Forms" in place of "Modules" for the container.

Gustav BrockCIOCommented:
You need to open the forms (one by one, can be in designmode), then check the property HasModule:

? Forms(n).HasModule

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

thebreppAuthor Commented:
Thanks for the comment. The form is deleted so wont show up this way.  I needed a way to list all forms modules whether there is a form or not.  I have a list of forms already.
This is a unique issue although I'm not the only one it has happened too.  VBA gets trashed due to an orphaned form module but I do not know the form name as it has been deleted.
Gustav BrockCIOCommented:
If you create a new database and import all known objects from the trashed database, I cannot see how an orphaned form module should be included.

thebreppAuthor Commented:
List all modules does not list form modules.  While they are "attached" to a form I believe they are stored separately.  I think I have an orphaned one based on the errors I receive plus some google searches on same.  Is there a way to list form modules without first using a forms collection since form has been deleted.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<List all modules does not list form modules. >>

 Re-read my comment please.

Why do you think you have an orphaned form module?  What symptoms are making you think this is the problem?  Form/Report modules are Class modules and are different from standard modules.  The VB editor lists them separately and Access handles them separately.
thebreppAuthor Commented:
Searches related to the following errors that can occur on compile, save or decompile actions:
Access errors on compile      
The search key was not found in any record.
Module Not Found

On Save: The search key was not found in any record.  The Save operation failed.

All standard attempts such as pull into new mdb or compact and repair fail to resolve problem.  Even used some of the commercial fix programs.  One of my searches indicated orphaned form module and fix was to create that form again.  Problem is I do not know the form.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If anyone can fix this issue, it will  be Wayne at 

Ping him and see what he says.
Note that I gather you did try Decompile, correct ?

thebreppAuthor Commented:
Thanks for the tip.  I tired everything under the planet.  Been with Access dev since 97 version and never ran in to this one before.
Thanks for recommending me, Joe.

I'm not convinced you're on the right track with regards to it being an orphan module.  Anyhow, you can read the list of modules (including form/report class modules) as VBA sees it, through the VBE.ActiveVBProject.VBComponents collection.  This collection is a separately maintained list to the collections Access itself provides, so if there really is an orphan you should be able to identify it by matching up from that list (i.e. ensuring each form/report module in the VBA collection has a real entry in the corresponding Access collections).

If you'd like me to take a look, get in touch through the link Joe provided above (go through the Contact Us link).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thebreppAuthor Commented:
Still have problem and have gone back to a prior version.  I need to save as text next as time permits to get changes since older version from bad version.  it keeps bombing out though after working with it a bit.  This keeps happening.  I can go in fresh copy but after doing a few things I must exit else it corrupts further to a point where you cannot dev anything.  "System Resource Exceeded" message tells me I waited too long.
This is code I had to put in one application that was particularly troublesome.  If I was the user signed in, it prompted me when the hidden login form closed.  When the database opened I had people log on and then hid the login form rather than closing it so I would have an anchor for shut down processing.  There was some corruption that none of the common solutions ever got rid of.  In desperation, I saved everything when I closed the database less it fail to open on me next time.  The app would seem to be functioning properly but after closing and reopening would become unreadable.

Private Sub Form_Unload(Cancel As Integer)
    If Environ("username") = "phartman" Or Environ("username") = "Pat" Then
        If MsgBox("Save as text?", vbYesNo) = vbYes Then
            Call ExportOnClose
        End If
    End If
End Sub

Public Sub ExportOnClose()
    Dim strPath As String
    Dim objFSO As Object
    Dim strMsg As String

On Error GoTo ErrProc
    'create new folder
    strPath = "C:\Pat\RAS\TextFiles" & Format(Date, "yymmdd")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If Not objFSO.FolderExists(strPath) Then
        objFSO.CreateFolder (strPath)
        strPath = strPath & "A"
        If Not objFSO.FolderExists(strPath) Then
            objFSO.CreateFolder (strPath)
            strPath = InputBox("enter path please")
            objFSO.CreateFolder (strPath)
        End If
    End If
    'export all objects
    MsgBox "This may take a few minutes.  Please wait for the count message.", vbOKOnly
    Call ExportDatabaseObjects("forms", strPath)
    Call ExportDatabaseObjects("reports", strPath)
    Call ExportDatabaseObjects("modules", strPath)
    Call ExportDatabaseObjects("QueryDefs", strPath)
    Call ExportDatabaseObjects("scripts", strPath)
    strMsg = "Exported Forms = " & iCountForms & vbCrLf
    strMsg = strMsg & "         Reports = " & iCountReports & vbCrLf
    strMsg = strMsg & "         Modules = " & iCountModules & vbCrLf
    strMsg = strMsg & "         Queries = " & iCountQueries & vbCrLf
    strMsg = strMsg & "         Macros = " & iCountScripts & vbCrLf
    Debug.Print strMsg
    MsgBox strMsg, vbOKOnly
    Exit Sub
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
    End Select
End Sub

Public Sub ExportDatabaseObjects(ExportType As String, Optional ExpLoc As Variant)
On Error GoTo Err_ExportDatabaseObjects
    'Dim db As Database
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim D As Document
    Dim C As Container
    Dim i As Integer
    Dim sExportLocation As String
    Set db = CurrentDb()
''import from text =
''application.Application.LoadFromText acForm, "frmRisks","C:\Temp\TextRiskReview070615\Form_frmRisks.txt"

    If ExpLoc & "" = "" Then
        sExportLocation = "C:\Pat\RAS\TextFiles\" 'Do not forget the closing back slash! ie: C:\Temp\
        sExportLocation = ExpLoc
    End If
    If Right(sExportLocation, 1) = "\" Then
        sExportLocation = sExportLocation & "\"
    End If
    Select Case ExportType
        Case "TableDefs"
            For Each td In db.TableDefs 'Tables
                If Left(td.Name, 4) <> "MSys" Then
                    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
                End If
            Next td
        Case "Forms"
            Set C = db.Containers("Forms")
            iCountForms = 0
            For Each D In C.Documents
                Application.SaveAsText acForm, D.Name, sExportLocation & "Form_" & D.Name & ".txt"
                iCountForms = iCountForms + 1
            Next D
        Case "Reports"
            Set C = db.Containers("Reports")
            iCountReports = 0
            For Each D In C.Documents
                Application.SaveAsText acReport, D.Name, sExportLocation & "Report_" & D.Name & ".txt"
                iCountReports = iCountReports + 1
            Next D
        Case "Scripts"
            Set C = db.Containers("Scripts")
            iCountScripts = 0
            For Each D In C.Documents
                Application.SaveAsText acMacro, D.Name, sExportLocation & "Macro_" & D.Name & ".txt"
                iCountScripts = iCountScripts + 1
            Next D
        Case "Modules"
            Set C = db.Containers("Modules")
            iCountModules = 0
            For Each D In C.Documents
                Application.SaveAsText acModule, D.Name, sExportLocation & "Module_" & D.Name & ".txt"
                iCountModules = iCountModules + 1
            Next D
        Case "QueryDefs"
            iCountQueries = 0
            For i = 0 To db.QueryDefs.Count - 1
                Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
                iCountQueries = iCountQueries + 1
            Next i
        Case Else
    End Select

    Set db = Nothing
    Set C = Nothing
    'MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    Exit Sub
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
End Sub

Open in new window

thebreppAuthor Commented:
This is not an exact fit but certainly something to hang on to. It appears Access can corrupt in now two ways unknown to me after many, many years of working with it.  My issue is VBA related and as you noted, there are times when the normal methods fail.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.