Solved

List Access From Modules

Posted on 2014-10-02
15
313 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
  • 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 49

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
 

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 49

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now