This article will show you how to use shortcut menus in the Access run-time environment.
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,
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.
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
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"
IF Len(strMenu) > 0 then
msgbox "invalid selection"
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)
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
DatasheetColumnCount = intCtrlCount
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:
has been developing with Microsoft Access since 1995 and is a Microsoft Access MVP.