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
FourteAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
Have you searched any loading code you have running?

Try running this from a standard module - using your own form name of course...

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
                    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
                    strList = strList & ctl.Name & " : " & prp.Name & vbCrLf
                    Debug.Print ctl.Name
                    intCount = intCount + 1
                End If
            End If
        Next
    Next
   
    MsgBox "Found in " & intCount & " locations" & vbCrLf & strList
   
    Set ctl = Nothing
    Set frm = Nothing
   
End Function
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
FourteAuthor Commented:
I have looked at the properties for all of the fields on the form but can't find any reference
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Leigh PurvisDatabase DeveloperCommented:
And the form itself - the event properties?
0
 
FourteAuthor Commented:
yes
0
 
Rey Obrero (Capricorn1)Commented:
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

0
 
FourteAuthor Commented:
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
0
 
FourteAuthor Commented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.