<

Using Shortcut (right-click) menus in the Access runtime environment

Published on
8,882 Points
2,282 Views
1 Endorsement
Last Modified:
This article will show you how to use shortcut menus in the Access run-time environment.

Background:

Right-click (shortcut) menus are an integral part of Windows applications and users expect them to be available in their custom Access applications. I have historically used these menus in my forms and reports to customize my applications and reduce screen clutter. In versions of Access prior to 2007 there was a built-in mechanism for creating these shortcut menus, but when Microsoft released Access 2007, this feature had been deprecated (fancy term for removed), and has not been restored.

To resolve this issue, in my applications, I started writing VBA code to build my menus, but the syntax is complicated and can be especially difficult when you start adding embedded submenus. Because of this, I started to develop a tool for creating these menus. As I was doing my research, I realized that there was not much written on this topic, so I created a series of articles about commandbars (shortcut menus). The first three articles, Understanding and using Commandbars, Creating your Own, and Using Built-in Menus of this series were written as I worked on developing the Access Shortcut Tool.

As I was demonstrating that tool to a bunch of Access experts, someone asked me about using shortcut menus in the run-time environment. I had always worked in environments where all my users had the Professional version of Office, so I had never encountered this problem and was unable to answer the questions. After the demonstration, I went back to my office, did some testing, and confirmed that for some unexplained reason, Microsoft has disabled the built-in right-click menus in the run-time environment. While I found this extremely strange, and have never received an answer as to why this is the case, my research showed that this limitation can be overcome relatively easily.
 

How to use Shortcut Menus in your applications:

Almost all Access objects (tables, forms, queries, reports, and controls) have built-in shortcut menus associated with them; many of these have different menus for different purposes (design, view, layout, preview, and datasheet). None of these built-in menus will work automatically (the way they do in an .accdb or .accde file) in the run-time. However, almost all of these objects also have a Shortcut Menu Bar property which can be set at design time. The down side is that you cannot simply enter the name of one of the Built-in menus into the Shortcut Menu Bar property, you must create your own menus and use them.

The first two articles in this series describe how to do this, so once you have created your own menu, you simply need to assign that menu to each of the controls on your form. You might also want to assign a custom shortcut menu to your forms Shortcut Menu Bar property; I usually do this even in my non-runtime applications so that the users don't see the built-in form menu when they click somewhere on my forms, outside of a control. My custom form shortcut menu generally just contains a 'Help' option which takes them to the help description for the active form.
 

Datasheets:

The only aspect of adding shortcut menus to a form where I have found some difficulty is when working with datasheets.  Datasheets have several built-in shortcut menus associated with them 'Form Datasheet Row', 'Form Datasheet Column', 'Form Datasheet Subcolumn', and 'Form Datasheet Cell'. Although you could configure your datasheet to use a single shortcut menu by assigning a value to the forms Shortcut Menu Bar property, I've found another technique which allows me to use all of the shortcut menus associated with the datasheet, without actually having to copy or recreate those menus manually. This technique uses the datasheet form's MouseUp event to determine whether the right mouse button was clicked.


Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

   If Button = acRightButton Then DatasheetPopups Me, intColCount

End Sub

Open in new window

image.gifimage.gif If so, the MouseUp event calls the DatasheetPopups procedure which determines how many rows, or columns are selected and uses the ShowPopup method to display the appropriate shortcut menu:


Public Sub DatasheetPopups(frm As Form, ColCount As Integer)

   Dim strMenu as string

   If (frm.SelHeight = 0) And (frm.SelWidth = 0) Then
      strMenu = "form datasheet cell"
  ElseIf (frm.SelHeight = frm.Recordsetclone.RecordCount) AND (frm.SelWidth = 1) Then
      strMenu = "form datasheet column"
   ElseIf frm.SelWidth = ColCount Then
      strMenu = "form datasheet row"
   End If

   IF Len(strMenu) > 0 then 
      commandbars(strMenu).showpopup
   Else
      msgbox "invalid selection"
   End If

End Sub

Open in new window

image.gifimage.gif Note that this procedure requires a ColCount argument, which is the total number of columns in the datasheet; this value is needed to determine whether an entire row (or multiple rows) has/have been selected in the datasheet. However, because a form's recordset may contain fields which are not actually displayed on the form, you cannot simply use the recordset.fields.Count property to determine how many controls are displayed in the datasheet. In order to get this value, you must iterate through the controls on the form and count the non-label controls. I generally declare a variable (intColCount) as an Integer in the forms declaration section, and then call the DatasheetColumnCount function in the datasheets Form_Load event.


Private Sub Form_Load(Cancel As Integer)

   'intColCount is declared as Private in the forms declaration section
   intColCount = DatasheetColumnCount(Me)

End Sub

Open in new window

image.gifimage.gif The DatasheetColumnCount function looks like:


Public Function DatasheetColumnCount(frm As Form) As Variant

   Dim ctrl As Control
   Dim intCtrlCount As Integer

   DatasheetColumnCount = Null

   If frm.CurrentView <> 2 Then Exit Function

      For Each ctrl In frm.Section(acDetail).Controls

   If ctrl.ControlType <> 100 Then
      intCtrlCount = intCtrlCount + 1
   End If

   Next
   DatasheetColumnCount = intCtrlCount

End Function

Open in new window

image.gifimage.gif

Summary:

Although this process can be time consuming to configure, Access users expect to be able to use right-click menus in their applications. If you are not using these menus in your applications, give it a try. It is not difficult to do with the right tool in your toolbox.
 

About the Author:

Dale Fye has been developing with Microsoft Access since 1995 and is a Microsoft Access MVP.
1
Comment
Author:Dale Fye
1 Comment
 
LVL 14

Expert Comment

by:Bill Ross
Hi Dale,

Do you have code to duplicate the built-in commandbars?  I'm interested in  the Text Filters and Number Filters that leads the users to a dialog box for input.  I've got the Sort and other items done, just the sub-menu process is stumping me.

Thanks,

Bill
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…
See the Basics of Office 365's Note Taking app, OneNote

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month