List Access From Modules

Posted on 2014-10-02
Last Modified: 2014-10-17
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.
Question by:thebrepp
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +4
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40357029
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

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40357035
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.

LVL 50

Expert Comment

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

? Forms(n).HasModule

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 40357040
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.
LVL 50

Expert Comment

by:Gustav Brock
ID: 40357043
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.


Author Comment

ID: 40357048
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.
LVL 58
ID: 40357057
<<List all modules does not list form modules. >>

 Re-read my comment please.

LVL 38

Expert Comment

ID: 40357262
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.

Author Comment

ID: 40357391
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.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 166 total points
ID: 40357590
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 ?


Author Comment

ID: 40357641
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.

Accepted Solution

wayne_phillips earned 167 total points
ID: 40357662
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).

Author Closing Comment

ID: 40385166
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.
LVL 38

Expert Comment

ID: 40385297
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


Author Comment

ID: 40386517
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.  

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question