Solved

Running a public form function (via shortcut menu)

Posted on 2016-10-25
13
33 Views
Last Modified: 2016-10-25
I need some help with a shortcut menu that is being created via VBA.  I want it to run a function within an open form, but for some reason can't get it to work.

Here is the test code that I have written within a module to create the shortcut menu:

Public Function SCTest()

    Dim cmbShortcutMenu As office.CommandBar
    Dim ctlCBarControl As CommandBarControl
    Set cmbShortcutMenu = CommandBars.Add("Shortcut_Test", msoBarPopup, False, False)

'Show selected outcomes
    Set ctlCBarControl = cmbShortcutMenu.Controls.Add(msoControlButton)
    With ctlCBarControl
       .Caption = "Test"
       .OnAction = "=forms!frmSCTest.SC_Function()"
       .Tag = .Caption
    End With
    Set cmbShortcutMenu = Nothing

End Function

Open in new window


Within the form "SCTest", I have created the following function

Public function SC_Function()
     msgbox "Hello"
End function

Open in new window


I have added a Textbox, set the shortcut menu property and when I right-click, select "Test"  I'd like it to trigger SC_Function() - but it doesn't.  I'm guessing I have something wrong with the .onaction syntax, but haven't been able to solve it.
 
Any ideas what I'm doing wrong?
0
Comment
Question by:Andy Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41858795
You need to call you function in VBA code like below ...

Call SC_Function()


End If
0
 
LVL 75
ID: 41858932
The OnAction syntax is:

=SC_Function()

and SC_Function must be  a Public Function in a Standard VBA Module, not behind a Form.
0
 

Author Comment

by:Andy Brown
ID: 41859005
Hi guys - thanks for the help.

Is there any way I can get it to call the code that is within the form (rather than a module)?  Moving the existing code would take longer than it's worth.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41859028
if you are going to use/call the function ONLY in this Form, you can place the code in the Form's module,
but remember that you can NOT use the function from any other form.
0
 

Author Comment

by:Andy Brown
ID: 41859032
That's exactly what I wanted Rey - thank you.  However, I can't seem to get the syntax right (see code above).
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 41859071
Do you have this in the OnAction

=SC_Function()

?

And again ... SC_Function() cannot be in code behind a Form .... must be in a Standard VBA Module.
However, from there ... you can call code behind a Form.
0
 

Author Comment

by:Andy Brown
ID: 41859081
Ah - so if I create a function in the module, it can call the function behind the form?
0
 

Author Closing Comment

by:Andy Brown
ID: 41859087
That worked - thank you.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859095
<SC_Function() cannot be in code behind a Form >  NOT true

try this sample db
SampleFormFunction.accdb
0
 

Author Comment

by:Andy Brown
ID: 41859106
Thanks again Rey / everyone.
0
 
LVL 75
ID: 41859107
Rey ... I don't see any Menu Bar or Right Click menu ??
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859116
I just showed you that you can place a function in the form's module.
0
 
LVL 75
ID: 41859130
But I'm talking about calling a Function in a Form Module from a Shortcut (aka Right Click) or legacy Menu Bar OnAction property.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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