CODEWITHSUNDEEP
ms-accessvbams-access-2013
Vexen Crabtree
Vexen Crabtree

Reputation: 359

Run-time error 7961 when referring to Modules(name) for Form modules

The Modules() collection contains all open modules, including the modules that sit behind forms. But I don't know how to refer to the modules that sit behind forms. In the code below (from the Immediate pane), the naming convention is that objects starting with "M_" are a standalone module, and objects starting with "F_" are forms.

From the Immediate pane:

'Proving that the method works for standalone modules:
debug.print Modules("M_Test").Name
M_Test

' Proving I am spelling the name of the form correctly:
debug.Print currentproject.AllForms("F_Inserts").Name
F_Inserts

' Proving the form is open (so it appears in Modules() collection)
debug.Print currentproject.AllForms("F_Inserts").IsLoaded
True

Good. Try to access the module behind the form - don't forget, all forms' modules are prepended by "Form_" in their name:

debug.Print Modules("Form_F_Inserts").Name
debug.Print Modules("F_Inserts").Name

Results in Run-time error 7961 both times - "can't find the module 'Form_F_Inserts' referred to in a macro expression or Visual Basic code". And pretty much as always nowadays, the "Help" button is broken and takes me to a "Unable to Service Request" error page (https://msdn.microsoft.com/Areas/Epx/Content/500.aspx?aspxerrorpath=/query/dev11.query).

The purpose of the routine is to get .CountOfLines for each form module. Here's the whole thing:

Dim lOut As String, lVariant As Variant, lInt As Integer, lBool As Boolean
lInt=0
For Each lVariant In CurrentProject.AllForms
    lBool = lVariant.IsLoaded   'If not loaded, we open it & close it after
    If Not lBool Then DoCmd.OpenForm lVariant.Name, acDesign, WindowMode:=acHidden
    If Forms(lVariant.Name).HasModule Then lInt = lInt + Modules("Form_" & lVariant.Name).CountOfLines
    If Not lBool Then DoCmd.Close acForm, lVariant.Name, acSaveNo
Next

Any ideas?

Upvotes: 1

Views: 278

Answers (1)

HansUp
HansUp

Reputation: 97101

The Modules() collection contains all open modules, including the modules that sit behind forms.

So assuming your F_Inserts form is open, you can reference its Module property and get CountOfLines from there.

'Debug.Print Modules("Form_F_Inserts").Name     ' fail
'Debug.Print Modules("F_Inserts").Name          ' fail
Debug.Print Forms!F_Inserts.Module.Name         '<-- this should return "Form_F_Inserts"
Debug.Print Forms!F_Inserts.Module.CountOfLines '<-- this should work, too

However if you want to retrieve CountOfLines for the module of a from which is not open, use the VBE object model with the form's module name (Form_F_Inserts)...

With Application.VBE.ActiveVBProject
    Debug.Print .VBComponents("Form_F_Inserts").CodeModule.CountOfLines
End With

If you want CountOfLines for each form module, perhaps this is sufficient ...

Dim objComponent As Object ' VBComponent
Dim strName As String

For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
    strName = objComponent.Name
    If strName Like "Form_*" Then
        Debug.Print strName, objComponent.CodeModule.CountOfLines
    End If
Next

Upvotes: 3

Related Questions