Need to recreate the right-click subdatasheet menu in MS Access 2010 Runtime


I have some MS Access 2010 applications that use large sub-datasheets where users are able to hide/unhide the columns of their choice.  

This worked fine until some of them have been forced to use MS Access 2010 Runtime, and now the right-click menus are gone.

Can anyone tell me how to recreate the right-click menu like the one attached in VBA?

Thanks in advance!

Stu GilmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
for some reason, never have figured out why, MS decided to disable the right click menus when running code in the runtime version.

I have had some luck playing with this, and generally use the mouseup event of various form objects to detect whether the right mouse button was pressed.

The first step is to identify exactly which right click menu you want to implement.  To do this, add this code to your application and call the subroutine to add an option ("What's This")to the bottom of almost every shortcut menu.
Public Sub WhatsThis()

   Dim cbr As CommandBar
   Dim cbtn As CommandBarButton
   On Error GoTo ProcError
   For Each cbr In Application.CommandBars
        With cbr
            Set cbtn = cbr.Controls.Add(1, , , , True)
            cbtn.BeginGroup = true
            cbtn.Caption = "What's This"
            cbtn.OnAction = "WhatsThisBar"
        End With
    Exit Sub
    'Debug.Print cbr.Name, Err.Number, Err.Description
    Resume NextCBR
End Sub

Public Sub WhatsThisBar()

    Debug.Print CommandBars.ActionControl.Parent.Name
    msgbox Commandbars.ActionControl.Parent.Name

End Sub

Open in new window

These two subroutines will allow you to select the "What's This" option to see the name of the commandbar you want to implement.  Then, depending on where you want to implement the code, you can add code to the MouseUp event that will call that particular shortcut menu.

In order to implement this, you will need to set the Shortcut Menu property of all of your forms to No, and add this code or similar code whereever you need this functionality.  Take a look at the sample attached.
Stu GilmanAuthor Commented:
Thanks Dale!  I'll try this out today and let you know how things go...

Dale FyeOwner, Developing Solutions LLCCommented:
It is not a trivial task to get that functionality everywhere you would normally see it.  

Someone mentioned I needed to create a new class for textboxes and other controls, so that I would not have to put code in the MouseUp of every form or control where you simply want to implement right click menus, but I've never gotten around to doing that.
10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

Stu GilmanAuthor Commented:
Hi Dale,

This does not appear to be working as hoped.

I've added this code to the subform:

    If Button = acLeftButton Then Exit Sub
    If (Me.SelHeight = Me.RecordsetClone.RecordCount) And (Me.SelWidth = 1) Then
        CommandBars("form datasheet column").ShowPopup
    ElseIf (Me.SelWidth = Me.RecordsetClone.Fields.Count) Then
        CommandBars("form datasheet row").ShowPopup
    End If

In order to get the second if statement to execute the CommandBars("form datasheet column").ShowPopup, I had to eliminate the +1 that you had in your code following the RecordCount statement.

The CommandBars statement does show the menu shown in the attachment, but all the entries are grayed-out.  Do you know why this is?

Also, the Sort Ascending/Descending and Width commands that are typically found in the right-click menu for the datasheet are not appearing.

What might I be doing wrong?


Dale FyeOwner, Developing Solutions LLCCommented:

Turns out that if the recordsource for your datasheet contains more fields than you have actually added to your datasheet form, then that 2nd check will not work properly.  You are going to have to change the 2nd part of the IF statement to:

ElseIf Me.SelWidth = NZ(DatasheetColumnCount(Me), 0) Then

Open in new window

and then, use the following function to determine the actual # of columns in the datasheet.
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
            'If ctrl.ColumnHidden = False Then
                intCtrlCount = intCtrlCount + 1
        End If
    DatasheetColumnCount = intCtrlCount
End Function

Open in new window

I have not played with this much, but I would probably put a form level variable in my form and call this function to set it's value in the Form_Load event.  Then, instead of running the function every time the MouseUp event is called, you could replace that 2st line of code above with:

ElseIf Me.SelWidth = intDatasheetColumnCount Then

Open in new window

Stu GilmanAuthor Commented:
Hi Dale,

Thanks again for your reply.

My message wasn't clear about the issue that I'm dealing with.

I added your original code, and by eliminating the +1 in the statement:

    If (Me.SelHeight = Me.RecordsetClone.RecordCount) And (Me.SelWidth = 1) Then

it executes the CommandBars("form datasheet column").ShowPopup properly, but all the entries in the popup menu except the Copy command are grayed-out.  That is what I'm really trying to figure out. (See attachment)

Do you know why I'd get the popup to come up, but it won't let me select any of the items except the Copy entry?

I"m right-clicking on the column behind the popup menu, and would like to be able to Hide it, as well as Unhide any fields that I may have chosen to Hide previously.

Thanks in advance,

Dale FyeOwner, Developing Solutions LLCCommented:
Right click menus are context sensitive and are, in my experience fickle!

Are you certain you don't have any code that is disabling those options?

if you go to the VBA code window and search your entire project for:

Commandbars("form datasheet column")

See if there is any code that is disabling those controls.  Then try the attached file and see what it does for you in runtime mode.
Stu GilmanAuthor Commented:
I tried a clean copy of my program and added this to the sub-datasheet:

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

    If Button = acLeftButton Then Exit Sub
    If Me.SelHeight = Me.Count Then
        CommandBars("form datasheet column").ShowPopup
    ElseIf Me.SelWidth = DatasheetColumnCount(Me) Then
        CommandBars("form datasheet row").ShowPopup
    End If
End Sub

And when I run without the /Runtime switch, I get the full context menu (Attached), and when in Runtime mode, no menu appears at all!

What next?
Dale FyeOwner, Developing Solutions LLCCommented:
Did you try running the application I sent you?  It was showing the filter options when run in my 2010 (x64) environment.
Stu GilmanAuthor Commented:
I did run your application, and it worked fine.

I have no idea why mine doesn't. Very strange that adding the /Runtime suppresses any menu from showing up, and because of the switch, I have no way to debug it.

Are there any special things for me to look for in terms of the subform properties settings?
Dale FyeOwner, Developing Solutions LLCCommented:
1.  did you run mine with the /runtime switch?

2.  Are there any special things for me to look for in terms of the subform properties settings? Not that I know of.  Have you looked at the forms Shortcut Menu Bar property?
Stu GilmanAuthor Commented:
I did.

Also, just imported your forms and code into my application, and was able to see the right-click menu just as I want to in my subforms, even with the /Runtime switch.

Not sure what the issue is with my subforms that are different than yours.
Dale FyeOwner, Developing Solutions LLCCommented:
I've seen situations where two subforms created at different times, but with all other properties set the same respond differently.

You might want to try recreating the datasheet form from scratch.  Or use one of mine and change the datasource and add your own controls.

Like I said, they tend to be fickle.  One of the reasons I tend to create my own shortcut menus.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stu GilmanAuthor Commented:
Thanks - will keep you posted!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.