?
Solved

How to locate a reference to a macro

Posted on 2006-04-15
9
Medium Priority
?
247 Views
Last Modified: 2008-02-20
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
0
Comment
Question by:Fourte
  • 4
  • 4
9 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16460368
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
 

Author Comment

by:Fourte
ID: 16460390
I have looked at the properties for all of the fields on the form but can't find any reference
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16460398
And the form itself - the event properties?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Fourte
ID: 16460440
yes
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16460445
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
 

Author Comment

by:Fourte
ID: 16460490
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
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1000 total points
ID: 16460612
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
 

Author Comment

by:Fourte
ID: 16470549
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16472790
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question