469,150 Members | 1,950 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

Macro runs correctly but displays Action Failed Message

1,258 Expert 1GB
I am working in Access 2003. I have written some code to add a context menu to a Treeview. I have a working right-click menu, I right-click on a node, I get my options and I choose "Display Details" and a message box pops up showing me the .Text, .Tag and .Key of the node. Perfect. However, after that I get an "Action Failed" message box from the macro referenced by the .OnAction of the context menu selection.

My macro has only one instruction, that is RunCode with the parameter
Forms!TreeViewForm.ShowPopUpNodeDetails ()
I have debugged through every line of this function and there are no errors being hit there.

Why does the system think this macro failed?

Just in case it helps, here is the code:
Expand|Select|Wrap|Line Numbers
  1. Public Function ShowPopUpNodeDetails() As Boolean
  2.  
  3.     'Set TreeView = Me!TreeView1
  4.    On Error GoTo ShowPopUpNodeDetails_Error
  5.  
  6.     With Me!TreeView1.SelectedItem
  7.         MsgBox "Text=" & .Text & vbNewLine & _
  8.             "Key=" & .Key & vbNewLine & _
  9.             "Tag=" & .Tag
  10.     End With
  11.  
  12.    On Error GoTo 0
  13.    ShowPopUpNodeDetails = True
  14.    Exit Function
  15.  
  16. ShowPopUpNodeDetails_Error:
  17.  
  18.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowPopUpNodeDetails of VBA Document Form_TreeViewForm"
  19.     Resume Next
  20.  
  21. End Function
  22.  
And here is the code that created the right-click menu.
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateNodeContextMenu()
  2.  
  3.    On Error GoTo CreateNodeContextMenu_Error
  4.  
  5.    Dim cmbShortcutMenu As Office.CommandBar
  6.  
  7.    CommandBars("TreeViewContextMenu").Delete
  8.     ' Create a shortcut menu named "SimpleShortcutMenu".
  9.     Set cmbShortcutMenu = CommandBars.Add("TreeViewContextMenu", _
  10.                         msoBarPopup, False, False)
  11.   With cmbShortcutMenu.Controls.Add(msoControlButton)
  12.       .Caption = "Node Details"
  13.       .OnAction = "PopUpNodeDetails"
  14.     End With
  15.  
  16.     With cmbShortcutMenu.Controls.Add(msoControlButton)
  17.       .Caption = "Delete Node"
  18.       .OnAction = "PopUpNodeDelete"
  19.     End With
  20.  
  21.     Set cmbShortcutMenu = Nothing
  22.  
  23.   On Error GoTo 0
  24.    Exit Sub
  25.  
  26. CreateNodeContextMenu_Error:
  27.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateNodeContextMenu of Module ContextMenu_bas"
  28.     Resume Next
  29. End Sub
  30.  
Jim
Mar 5 '11 #1

✓ answered by ADezii

  1. ShowPopUpNodeDetails() should be Declared as Public in a Standard Code Module, not the Form's Code Module.
  2. The Function Name Argument of the RunCode Action in your Macro should disply the Function name only, namely:
    Expand|Select|Wrap|Line Numbers
    1. ShowPopUpNodeDetails() 
  3. In ShowPopUpNodeDetails(), replace the Me qualifier with the Absolute Path to the Form, as in:
    Expand|Select|Wrap|Line Numbers
    1. With Forms!TreeViewForm!TReeview1.SelectedItem
    2.            MsgBox "Text=" & .Text & vbNewLine & _
    3.            "Key=" & .Key & vbNewLine & _
    4.            "Tag=" & .Tag
    5. End With
    6.  

4 2708
ADezii
8,800 Expert 8TB
  1. ShowPopUpNodeDetails() should be Declared as Public in a Standard Code Module, not the Form's Code Module.
  2. The Function Name Argument of the RunCode Action in your Macro should disply the Function name only, namely:
    Expand|Select|Wrap|Line Numbers
    1. ShowPopUpNodeDetails() 
  3. In ShowPopUpNodeDetails(), replace the Me qualifier with the Absolute Path to the Form, as in:
    Expand|Select|Wrap|Line Numbers
    1. With Forms!TreeViewForm!TReeview1.SelectedItem
    2.            MsgBox "Text=" & .Text & vbNewLine & _
    3.            "Key=" & .Key & vbNewLine & _
    4.            "Tag=" & .Tag
    5. End With
    6.  
Mar 6 '11 #2
jimatqsi
1,258 Expert 1GB
Adezii,
Thanks for your reply. Now please excuse me while I try to learn something.

What you are suggesting is what I tried first. Because I am trying to make something generic, and then I found there was no way to pass a parameter to the macro to tell it what form I was dealing with. So if I have to specify the form name in the function, it seems I might as well build it into the form itself. Any module with nothing generic about it might as well be part of some object's module.

What's curious is that I read on a Microsoft page that a macro called like this will first look in the "form's class module" for the function. I'd never heard that term before ("class module", yes, but not "form's class module"). So it seemed that I could have a truly generic piece of code by pasting this function into every form that I wanted to use it on by using the "Me" qualifier.

Anyway, it hasn't worked for me. It works like that but the stupid macro error message comes up. So, I can make it work with no error message by making a public function that refers explicitly to a given form. And when I want to make a TreeView right-click menu in another form, I would have to make another, similar public module and change the explicit form reference. And again and again, every time I use TreeViews.

Isn't there a better way?

Jim
Mar 9 '11 #3
ADezii
8,800 Expert 8TB
If you wish the Code to be Portable/Generic, then simply pass the Name of the Form and TreeView Control to the Public Function:
Expand|Select|Wrap|Line Numbers
  1. Public Function fSomePublicFunction(strFormname As String, strTreeViewName As String)
  2.   With Forms(strFormname).Controls(strTreeViewName).SelectedItem
  3.     MsgBox "Text=" & .Text & vbNewLine & _
  4.            "Key=" & .Key & vbNewLine & _
  5.            "Tag=" & .Tag
  6.   End With
  7. End Function
Sample Calls to Function:
Expand|Select|Wrap|Line Numbers
  1. Call fSomePublicFunction("frmtreeView2", "TreeView1")
Expand|Select|Wrap|Line Numbers
  1. Call fSomePublicFunction("Form1", "tvwCustomers")
Expand|Select|Wrap|Line Numbers
  1. Call fSomePublicFunction("frmDemo", "MyTreeView")
Expand|Select|Wrap|Line Numbers
  1. etc...
Mar 9 '11 #4
jimatqsi
1,258 Expert 1GB
Adezii,
Okay, I have been able to completely generalize this. Here's how I did it.

I have a one-time only function to build the context-menu. It looks like this:
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateNodeContextMenu()
  2.  
  3.    On Error GoTo CreateNodeContextMenu_Error
  4.  
  5.    Dim cmbShortcutMenu As Office.CommandBar
  6.  
  7.    CommandBars("TreeViewContextMenu").Delete
  8.     ' Create a shortcut menu named "SimpleShortcutMenu".
  9.     Set cmbShortcutMenu = CommandBars.Add("TreeViewContextMenu", _
  10.                         msoBarPopup, False, False)
  11.   With cmbShortcutMenu.Controls.Add(msoControlButton)
  12.       .Caption = "Show Node Details"
  13.       .OnAction = "PopUpNodeDetails"
  14.     End With
  15.  
  16.     With cmbShortcutMenu.Controls.Add(msoControlButton)
  17.       .Caption = "Delete Node-"
  18.       .OnAction = "PopUpNodeDelete"
  19.     End With
  20.  
  21.     Set cmbShortcutMenu = Nothing
  22.  
  23.   On Error GoTo 0
  24.    Exit Sub
  25.  
  26. CreateNodeContextMenu_Error:
  27.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateNodeContextMenu of Module ContextMenu_bas"
  28.     Resume Next
  29. End Sub
I ran that from the immediate window in the debugger.

In the MouseUp event of the TreeView object I have this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TreeView1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Long, ByVal y As Long)
  2.    On Error GoTo TreeView1_MouseUp_Error
  3.  
  4.   If Button = acRightButton Then
  5.     CommandBars("TreeviewContextMenu").ShowPopup
  6.   End If
  7.  
  8.    On Error GoTo 0
  9.    Exit Sub
  10.  
  11. TreeView1_MouseUp_Error:
  12.  
  13.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TreeView1_MouseUp of VBA Document Form_TreeViewForm"
  14.     Resume Next
  15. End Sub
  16.  
I have a macro named PopupNodeDetails. It does RunCode() with parameter = ShowNodeDetails ()

ShowNodeDetails is a public function that looks like this:
Expand|Select|Wrap|Line Numbers
  1. Public Function ShowNodeDetails()
  2.  
  3.    On Error GoTo ShowNodeDetails_Error
  4.  
  5.     With Screen.ActiveControl.SelectedItem
  6.         MsgBox "Text=" & .Text & vbNewLine & _
  7.             "Key=" & .Key & vbNewLine & _
  8.             "Tag=" & .Tag
  9.     End With
  10.  
  11.    On Error GoTo 0
  12.    Exit Function
  13.  
  14. ShowNodeDetails_Error:
  15.  
  16.     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowNodeDetails of VBA Document Form_TreeViewForm"
  17.     Resume Next
  18. End Function
So all I have to do to add this context menu to any TreeView in the same DB is to paste in the MouseUp event code.

Thanks for helping me work through this.

Jim
Mar 11 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Tom Chidwick | last post: by
1 post views Thread by deancarstens | last post: by
reply views Thread by Craig Martin | last post: by
6 posts views Thread by darren via AccessMonster.com | last post: by
3 posts views Thread by e name | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.