Solved

Running a public form function (via shortcut menu)

Posted on 2016-10-25
13
15 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
  • 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
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now