Link to home
Start Free TrialLog in
Avatar of Fourte
Fourte

asked on

How to locate a reference to a macro

In the project that I am working on, one of the forms has developed a fault (probably my sticky fingers).
When the form opens I get the error saying something like "can't find macro ".".
Is there any way in which I can easily identify any and all references to macro's?
Kind regards
Peter
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you accidentally - typed a "." into an event property for the form?
Probably Open, Load, Activate or Current.

Or on a control that initially receives the focus.
Avatar of Fourte
Fourte

ASKER

I have looked at the properties for all of the fields on the form but can't find any reference
And the form itself - the event properties?
Avatar of Fourte

ASKER

yes
can you see where the code is stopping when you clcik DEBUG in the error message box
if not try this.

in the form open event or load event

private sub form_open(cancel as integer)

Stop    '<<<<  type this


end sub

then step through yor codes by pressing F8, see where it will error out

Avatar of Fourte

ASKER

1.   DEBUG is not available on the error message
2.   tried your idea but after the initial error,  before it goes into the form, it doesn't error out

Must be a sign that I shouldn't be working this weekend
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fourte

ASKER

Managed to get it to run once, but do not know how.    How ever it did identify three fields 'SupplierID, 'JobNo' and 'OrderNo' but on examination I can find no reference to "." Macro.     Since then it keeps asking for 'macro name'. '

I simply created a blank form, added a command button and then added your code to the 'on dblClick'   Is this OK or have I got myself in a twist again?

Is it possible to be more explicite?   i.e. to identify the property that is causing the problem.

Your help is very much appreciated as I am very much out of my depth with VBA

Cheers

Peter
It should already list the precise property as well as the control in question.
What you have mentioned should be OK (though there's usually no reason to not use the Click even of a command button :-)

Perhaps try the following slight revision - as errors could be a problem.
Also - when it's done if you hit Ctrl-G to view the debug window - there should be a list of the same data as reported in the message box.
If the function returns no info then the problem looks like it's more buggy than actually having a wrong macro name.

Remember to change to using your problem form's name where indicated.

Function fCheckProperties()
On Error Resume Next

    Dim frm As Form
    Dim ctl As Control
    Dim prp As Property
    Dim strList As String
    Dim strIgnoreList As String
    Dim intCount As Integer
   
    Const cStrForm = "Your Form Name Here"  <--- !!!!!!!!!!!!!!!change this!!!!!!!!!!!!!!

    strIgnoreList = "Text,SelText,SelStart,SelLength,PrtMip,PrtDevMode,PrtDevNames," & _
                        "CurrentSectionTop,CurrentSectionLeft,SelLeft,SelTop,SelWidth,SelHeight"
   
    DoCmd.OpenForm cStrForm, acDesign
    Set frm = Forms(cStrForm)
   
    For Each prp In frm.Properties
        If InStr(strIgnoreList, prp.Name) = 0 Then
            If prp.value = "." Then
                If err <> 2186 Then
                    strList = strList & "Form : " & prp.Name & vbCrLf
                    Debug.Print "Form : " & prp.Name
                    intCount = intCount + 1
                End If
                err.Clear
            End If
        End If
    Next

    For Each ctl In frm.Controls
        For Each prp In ctl.Properties
            If InStr(strIgnoreList, prp.Name) = 0 Then
                If prp.value = "." Then
                    If err <> 2186 Then
                        strList = strList & ctl.Name & " : " & prp.Name & vbCrLf
                        Debug.Print ctl.Name & " : " & prp.Name
                        intCount = intCount + 1
                    End If
                End If
            End If
        Next
    Next
   
    MsgBox "Found in " & intCount & " locations" & vbCrLf & strList
   
    Set ctl = Nothing
    Set frm = Nothing
   
End Function