Solved

ShortCut menu problem in datasheet - Access 2010

Posted on 2013-12-12
8
1,572 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 (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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 48

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
 

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 (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 48

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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