Programmatically Determine if a Form/Report has Macros

**NeoPa** · Oct 17 '13, 10:00 PM

Topher, It's sad, but true, that macros in Access are very rarely worth the effort. The implementation has always been a long way short of an adequate implementation suitable for professional work. Of course, I appreciate these are something you've been given and not anything you're responsible for.

It's also sad, but true, that Bytes experts take breaks from time to time, and can't be relied upon to see every thread of the many posted per day. I'm afraid that's the name of the game. You have to live with that restriction. It's a good and interesting question though so I'll have a look at providing some sort of answer for you.

As you quite correctly highlight, the 'HasModule' property of Form and Report objects is a handy way to tell, quickly and easily, if such an object has any VBA code behind it. The macro side of things is absolutely not so well designed. What is needed is a couple of procedures that check the status of all Event Properties in every object within the specified (Form or Report) object.

NB. See below for the exception where code can be invoked from a non-related module. This may be of interest to you.

The first procedure would handle the object itself and all controls of that object.

The second procedure would check through all the properties of any passed object and, if they are an Event Property, check the value. The possible classes of values are (See Event Properties.) :
Empty - Not assigned. Ignore.
[Event Procedure] - Code. Ignore.
=* - Code (Specific procedure). Ignore.
Anything else - Macro.

There appears to be no defined way to determine which properties are Event properties, but you can make a list in your code from Events and Event Properties Reference. Not tidy, I know, but can give you what you need.

**topher23** · Oct 18 '13, 02:25 PM

Thanks, NeoPa, for your typically well-thought-out answer. I didn't even think to check the form and control properties themselves for macro triggers. I'll try to bang out a function for that - if it works, I'll post it back up here so others can benefit.

P.S. I thought you were omnipresent, omniscient, and omnipotent, NeoPa! ;)

**NeoPa** · Oct 18 '13, 11:58 PM

Me too Topher - but then I found I was just a normal chap. Boy was I mad ;-(

Knock out some code. Then, if you have any problems with it, we can look at it and find solutions for you. I expect you'll manage just fine by yourself though ;-)

**ADezii** · Oct 19 '13, 03:42 PM

Originally posted by topher23

Thanks, NeoPa, for your typically well-thought-out answer. I didn't even think to check the form and control properties themselves for macro triggers. I'll try to bang out a function for that - if it works, I'll post it back up here so others can benefit.

P.S. I thought you were omnipresent, omniscient, and omnipotent, NeoPa! ;)

I found this Thread interesting, so I am in the process of creating Code that will check every Event Procedure in every Form to see if it has any Code ([Event Procedure], [Embedded Macro], or Reference) associated with that Procedure. If it does, it will write the Object Type (Form in this case), Object Name (frmCustomers), Event (AfterUpdate()) , and Status ([Event Procedure], [Embedded Macro], [Reference]) to a Results Table. It can easily be expanded to include Reports and Event Procedures behind all Controls on all Forms. I should have this done in a day or two and I will be more than happy to Post the Results.

**zmbd** · Oct 20 '13, 12:14 PM

Sorry guys that this took me so long to get to:
the following should be modifiable:
Public Function FindMacrosInFor mReports() As Long (Allen Browne)
THis page has a series of several functions that are interrelated. I don't think that the last one is needed.

**ADezii** · Oct 20 '13, 03:27 PM

Create a Table named tblResults with 4 Fields:
1. Object {TEXT}
2. Name {TEXT}
3. Event {TEXT}
4. Status {TEXT}

Execute the following Function from the Immediate Window or via a Macro (all Forms must be closed):

Code:

Public Function fAnalyzeForms2()
On Error Resume Next
Dim aob As AccessObject
Dim prp As Property

CurrentDb.Execute "DELETE * FROM tblResults;", dbFailOnError

For Each aob In CurrentProject.AllForms
  DoCmd.OpenForm aob.Name, acDesign, , , acFormReadOnly, acHidden
    For Each prp In Forms(aob.Name).Properties
      If Left$(prp.Name, 2) = "On" And Forms(aob.Name).Properties(prp.Name).Value <> "" Then
        CurrentDb.Execute "INSERT INTO tblResults ([Object],[Name],[Event],[Status]) VALUES " & _
                        "('Form','" & aob.Name & "','" & Mid$(prp.Name, 3) & "','" & _
                        Forms(aob.Name).Properties(prp.Name) & "')", dbFailOnError
        'Debug.Print prp.Name & " | " & prp.Value
      ElseIf (prp.Name Like "Before*") Or (prp.Name Like "After*") Or (prp.Name = "MouseWheel") Then
        If Forms(aob.Name).Properties(prp.Name).Value <> "" Then
          CurrentDb.Execute "INSERT INTO tblResults ([Object],[Name],[Event],[Status]) VALUES " & _
                            "('Form','" & aob.Name & "','" & prp.Name & "','" & _
                            Forms(aob.Name).Properties(prp.Name) & "')", dbFailOnError
        End If
      Else
        'Not interested
      End If
    Next
    'Here is where you would branch to a Function passing the Form Name (aob.name). The Function
    'would then Loop thru all the Controls on the passed Form identifying any Embedded Macros,
    'Event Procedures, or References and populating tblResults
  DoCmd.Close acForm, aob.Name, acSaveNo
Next
End Function

Sample results after Function Execution:

Code:

Object	Name	                Event	            Status
Form	  frmZoom	             MouseUpEmMacro		"Version =196611
Form	  Employees (page break)  AfterUpdate	  		Employees (page break).RequeryReportsTo
Form	  Customers			   AfterUpdate			Customers.Update Country List
Form	  Startup			     Open				=OpenStartup()
Form	  Startup			     Close				=HideStartupForm()
Form	  Customer Orders Subform1	Current			[Event Procedure]
Form	  Customers			   Load				[Event Procedure]
Form	  Employees			   AfterUpdate			[Event Procedure]
Form	  frmZoom			     Open				[Embedded Macro]
Form	  Orders Subform		   BeforeDelConfirm		[Event Procedure]
Form	  Orders Subform		   BeforeUpdate			[Event Procedure]
Form	  Orders Subform		   Error				[Event Procedure]
Form	  Orders Subform2		  Error				[Event Procedure]
Form	  Orders Subform2		  BeforeDelConfirm		[Event Procedure]
Form	  Orders Subform2	      BeforeUpdate			[Event Procedure]
Form	  Suppliers			    BeforeUpdate			[Event Procedure]
Form	  Suppliers			    Current			[Event Procedure]
Form	  Suppliers			    Close				[Event Procedure]
Form	  frmZoom			      MouseUp			[Embedded Macro]
Form	  frmZoom			      Click				[Embedded Macro]

P.S. - Kindly forgive my incomplete formatting of the Results - would have been late for work! (LOL)

**zmbd** · Oct 20 '13, 07:38 PM

Allen Browne's code:
If you are using ACC2007/2010 there needs to be a tweak in the first set of code "Public Function FindMacrosInFor mReports()" Alter the line "PropValue TEXT(64));" to "PropValue Memo);" otherwise, the new XML format of the embeded macro will cause an error.

**topher23** · Oct 21 '13, 03:24 PM

After looking over zmbd's Allen Browne link (whose website I checked before I posted, so I feel somewhat sheepish that I missed it) and Adezii's, code, I'm struck by how similar the methodology is, down to the operators used in the If-Then's and the results table. Adezii, are you an Allen Browne protege?

This is what I've put together. I slapped all of the possible event procedures into a function with a great big Select Case statement (not sure if it's a faster or slower method), and because I want to convert in real-time rather than check for results, I plugged acCmdConvertMac rosToVisualBasi c right into the If-Then. I ran it against several db's and kept coming up with no results, but then I realized the problem: I was running the code against an automation instance and passing just the form, not the application, to the procedure, so the DoCmd function was running against the default Application object rather than my automation instance. Once I passed the new Application instance to the procedure, everything worked perfectly. There's probably an elegant way of pulling the application object off the form instance, but this works in a pinch.

Code:

Public Function ConvertFormMacros(f As Form, app As Application)
    Dim c As Control
    Dim p As Property
    On Error Resume Next
    
    For Each p In f.Properties
        If IsEvent(p.Name) Then
            If Not (Nz(p.Value) = "[Event Procedure]" Or Nz(p.Value) Like "=*" Or Nz(p.Value) = "") Then
                app.DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
            End If
        End If
    Next p
    
    For Each c In f.Controls
        For Each p In c.Properties
            If IsEvent(p.Name) Then
                If Not (Nz(p.Value) = "[Event Procedure]" Or Nz(p.Value) Like "=*" Or Nz(p.Value) = "") Then
                    app.DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
                End If
            End If
        Next p
    Next c
    
End Function


Public Function IsEvent(strProperty As String)
    Select Case strProperty
        Case "OnActivate", "AfterDelConfirm", "AfterInsert", "AfterUpdate", "OnApplyFilter", "BeforeDelConfirm", _
                "BeforeInsert", "BeforeUpdate", "OnChange", "OnClick", "OnClose", "OnCurrent", "OnDblClick", "OnDeactivate", _
                "OnDelete", "OnDirty", "OnEnter", "OnError", "OnExit", "OnFilter", "OnFormat", "OnGotFocus", "OnKeyDown", _
                "OnKeyPress", "OnKeyUp", "OnLoad", "OnLostFocus", "OnMouseDown", "OnMouseMove", "OnMouseUp", "OnNoData", _
                "OnNotInList", "OnOpen", "OnPage", "OnPrint", "OnResize", "OnRetreat", "OnTimer", "OnUnload", "OnUpdated"
            IsEvent = True
        Case Else
            IsEvent = False
    End Select
End Function

I suppose the only real advantage to this method rather than saving results in a table is that once the first macro is detected in a form, all of the macros get converted so there are no more positive results for that form. On the flipside, it does need babysitting while it runs, but the calling subroutine which inserts the error handling code needs to be babysat anyway, so in this case that's not an issue.

**ADezii** · Oct 21 '13, 05:33 PM

There's probably an elegant way of pulling the application object off the form instance...

If I am interpreting you correctly...

Code:

Dim frm As Form
Dim AccApp As Access.Application

Set frm = Forms("Form1")        'assuming Form1 is Loaded
Set AccApp = frm.Application

Debug.Print AccApp.Version

**topher23** · Oct 21 '13, 06:28 PM

Yep, that's it, Adezii. If anyone is interested in how the functions you've all helped me put together go into the error handling insertion code, it's all here: http://tophersaccesstips.blogspot.co...-handling.html

**NeoPa** · Oct 22 '13, 12:43 AM

Nice work Topher :-) I'm so pleased for you that you ran with it and produced your own code from the promptings.

As a quick performance boost (I believe), here's another potential version of IsEvent() :

Code:

Private Const conEvents As String = _
                  ",OnActivate,AfterDelConfirm,AfterInsert,AfterUpdate" _
                & ",OnApplyFilter,BeforeDelConfirm,BeforeInsert,BeforeUpdate" _
                & ",OnChange,OnClick,OnClose,OnCurrent,OnDblClick" _
                & ",OnDeactivate,OnDelete,OnDirty,OnEnter,OnError,OnExit" _
                & ",OnFilter,OnFormat,OnGotFocus,OnKeyDown,OnKeyPress,OnKeyUp" _
                & ",OnLoad,OnLostFocus,OnMouseDown,OnMouseMove,OnMouseUp" _
                & ",OnNoData,OnNotInList,OnOpen,OnPage,OnPrint,OnResize" _
                & ",OnRetreat,OnTimer,OnUnload,OnUpdated,"

Private Function IsEvent(strPropName As String) As Boolean
    IsEvent = (InStr(1, conEvents, "," & strPropName & ",") > 0)
End Function

Personally, I would place the Const definition at the head of the module rather than by the procedure itself, but it's all there.

**ADezii** · Oct 22 '13, 02:56 AM

Really nice job, topher23. The only potential problem that I see is if you have a Tabbed Control on a Form with Multiple Pages/Controls. The Tab Control itself would show up as a Control but the Controls within would not via the Code. They would have to be accessed via the Pages Collection. If I am incorrect, please correct me.

**topher23** · Oct 22 '13, 05:00 AM

I think you're right, ADezii. In the case of the databases I've inherited, I might just be able to get away with not coding an exception for the tabbed control. Most of the macros appear to be on command buttons (thanks to the control wizard), and fortunately any form with tabbed controls also has at least one command button on the form itself, so that control is triggering the macro conversion. All the same, I'll look into it.

**ADezii** · Oct 22 '13, 02:08 PM

@topher23:
It appears as though I may have been wrong. The Controls Collection of a Form contains the Tab Control itself, the individual Pages, as well as Controls contained within the Pages, at least in Version 2007. Sorry about that.

Programmatically Determine if a Form/Report has Macros

Programmatically Determine if a Form/Report has Macros

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment