UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form/report Document Hasmodule?, Any Version    
 
   
Schizolocal
post Mar 5 2018, 04:41 AM
Post#1



Posts: 272
Joined: 22-March 06
From: St.Helena


Hi

Does anyone know if there is a way of determining whether a form or report has a class module without actually opening it (and if so, how it can be accessed, even read-only, via VBA)? I can't see anything useful in the Properties of the Form object of AllForms, or of the Document object of the Forms Container, nor can I see any pattern in the MSysObject table.

Thanks
Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
HairyBob
post Mar 5 2018, 04:57 AM
Post#2



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Stuart,

This may help:

CODE
Public Sub PrintModuleNames()

    Dim intIndex As Integer
    Dim mods As Modules
    
    Set mods = Application.Modules
    
    For intIndex = 0 To mods.Count - 1
        Debug.Print mods(intIndex).Name
        Debug.Print mods(intIndex).Type
    Next
    
    Set mods = Nothing

End Sub


I found that Type is 0 for standard modules and 1 for class modules.

Unfortunately, I can't see any property for a module object which differentiates between a class module not attached to a form/report and one that is; however, the form/report name accessed via the 'Name' property is prefixed with 'Form_' for forms and 'Report_' for reports.

There is a HasModule property for forms and reports (https://msdn.microsoft.com/en-us/VBA/access...property-access); however, to access it, the form/report has to be open.

HTH...

Hairy.
This post has been edited by HairyBob: Mar 5 2018, 05:24 AM
Go to the top of the page
 
Schizolocal
post Mar 5 2018, 06:09 AM
Post#3



Posts: 272
Joined: 22-March 06
From: St.Helena


Of course (slaps forehead in embarassment!)

Thanks
Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
HairyBob
post Mar 5 2018, 06:46 AM
Post#4



Posts: 992
Joined: 26-March 08
From: London, UK


No problem yw.gif
Go to the top of the page
 
DanielPineault
post Mar 5 2018, 06:58 AM
Post#5


UtterAccess VIP
Posts: 6,068
Joined: 30-June 11



See: https://msdn.microsoft.com/en-us/library/of...office.11).aspx

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Schizolocal
post Mar 5 2018, 09:49 AM
Post#6



Posts: 272
Joined: 22-March 06
From: St.Helena


Thankyou Daniel
But the HasModule property applies only to open forms/reports. As I said, I am trying to find properties of all forms/reports without opening them (even in design mode) and determining whether or not they have code.

Regards
Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
Schizolocal
post Mar 5 2018, 09:57 AM
Post#7



Posts: 272
Joined: 22-March 06
From: St.Helena


Hairy

I've just tried the code - I find that the "mods" collection contains only those modules that are open in the VBA project window.

(which agrees with the VBA help : "The Modules collection contains all open standard modules and class modules in a Microsoft Access database" )

So it seems I have to open the form anyway. Bummer.

Stuart
This post has been edited by Schizolocal: Mar 5 2018, 10:00 AM

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
HairyBob
post Mar 5 2018, 10:27 AM
Post#8



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Stuart,

That's strange... The code I posted works for me without the forms/reports having been opened - see attachment - without opening the form or report, open mdlTest and run PrintModuleNames().

Hairy.

Attached File(s)
Attached File  HasModule.zip ( 20.96K )Number of downloads: 6
 
Go to the top of the page
 
Schizolocal
post Mar 5 2018, 11:44 AM
Post#9



Posts: 272
Joined: 22-March 06
From: St.Helena


Thanks for the sample.

When I run your "PrintModuleNames" procedure from the original state of the VBA project window, I get, as you say, a listing of all five modules in the debug window. But only if those modules are open in the VBA project window. Close them and the only one that gets listed is mdlTest, and even that disappears if I also close the mdlTest module and call "PrintModuleNames" from the debug window.

(Note: initially, the forms/reports are not open in the database window, but their VBA class modules are open in the VBA project window).

This is the same behaviour as I have experienced in my code.

I doubt it makes any difference, but I am testing with A2007.

Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
DanielPineault
post Mar 5 2018, 03:11 PM
Post#10


UtterAccess VIP
Posts: 6,068
Joined: 30-June 11



QUOTE
But the HasModule property applies only to open forms/reports. As I said, I am trying to find properties of all forms/reports without opening them (even in design mode) and determining whether or not they have code.


That is simply not true and I quote

QUOTE
The HasModule property can be set only in form or report Design view but can be read in any view

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Schizolocal
post Mar 5 2018, 03:39 PM
Post#11



Posts: 272
Joined: 22-March 06
From: St.Helena


Daniel

I believe we are both right (what a diplomat, eh?).

I do not dispute the fact that the HasModule property can be read in any view, but my point is that the object must be open - whether in form view or design view or any other view, it must be open. I am looking for a property indicating whether a non-open form/report has code (or a different way of determining). Here is what I have found:

The MSysObject table includes records for only standard and stand-alone class modules. It contains records for all forms and reports (whether open or not) but does not have any indication of whether they have code associated.

The AllForms and AllReports collections of the CurrentProject of the Application object includes all forms and reports, but again, there is no indication of whether they have associated code. The AllModules collection only includes standard and stand-alone class modules. It would seem to me that the AccessObject object model simply interprets the MSysObject table.

The Documents collection of the Forms/Reports Containers appears to have the same problem as AllForms/AllReports - no HasModule property or equivalent.

Now, as we have seen, the Application.Modules collection includes only those modules/classes (including Form/Report-associated class modules) that are currently open in the VBA project window, analagously to the Forms collection including only those forms which are currently open in the database window.


Unless there is something I have missed (which was effectively the gist of my original query), it would seem to me that I can only determine whether a form or report has associated code by opening it, but, as you say, in any view.


Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
DanielPineault
post Mar 5 2018, 04:01 PM
Post#12


UtterAccess VIP
Posts: 6,068
Joined: 30-June 11



Sorry, a little slow today! crazy.gif




What about something like:

CODE
'---------------------------------------------------------------------------------------
   ' Procedure : Obj_HasModule
   ' Author    : Daniel Pineault, CARDA Consultants Inc.
   ' Website   : [URL="http://www.cardaconsultants.com"]http://www.cardaconsultants.com[/URL]
   ' Purpose   : Determine if a Form or Report has a VBA code module
   '               Alternative to HasModule that doesn't require the object to be open
   ' Copyright : The following is release as Attribution-ShareAlike 4.0 International
   '             (CC BY-SA 4.0) - [URL="https://creativecommons.org/licenses/by-sa/4.0/"]https://creativecommons.org/licenses/by-sa/4.0/[/URL]
   ' Req'd Refs: Uses Late Binding, so none required
   '
   ' Input Variables:
   ' ~~~~~~~~~~~~~~~~
   ' sObjName  : Name of the Report/Form to check for an associated module
   ' sObjType  : Object Type -> "Form" or "Report"
   '
   ' Usage:
   ' ~~~~~~
   ' Obj_HasModule("Employee List","Form")
   '
   ' Revision History:
   ' Rev       Date(yyyy/mm/dd)        Description
   ' ********************************************************************************
   ******
   ' 1         2018-03-05              Initial Release
   '---------------------------------------------------------------------------------------
   Public Function Obj_HasModule(ByVal sObjName As String, sObjType As String) As Boolean
       On Error GoTo Error_Handler
       Dim VBComp                As Object
       Dim sSearchFor            As String
  
       Select Case sObjType
           Case "Form"
               sSearchFor = "Form_" & sObjName
           Case "Report"
               sSearchFor = "Report_" & sObjName
       End Select
  
       With Application.VBE
           For Each VBComp In .ActiveVBProject.VBComponents
               If VBComp.Name = sSearchFor Then
                   Obj_HasModule = True
                   Exit For
               End If
           Next VBComp
       End With
  
   Error_Handler_Exit:
       On Error Resume Next
       If Not VBComp Is Nothing Then Set VBComp = Nothing
       Exit Function
  
   Error_Handler:
       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
              "Error Number: " & Err.Number & vbCrLf & _
              "Error Source: Obj_HasModule" & vbCrLf & _
              "Error Description: " & Err.Description & _
              Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
              , vbOKOnly + vbCritical, "An Error has Occured!"
       Resume Error_Handler_Exit
   End Function



or

CODE
'---------------------------------------------------------------------------------------
' Procedure : Obj_HasModule2
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : [URL="http://www.cardaconsultants.com"]http://www.cardaconsultants.com[/URL]
' Purpose   : Determine if a Form or Report has a VBA code module
'               Alternative to HasModule that doesn't require the object to be open
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - [URL="https://creativecommons.org/licenses/by-sa/4.0/"]https://creativecommons.org/licenses/by-sa/4.0/[/URL]
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjName  : Name of the Report/Form to check for an associated module
' sObjType  : Object Type -> "Form" or "Report"
'
' Usage:
' ~~~~~~
' Obj_HasModule2("Employee List","Form")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ********************************************************************************
******
' 1         2018-03-05              Initial Release
'---------------------------------------------------------------------------------------
Public Function Obj_HasModule2(ByVal sObjName As String, sObjType As String) As Boolean
     On Error GoTo Error_Handler
     Dim VBComp                As Object
     Dim sSearchFor            As String

     Select Case sObjType
         Case "Form"
             sSearchFor = "Form_" & sObjName
         Case "Report"
             sSearchFor = "Report_" & sObjName
     End Select

     With Application.VBE
         Set VBComp = .ActiveVBProject.VBComponents(sSearchFor)
         Obj_HasModule2 = True
     End With

Error_Handler_Exit:
     On Error Resume Next
     If Not VBComp Is Nothing Then Set VBComp = Nothing
     Exit Function

Error_Handler:
     If Err.Number <> 9 Then
         MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                "Error Number: " & Err.Number & vbCrLf & _
                "Error Source: Obj_HasModule2" & vbCrLf & _
                "Error Description: " & Err.Description & _
                Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                , vbOKOnly + vbCritical, "An Error has Occured!"
     End If
     Resume Error_Handler_Exit
End Function

You could also probably export the object using the undocumented SaveAsText and then read the property from there.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
HairyBob
post Mar 6 2018, 01:16 AM
Post#13



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Stuart,

I see that Daniel has posted a neat solution for you, so I won't go any further. In Daniel's code however, when assigning the sSearchFor string, (both functions) 'Report' should have an underscore at the end; i.e.

CODE
...
sSearchFor = "Report" & sObjName
...


Should be:

CODE
...
sSearchFor = "Report_" & sObjName
...


Hairy.
This post has been edited by HairyBob: Mar 6 2018, 01:58 AM
Go to the top of the page
 
Schizolocal
post Mar 6 2018, 05:08 AM
Post#14



Posts: 272
Joined: 22-March 06
From: St.Helena


Daniel

Thank you very much. I will certainly investigate further. Clearly the VBE object was the missing detail.

Just as a matter of interest, is there anything to prevent me from declaring a standard or class module named, say, "Form_Book" or "Report_It"? - not that I would, but that is by-the-by.
If so, the functions would be potentially unreliable.

Cheers
Stuart

--------------------
Opinions expressed that happen to coincide with
those of my employer, are mine. I work for myself.
Go to the top of the page
 
DanielPineault
post Mar 6 2018, 12:50 PM
Post#15


UtterAccess VIP
Posts: 6,068
Joined: 30-June 11



Good thought. You could check the .Type. Perhaps the following would be more reliable.

CODE
'---------------------------------------------------------------------------------------
  ' Procedure : Obj_HasModule
  ' Author    : Daniel Pineault, CARDA Consultants Inc.
  ' Website   : [URL="http://www.cardaconsultants.com"]http://www.cardaconsultants.com[/URL]
  ' Purpose   : Determine if a Form or Report has a VBA code module
  '               Alternative to HasModule that doesn't require the object to be open
  ' Copyright : The following is release as Attribution-ShareAlike 4.0 International
  '             (CC BY-SA 4.0) - [URL="https://creativecommons.org/licenses/by-sa/4.0/"]https://creativecommons.org/licenses/by-sa/4.0/[/URL]
  ' Req'd Refs: Uses Late Binding, so none required
  '
  ' Input Variables:
  ' ~~~~~~~~~~~~~~~~
  ' sObjName  : Name of the Report/Form to check for an associated module
  ' sObjType  : Object Type -> "Form" or "Report"
  '
  ' Usage:
  ' ~~~~~~
  ' Obj_HasModule("Employee List","Form")
  '
  ' Revision History:
  ' Rev       Date(yyyy/mm/dd)        Description
  ' ********************************************************************************
  ******
  ' 1         2018-03-05              Initial Release
  ' 2         2018-03-06              Additional check for the Type to avoid confusing
  '                                   conflicting standard/class modules
  '---------------------------------------------------------------------------------------
  Public Function Obj_HasModule(ByVal sObjName As String, sObjType As String) As Boolean
      On Error GoTo Error_Handler
      Dim VBComp                As Object
      Dim sSearchFor            As String
  
      Select Case sObjType
          Case "Form"
              sSearchFor = "Form_" & sObjName
          Case "Report"
              sSearchFor = "Report_" & sObjName
      End Select
  
      With Application.VBE
          For Each VBComp In .ActiveVBProject.VBComponents
              If VBComp.Name = sSearchFor And VBComp.Type = 100 Then
                  Obj_HasModule = True
                  Exit For
              End If
          Next VBComp
      End With
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not VBComp Is Nothing Then Set VBComp = Nothing
      Exit Function
  
  Error_Handler:
      MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Error Source: Obj_HasModule" & vbCrLf & _
             "Error Description: " & Err.Description & _
             Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
             , vbOKOnly + vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
  End Function


CODE
'---------------------------------------------------------------------------------------
  ' Procedure : Obj_HasModule2
  ' Author    : Daniel Pineault, CARDA Consultants Inc.
  ' Website   : [URL="http://www.cardaconsultants.com"]http://www.cardaconsultants.com[/URL]
  ' Purpose   : Determine if a Form or Report has a VBA code module
  '               Alternative to HasModule that doesn't require the object to be open
  ' Copyright : The following is release as Attribution-ShareAlike 4.0 International
  '             (CC BY-SA 4.0) - [URL="https://creativecommons.org/licenses/by-sa/4.0/"]https://creativecommons.org/licenses/by-sa/4.0/[/URL]
  ' Req'd Refs: Uses Late Binding, so none required
  '
  ' Input Variables:
  ' ~~~~~~~~~~~~~~~~
  ' sObjName  : Name of the Report/Form to check for an associated module
  ' sObjType  : Object Type -> "Form" or "Report"
  '
  ' Usage:
  ' ~~~~~~
  ' Obj_HasModule2("Employee List","Form")
  '
  ' Revision History:
  ' Rev       Date(yyyy/mm/dd)        Description
  ' ********************************************************************************
  ******
  ' 1         2018-03-05              Initial Release
  ' 2         2018-03-06              Additional check for the Type to avoid confusing
  '                                   conflicting standard/class modules
  '---------------------------------------------------------------------------------------
  Public Function Obj_HasModule2(ByVal sObjName As String, sObjType As String) As Boolean
      On Error GoTo Error_Handler
      Dim VBComp                As Object
      Dim sSearchFor            As String
  
      Select Case sObjType
          Case "Form"
              sSearchFor = "Form_" & sObjName
          Case "Report"
              sSearchFor = "Report_" & sObjName
      End Select
  
      With Application.VBE
          Set VBComp = .ActiveVBProject.VBComponents(sSearchFor)
          If VBComp.Type = 100 Then
              Obj_HasModule2 = True
          End If
      End With
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not VBComp Is Nothing Then Set VBComp = Nothing
      Exit Function
  
  Error_Handler:
      If Err.Number <> 9 Then
          MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Obj_HasModule2" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
      End If
      Resume Error_Handler_Exit
  End Function

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Mar 6 2018, 04:59 PM
Post#16


UtterAccess VIP
Posts: 6,068
Joined: 30-June 11



For fun, here's another potential approach

CODE
'---------------------------------------------------------------------------------------
' Procedure : Obj_HasModule3
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : [URL="http://www.cardaconsultants.com"]http://www.cardaconsultants.com[/URL]
' Purpose   : Determine if a Form or Report has a VBA code module
'               Alternative to HasModule that doesn't require the object to be open
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - [URL="https://creativecommons.org/licenses/by-sa/4.0/"]https://creativecommons.org/licenses/by-sa/4.0/[/URL]
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjName  : Name of the Report/Form to check for an associated module
' sObjType  : Object Type -> "Form" or "Report"
'
' Usage:
' ~~~~~~
' ? Obj_HasModule3("Employee List","Form")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ********************************************************************************
******
' 1         2018-03-05              Initial Release
'---------------------------------------------------------------------------------------
Public Function Obj_HasModule3(ByVal sObjName As String, sObjType As String) As _
       Boolean
    On Error GoTo Error_Handler
    Dim sFile                 As String
    Dim sSaveAsTextContent    As String
    Dim iFileNumber           As Integer

    'Determine the temo folder' location
    sTmpFldr = Environ("temp") & "\"

    'Save the object to text
    Select Case sObjType
        Case "Form"
            sFile = sTmpFldr & "Form-" & sObjName & ".txt"
            Application.SaveAsText acForm, sObjName, sFile
        Case "Report"
            sFile = sTmpFldr & "Report-" & sObjName & ".txt"
            Application.SaveAsText acReport, sObjName, sFile
    End Select

    'Read the text file looking for VBA content
    iFileNumber = FreeFile
    Open sFile For Input As #iFileNumber
    Do Until EOF(1)
        Line Input #1, textline
        If Trim(textline) = "CodeBehindForm" Then
            Obj_HasModule3 = True
            Exit Do
        End If
    Loop

Error_Handler_Exit:
    On Error Resume Next
    Close #iFileNumber    'Close our handle to the text file we were reading
    Kill sFile    'Delete the text file now that we're done with it
    Exit Function

Error_Handler:
    If Err.Number = 32584 Then
        'The specified object does not exist!  What do we do?
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Obj_HasModule3" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl), _
               vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function





One more option to ponder. I'd normally stick with the pure VBA approaches, but I wanted to prove this approach was possible and thought I'd share it in case it could serve someone else in some manner.



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 10:26 AM