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

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 StringDim OpenReport As Report
Dim ActiveReport As Access.AccessObjectModuleFilter = "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 StringDim OpenReport As Report
Dim ActiveReport As Access.AccessObjectModuleFilter = "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.netSaturday, 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.
- Edited by Lawrence Ellefson Saturday, August 29, 2020 7:59 PM
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
- Edited by ForssPeterNova Monday, August 31, 2020 5:15 AM
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.netSunday, August 30, 2020 5:42 PM