?
Solved

Running a public form function (via shortcut menu)

Posted on 2016-10-25
13
Medium Priority
?
37 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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