?
Solved

ShortCut menu problem in datasheet - Access 2010

Posted on 2013-12-12
8
Medium Priority
?
1,609 Views
Last Modified: 2014-02-13
Hi all

This problem has persisted from Access 2003 to Access 2010

I have a custom shortcut menu that I assigned to the Shortcut Menu Bar of a subform object that is displayed in datasheet view on another form
The correct shortcut menu is shown when the user right clicks on a cell in the datasheet.  However, if the user right clicks on the heading of a column or selects an
entire column of the subform, the standard shortcut menu is shown.

How do I override the standard shortcut menu that is shown when you right click on a
column heading or selects the entire column in datasheet mode on my subform

I only want my custom shortcut menu to appear when the user right clicks ANYWHERE on the subform

Thanks
H
0
Comment
Question by:HenryV1955
[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
  • 4
  • 3
8 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39715405
You cannot, to the best of my knowledge, disable the any of the datasheets various shortcut menus and still implement your custom menu.

However, you could hide all of the menuitems associated with each of those menus, and add your own  menu items to those commandbars.  Did you create your custom shortcut menu using VBA, or the old 2003 Customize dialog box?
0
 
LVL 75
ID: 39715832
I agree in that I've never been able to find a way to hide the Right Click menu you speak of.
I suspect however, that I could be deleted from the CommandBars collection - which for me would not be a big deal since I have no use for that menu. Actually, you may be able to Hide and/or Disable this menu.

You can used Steven Leban's CommandBars tool to display ALL CommandBars in a given db

http://www.lebans.com/commandbars.htm

mx
0
 

Author Comment

by:HenryV1955
ID: 39716784
Hi fyed and DatabaseMX

Thanks for both your replies.

Yes did create my shortcut menu using VBA in Access 2010

I'll look into your comments and Lebans website and get back to you

H
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39716820
I've got some other code that will help you identify which built-in shortcuts you need to look at, and some code that will hide/reveal (.visible = true/false)  all of the items in one of the standard commandbars.  Unfortunately, I cannot get to it at the moment.

BTW, you can add items to those standard commandbars at runtime, so you could simply change the code you used to create your commandbar, point it to the standard command bar and add your own controls.

In the datasheets where I do that, I use some code in the forms OPEN event to hide the standard shortcut items and reveal mine.  Then, in the Form_Close event, I reverse that process and hide my items and reveal the original items.
0
 

Author Comment

by:HenryV1955
ID: 39721536
HI fyed

I would appreciate your code on how to hide/reveal the items in the standard shortcut menu, whenever you get the chance

Thanks
H
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1500 total points
ID: 39730886
@Henry,

I'm not sure which Shortcut menu you are having difficulties with.  I've got an article that is about to be published on this topic, but here is some of what you need to know.  To determine the possible shortcut menus you need to "disable", use the following subroutine and pass it the value "datasheet"
Public Sub CbrShortCutMenus(Optional SearchFor As String)

    Dim cbr As Object
    Dim intCount As Integer
    
    For Each cbr In Application.CommandBars
        If cbr.Type = 2 Then
            If SearchFor = "" Then SearchFor = " "
            If InStr(cbr.Name & " ", SearchFor) > 0 Then Debug.Print cbr.Name
        End If
    Next

End Sub

Open in new window

This will give you a list (in the debug window of the various datasheet shortcutmenus).  My guess is that it will be one of the following:
Form Datasheet Cell
Form Datasheet Row
Form Datasheet Column
Figure out which of these you want to disable, then run the following code to identify the captions for each of the menu items in the menu to "disable".
Public Sub CbrMenuItems(CommandBarName As String)

    Dim intLoop As Integer
    Dim ctrl As Control
    
    On Error Resume Next
    For intLoop = 1 To CommandBars(CommandBarName).Controls.Count
        Debug.Print CommandBars(CommandBarName).Controls(intLoop).Caption;
    Next
    
End Sub

Open in new window

Finally, create a subroutine in your form that you can call from the Form_Open and Form_Close events, something like:
Private Sub HideRevealFormDatasheetRowMenuItems(Optional Hidden as boolean = false)

    With Commandbars("Form Datasheet Row") 
        .Controls("Ne&w Record").visible = NOT Hidden
        .controls("Delete &Record").visible = NOT Hidden
        'Add more lines for the the rest of the menu items you want to hide/reveal
    End With

End Sub

Open in new window

Then in the Form_Open event, use:
HideRevealFormDatasheetRowMenuItems Hidden:=true

Open in new window

and in the Form_Close event, use:
HideRevealFormDatasheetRowMenuItems Hidden:=false

Open in new window

0
 

Author Closing Comment

by:HenryV1955
ID: 39856274
Thank you
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39857535
Glad I could help.

Here is the article I mentioned above.  With some sample code in the article and a database attached with additional code.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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