Solved

List Access From Modules

Posted on 2014-10-02
15
319 Views
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.
0
Comment
Question by:thebrepp
[X]
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
15 Comments
 
LVL 47

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
Next

Open in new window

0
 
LVL 57

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 dbs As DAO.DATABASE
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"

      
 and:
        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.

Jim.
0
 
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

/gustav
0
Technology Partners: 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!

 

Author Comment

by:thebrepp
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.
0
 
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.

/gustav
0
 

Author Comment

by:thebrepp
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.
0
 
LVL 57
ID: 40357057
<<List all modules does not list form modules. >>

 Re-read my comment please.

Jim.
0
 
LVL 36

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:thebrepp
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.
0
 
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 http://www.everythingaccess.com/accessdatabaserepair_info.asp 

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

mx
0
 

Author Comment

by:thebrepp
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.
0
 
LVL 1

Accepted Solution

by:
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).
0
 

Author Closing Comment

by:thebrepp
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.
0
 
LVL 36

Expert Comment

by:PatHartman
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)
    Else
        strPath = strPath & "A"
        If Not objFSO.FolderExists(strPath) Then
            objFSO.CreateFolder (strPath)
        Else
            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
    
ExitProc:
    Exit Sub
ErrProc:
    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\
    Else
        sExportLocation = ExpLoc
    End If
    If Right(sExportLocation, 1) = "\" Then
    Else
        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_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

Open in new window

0
 

Author Comment

by:thebrepp
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.  
Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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