Solved

ShortCut menu problem in datasheet - Access 2010

Posted on 2013-12-12
8
1,465 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
  • 4
  • 3
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
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: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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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

15 Experts available now in Live!

Get 1:1 Help Now