locked
Is there a way to find all Forms and Reports - Has module Yes? RRS feed

  • Question

  • Hi

    Is there a way to find all Forms and Reports - Has module Yes?

    Like a list.


    Cheers // Peter Forss Stockholm

    Friday, August 28, 2020 6:40 PM

Answers

  • I was able to make a list of reports with modules using the following code. First, create a table named HasModuleList with fields ID, ReportName and FormName. You should be able to do the same thing with forms.

    Dim db as Database

    Set db = CurrentDb
    Dim ModuleListTable As Recordset
    Dim ModuleFilter As String

    Dim OpenReport As Report
    Dim ActiveReport As Access.AccessObject

    ModuleFilter = "SELECT DISTINCTROW HasModuleList.ID, HasModuleList.ReportName, HasModuleList.FormName FROM HasModuleList;"
    Set ModuleListTable = db.OpenRecordset(ModuleFilter)
    With ModuleListTable
        For Each ActiveReport In CurrentProject.AllReports
            With ActiveReport
                    DoCmd.OpenReport .Name, acViewDesign, , , acWindowNormal
                    Set OpenReport = Screen.ActiveReport
                    'msgbox OpenReport.HasModule
                    If OpenReport.HasModule = False Then
                        DoCmd.Close acReport, .Name, acSaveNo
                    Else
                        ModuleListTable.AddNew
                        ModuleListTable.Fields("ReportName") = OpenReport.Name
                        ModuleListTable.Update
                        DoCmd.Close acReport, .Name, acSaveNo
                    End If
            End With
            'Exit Function
        Next
    .Close
    End With
    Exit Function
    End Function

    • Marked as answer by ForssPeterNova Saturday, August 29, 2020 6:03 AM
    Friday, August 28, 2020 8:38 PM

All replies

  • You can use the code below to open all reports or forms in design mode, check if it has a module, then if it doesn't, the form or report will close. So you will have tabs of open reports and forms that have modules. Now, the trick is to create a list of the open reports or forms. Once the report or form is open, you might be able to then put its Name in a table. I'll need to think about that. Maybe someone else has an idea.

    Public Function AllForms()
    Dim OpenReport As Form
    Dim ActiveForm As Access.AccessObject
    For Each ActiveForm In CurrentProject.AllForms
        With ActiveReport
                DoCmd.OpenForm .Name, acViewDesign, , , acWindowNormal
                Set OpenForm = Screen.ActiveForm
                'msgbox OpenForm.HasModule
                If OpenForm.HasModule = False Then
                    DoCmd.Close acForm, .Name, acSaveNo
                End If
        End With
    Next
    Exit Function
    End Function

    Public Function AllReports()
    Dim OpenReport As Report
    Dim ActiveReport As Access.AccessObject
    For Each ActiveReport In CurrentProject.AllReports
        With ActiveReport
                DoCmd.OpenReport .Name, acViewDesign, , , acWindowNormal
                Set OpenReport = Screen.ActiveReport
                'msgbox OpenReport.HasModule
                If OpenReport.HasModule = False Then
                    DoCmd.Close acReport, .Name, acSaveNo
                End If
        End With
    Next
    Exit Function
    End Function

    Friday, August 28, 2020 7:50 PM
  • I was able to make a list of reports with modules using the following code. First, create a table named HasModuleList with fields ID, ReportName and FormName. You should be able to do the same thing with forms.

    Dim db as Database

    Set db = CurrentDb
    Dim ModuleListTable As Recordset
    Dim ModuleFilter As String

    Dim OpenReport As Report
    Dim ActiveReport As Access.AccessObject

    ModuleFilter = "SELECT DISTINCTROW HasModuleList.ID, HasModuleList.ReportName, HasModuleList.FormName FROM HasModuleList;"
    Set ModuleListTable = db.OpenRecordset(ModuleFilter)
    With ModuleListTable
        For Each ActiveReport In CurrentProject.AllReports
            With ActiveReport
                    DoCmd.OpenReport .Name, acViewDesign, , , acWindowNormal
                    Set OpenReport = Screen.ActiveReport
                    'msgbox OpenReport.HasModule
                    If OpenReport.HasModule = False Then
                        DoCmd.Close acReport, .Name, acSaveNo
                    Else
                        ModuleListTable.AddNew
                        ModuleListTable.Fields("ReportName") = OpenReport.Name
                        ModuleListTable.Update
                        DoCmd.Close acReport, .Name, acSaveNo
                    End If
            End With
            'Exit Function
        Next
    .Close
    End With
    Exit Function
    End Function

    • Marked as answer by ForssPeterNova Saturday, August 29, 2020 6:03 AM
    Friday, August 28, 2020 8:38 PM
  • Hi Lawrence

    Thank you very, very much for this code.

    Great help. Gave me the list I wanted.


    Cheers // Peter Forss Stockholm

    Saturday, August 29, 2020 6:03 AM
  • Gave me the list I wanted.

    Hi Peter,

    Just for curiousity, what is your next step with the list?

    Imb.

    Saturday, August 29, 2020 5:00 PM
  • No charge for a fellow Scandinavian. But be careful with this property. As I read it, if you have VBA code behind a form or report and you set the Has Module to False, you will delete all the code for that form or report. So maybe create a test form and report, insert some code and test it.
    • Proposed as answer by isladogs52 Saturday, August 29, 2020 9:06 PM
    Saturday, August 29, 2020 5:25 PM
  • Hi Imb

    I have, or have had, some mysterious errors in an application. Once, update queries were run without the user having access to these very queries (set all balances to 1000). Twice, update queries, which by condition can only be run once per record, have added double lines.

    I began to suspect that I had exceeded the limit of 1000 modules (including Forms and Reports that has modules) Therefore, I was looking for a way to quickly examine how many forms and reports have modules. The "clean" modules are easily included in the Navigation pane,

    And I have not reached the limit yet.



    Cheers // Peter Forss Stockholm

    Saturday, August 29, 2020 6:10 PM
  • That is indeed what happens.  If you switch an object to HasModule False, it deletes any code behind it.

    I actually add blank modules to objects as a means to stop people being able to copy them without having the VBA Project Password.  Just an extra layer of protection.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, August 29, 2020 6:12 PM
  • Hi Lawrence

    Thanks again. But I have not lost any code as I can see or find by testing the application.

    What row deletes all the code?


    Cheers // Peter Forss Stockholm

    Saturday, August 29, 2020 6:13 PM
  • Peter:

    I wasn't referring to a row of code that I developed for you. I was referring to what the Has Module documentation states. Apparently, if you have code behind a form or report (the Has Module property is set to True) and you manually or with VBA code, set the Has Module property to False, all the code associated with the form or report disappears. 


    Saturday, August 29, 2020 7:10 PM
  • I have, or have had, some mysterious errors in an application. Once, update queries were run without the user having access to these very queries (set all balances to 1000). Twice, update queries, which by condition can only be run once per record, have added double lines.

    Hi Peter,

    Would the limit of the number of modules be the reason for the errors? I expect more some - undiscovered - flaw in the whole process.

    I just removed my almost last form with a module. Forms in my systematics are "just" a bunch of routines working on an generalized form.

    Imb.

    Saturday, August 29, 2020 8:27 PM
  • Hi Daniel

    Blank modules. 

    Can you have as many as you want (the 1000 module limit)

    Are they completly blank?


    Cheers // Peter Forss Stockholm


    Sunday, August 30, 2020 5:16 PM
  • The limit still applies.

    They are blank with the exception of

    Option Compare Database and Option Explicit


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, August 30, 2020 5:42 PM