Solved Can I go directly to the module (1 Viewer)

[Superpat]

Hello,
I have this code who is good, but I don't know how to modify to go directy to my form (strNomModule) and modify this "For Each oComponent In Application.VBE.ActiveVBProject.VBComponents"

Function RechercheIntituleModForm(ByVal sSearchTerm As String, strNomModule As String)
        Dim oComponent As Object                                     'VBComponent
        For Each oComponent In Application.VBE.ActiveVBProject.VBComponents
                Debug.Print "Module IntituleModForm : " & oComponent.CodeModule
                If oComponent.CodeModule = strNomModule Then
                        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
                                Debug.Print "Module contient et vrai : " & oComponent.Name        'Name of the current module in which the term was found (at least once)
                                RechercheIntituleModForm = True
                        Else
                                Debug.Print "Module ne contient pas : " & oComponent.Name        'Name of the current module in which the term was not found (at least once)
                                RechercheIntituleModForm = False
                        End If
                        Exit For                                     'We know it exist so let leave, no point continuing
                End If
         Next oComponent
End Function

Thank to help

 

[moke123]

just a guess as I cant test, can you use the index?

For Each oComponent In Application.VBE.ActiveVBProject.VBComponents(strNomModule)

 

[Superpat]

just a guess as I cant test, can you use the index?

For Each oComponent In Application.VBE.ActiveVBProject.VBComponents(strNomModule)

Thanks, but it do not work. Error 438

 

[isladogs]

Do you have the Visual Basic Extensibility reference assigned in your project?

 

[Superpat]

Yes I have

 

[Gasman]

Can you have an index by the name of the form?

 

[isladogs]

It works fine for me.
I made a couple of cosmetic changes for testing purposes:

Function RechercheIntituleModForm(ByVal sSearchTerm As String, strNomModule As String)
        Dim oComponent As Object                                     'VBComponent
        For Each oComponent In Application.VBE.ActiveVBProject.VBComponents
              '  Debug.Print "Module IntituleModForm : " & oComponent.CodeModule
                If oComponent.CodeModule = strNomModule Then
                        Debug.Print "Module IntituleModForm : " & oComponent.CodeModule
                        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
                                Debug.Print "Module contient et vrai : " & oComponent.Name & " : " & sSearchTerm         'Name of the current module in which the term was found (at least once)
                                RechercheIntituleModForm = True
                        Else
                                Debug.Print "Module ne contient pas : " & oComponent.Name & " : " & sSearchTerm        'Name of the current module in which the term was not found (at least once)
                                RechercheIntituleModForm = False
                        End If
                        Debug.Print ""
                        Exit For                                     'We know it exist so let leave, no point continuing
                End If
         Next oComponent
End Function

Then tested it using

Sub TestSearch()
    RechercheIntituleModForm "ViewCode", "Form_frmMain"
    RechercheIntituleModForm "XKPWQ", "Form_frmMain"
End Sub

NOTE; you must use the name of the form code module "Form_YourFormName"

Results:

Module IntituleModForm : Form_frmMain
Module contient et vrai : Form_frmMain : ViewCode

Module IntituleModForm : Form_frmMain
Module ne contient pas : Form_frmMain : XKPWQ

 

[Superpat]

Can you have an index by the name of the form?

I do not know how to do, I have just the name of the form strNomModule. However, the loop works well, but reviews all the forms.

 

[Superpat]

Thanks @isladogs, it works fine to me also

 

[Josef P.]

Hi,
Are you looking for this:

set oComponent = Application.VBE.ActiveVBProject.VBComponents(strNomModule)

 

[Superpat]

Hi,
Are you looking for this:

set oComponent = Application.VBE.ActiveVBProject.VBComponents(strNomModule)

Thanks @Josef P. , I have not see than @isladogs make again loop. You go directly, that what I want.

 

[isladogs]

Ah I missed that point...
That simplifies the code significantly...

Function RechercheIntituleModForm(ByVal sSearchTerm As String, strNomModule As String)
        Dim oComponent As Object                                     'VBComponent
        
        Set oComponent = Application.VBE.ActiveVBProject.VBComponents(strNomModule)
    
        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
                Debug.Print "Module contient et vrai : " & oComponent.Name & " : " & sSearchTerm         'Name of the current module in which the term was found (at least once)
        Else
                Debug.Print "Module ne contient pas : " & oComponent.Name & " : " & sSearchTerm        'Name of the current module in which the term was not found (at least once)
        End If
End Function

 

[Superpat]

Sorry,I have a little problem more. I have a form with no module.
In errSub, I put err=9, but it is not very elegant, can we treat this case otherwise or precisely treating nothing at all

Function RechercheIntituleModForm(ByVal sSearchTerm As String, strNomModule As String)
        Dim oComponent As Object                                     'VBComponent
        On Error GoTo errSub
        Set oComponent = Application.VBE.ActiveVBProject.VBComponents(strNomModule)
        Debug.Print "Module IntituleModForm : " & oComponent.CodeModule
        If oComponent.CodeModule = strNomModule Then
                If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
                        Debug.Print "Module contient et vrai : " & oComponent.Name
                        RechercheIntituleModForm = True
                Else
                        Debug.Print "Module ne contient pas : " & oComponent.Name
                        RechercheIntituleModForm = False
                End If
        End If
Exitsub:
        On Error GoTo 0
        Exit Function
errSub:
        If err = 9 Then
                RechercheIntituleModForm = True
                On Error GoTo 0
                Exit Function
        End If
End Function

 

[Josef P.]

Do you want to find out whether the appropriate code module is available in ActiveVBProject.VBComponents? => Loop over VBComponents (as it can already be seen above).
Or do you want to check whether the form has a code module before checking the text in the code module? => FormReference.HasModule
From somewhere RechercheIntituleModForm must get the module name. In this procedure you could check in advance if the form has a code module.

 

[Superpat]

Before, in this module :

Function fnDmwListAllFormulairesDeroulantes(lComptageFormulaires As String) As String
        ' Liste les formulaires dans le fichier "t_ListeDeroulanteFormulaires
        On Error GoTo ErrHandler
        Dim bolExiste As Boolean
        Dim Table_Name As String
        Dim oRst    As DAO.Recordset
        Dim oDb     As DAO.Database
        Dim frm     As Object
        Set oDb = CurrentDb
        Table_Name = "t_ListeDeroulanteFormulaires"
        Set oRst = oDb.OpenRecordset(Table_Name, dbOpenDynaset)
        For Each frm In CurrentProject.AllForms
                Debug.Print "CurrentProject.AllForms : " & frm.Name
'                If frm.Name = "FormulaireVide" Then
'                        Stop
'                End If
                With oRst
                        bolExiste = RechercheIntituleModForm(strSearch, "Form_" & frm.Name)
                        If bolExiste = True Then
                                Debug.Print "Existe form : " & frm.Name
                        Else
                                Debug.Print "Existe pas form : " & frm.Name
                                .AddNew
                                ![Liste_Formulaires] = frm.Name
                                .Update
                        End If
                End With
        Next frm
        oRst.Close
        oDb.Close
        Set oRst = Nothing
        Set oDb = Nothing
        Dim strSQLComptageOperation As Long
        lComptageFormulaires = DCount("Liste_Formulaires", Table_Name)
        fnDmwListAllFormulairesDeroulantes = lComptageFormulaires
procDone:
        Exit Function
ErrHandler:
        MsgBox err.Number & " " & err.Description
        Resume procDone
End Function

 

[Josef P.]

If a form does not contain a code module, should it be stored in the table?

 

[Superpat]

Not, of course

 

[Josef P.]

Ok, then just an idea:
You only want to check the code modules of forms.

Function fnDmwListAllFormulairesDeroulantes(lComptageFormulaires As String) As String

    ...
    
    Set oDb = CurrentDb
    Table_Name = "t_ListeDeroulanteFormulaires"
    Set oRst = oDb.OpenRecordset(Table_Name, dbOpenDynaset)
    
    Dim vbc As VBComponent
    For Each vbc In GetAllFormVBComponents
        'bolExiste = RechercheIntituleModForm(strSearch, vbc) ' Interface changed form string to VBComponent (Object)
        'or
        bolExiste = RechercheIntituleModForm(strSearch, vbc.Name)
        If bolExiste = True Then
            Debug.Print "Existe form : " & frm.Name
        Else
            Debug.Print "Existe pas form : " & frm.Name
            .AddNew
            ![Liste_Formulaires] = frm.Name
            .Update
        End If
    Next
    
    ...
  
End Function

Private Function GetAllFormVBComponents() As Collection

    Dim col As Collection
    Dim vbc As VBComponent
    
    Set col = New Collection
    
    For Each vbc In Application.VBE.ActiveVBProject.VBComponents
        If Left(vbc.Name, 5) = "Form_" Then
            col.Add vbc, vbc.Name
        End If
    Next
    
    Set GetAllFormVBComponents = col

End Function

 

[isladogs]

Another possible approach would be to use the Boolean HasModule property
You will need to open the form(s) in design view in order to check that
Something like

Docmd.OpenForm "FormName",acDesign,,,,acHidden

If Forms!FormName.HasModule = False Then Exit Function

 

[Superpat]

Thanks @Josef P. and @isladogs all is good
And above all, I would not have been able to do this alone.