Link to home
Start Free TrialLog in
Avatar of Stu Gilman
Stu GilmanFlag for United States of America

asked on

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

Hi,

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
Capture.PNG
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
        
NextCBR:
    Next
   
ProcExit:
    Exit Sub
    
ProcError:
    '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.
DatasheetCommandbars.accdb
Avatar of Stu Gilman

ASKER

Thanks Dale!  I'll try this out today and let you know how things go...

Stu
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.
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?

Thanks,

Stu
Capture2.PNG
Stu,

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
            'endif
        End If
        
    Next
    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

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,

Stu
Capture2.PNG
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.
Runtime-Datasheet-Test.accdb
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?
Capture3.PNG
Did you try running the application I sent you?  It was showing the filter options when run in my 2010 (x64) environment.
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?
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks - will keep you posted!