Custom Shortcut Menu: List of msoControlButton Values? Also Question on "Remove Filter/Sort"

[DittoBird]

Hi folks,

Using MS Access 2016. I'm trying to find a list of msoControlButton values to use VBA to create one or more custom shortcut menus. To make it clearer, I'm talking about the lines used in the code, below, for example ".Controls.Add msoControlButton, 141, , , True". The number 141 sets up the Find command.

I haven't done much on shortcut menus since A2003. While I've done some work in A2007 using XML nearly a decade ago, none of which I recall and I don't have access to my code from then. I am using the code below, which was garnered from https://docs.microsoft.com/en-us/off...trol-or-report. I'd really like to find a number of commands, especially one that I used a lot in A2003 shortcut menus, the "Filter" with a blank space to enter whatever you wished, including wild cars plus "and" and "or".

Option Compare DatabaseOption Explicit

'Based on https://docs.microsoft.com/en-us/office/vba/access/concepts/miscellaneous/create-a-shortcut-menu-for-a-form-form-control-or-report
Sub CreateShortcutMenuWithGroups()
    Dim cmbRightClick As Office.CommandBar
 
 ' Create the shortcut menu.
    Set cmbRightClick = CommandBars.Add("cmdFormFiltering", msoBarPopup, False, True)
     
    With cmbRightClick
        ' Add the Find command.
        .Controls.Add msoControlButton, 141, , , True
        
         ' Add the copy command.
         .Controls.Add(msoControlButton, 19, , , True).BeginGroup = True
        
        ' Add the paste command.
        .Controls.Add msoControlButton, 22, , , True
         
        ' Start a new grouping and add the Sort Ascending command.
        .Controls.Add(msoControlButton, 210, , , True).BeginGroup = True
         
        ' Add the Sort Descending command.
        .Controls.Add msoControlButton, 211, , , True
         
        ' Start a new grouping and add the Remove Filer/Sort command.
        .Controls.Add(msoControlButton, 605, , , True).BeginGroup = True
         
        ' Add the Filter by Selection command.
        .Controls.Add(msoControlButton, 640, , , True).BeginGroup = True
         
        ' Add the Filter Excluding Selection command.
        .Controls.Add msoControlButton, 3017, , , True
        
    End With
 
    Set cmbRightClick = Nothing
End Sub

I've tried Micron's code he researched in post #11 in this link: https://www.accessforums.net/showthread.php?t=68045; this was the only hit I got that came close! Unfortunately, after waiting about 15 to 20 minutes for it to run on Excel 2016, it gave me the codes but just the picture of the command.



----------
Also, quick question on removing a sort. Using the menu I generated above, I have sorted, but not used filters, on the portion of a form view below. The Remove Filter/Sort command is not enabled. It is enabled when I apply a filter. Is there any way to remove the sort with a shortcut menu command and go back to whatever the sort is on the SQL? I'm not sure how to apply the VBA set me.OrderByOn = False and me.OrderBy = "" in a post Access 2003 world. Sorry, I'm having extreme difficulty with images.



Thank you in advance,
--
Tim

PLEASE IGNORE THESE IMAGES FOLLOWING - I COULD NOT GET RID OF THEM AND THE FORUM WOULD NOT ALLOW ME TO DELETE THIS POST TO SDTART AGAIN

SORRY FOR THE CONFUSION

[Micron]

Can't tell if your sort command made any difference but I would try removing the sort from the query - I cannot recall which takes precedence but I have read where sorts didn't work at all and that was the reason reported.
Impossible to read your code from that image. Posting code within code tags is preferred, especially because if anyone wishes to test for you, it's unlikely they would be willing to type out reams of code contained in a pic when you could simply post it.

Consider giving up the fight that ensues when trying to avoid using the ribbon for simple user actions.

[DittoBird]

Can't tell if your sort command made any difference but I would try removing the sort from the query - I cannot recall which takes precedence but I have read where sorts didn't work at all and that was the reason reported.
Impossible to read your code from that image. Posting code within code tags is preferred, especially because if anyone wishes to test for you, it's unlikely they would be willing to type out reams of code contained in a pic when you could simply post it.

Consider giving up the fight that ensues when trying to avoid using the ribbon for simple user actions.

Thank you. I do apologize for the confusion. I could not delete the photos that I accidentally added early on in composing the message and then could not delete this message to try again. I'd only meant the single screen showing the behaviour of the Remove Filter/Sort command. I could not get rid of attachments below my name that show two other screen shots. One is a repeat of the the first screen shot, the other is basically my user screen and the VBA window of the very same code I show at the beginning. I added both of these accidentally as an attachment, instead of using the Insert Image button. Neither should be there.

WRT the ribbon, this is a very simple application to collect equipment information, deployed as an accde. Other people using it and I will be deploying to the field with very small laptops and I'd rather not use the ribbon as it takes up far too much real estate. All I have is a splash form, then a "pick site" (as in search and rescue stations, lighthouses, storage sites, cabins deep in the woods, landing sites, etc) and display the main form to enter all the various equipment details either in the field or back at the office using photos of name plates, engineering drawings, regulatory inventories, and so on. It's meant to be a data gathering tool to eventually import the Access data into an SAP S/4HANA back end.

As far as sorting goes, that's interesting, I will look into it further. I've never had an issue with including an Order By in queries I create for a form based on users' choices. Mind you, the vast majority of my work experience is with native Oracle SQL (I stopped using linked tables to work with Oracle in the late 1990s after gaining a couple of years experience using it with Access and dealing with a vendor's badly designed number fields that translated as text fields via ODBC linked tables ). I used DAO and ODBC to create numerous queries as well as execute PL/SQL. But I've done a lot of Jet as a back end with my hobbies at home and haven't had an issue with the same approach. Mind you, I don't recall the issue here with the remove/filter sort - I must check tonight) THAT SAID...

Do you think it's better to simply pass the SQL or actual query without an order by clause and instead use OrderBy = "whatever1, whatever 2" ?

Again sorry for the confusion I caused.
--
Tim

[Micron]

IIRC, you have to use "Go Advanced" button and somewhere below your post is a link/button to "Manage Attachments" and they are listed below. You click the one you want to remove but what's not terribly obvious is that at the top right of the attachment icon a checkbox appears, which you have to select. That's all I can recall about that.

Re your question, hard to advise whether or not to impose a sort in the query or not - can't see your db. You might want an initial sort, or might not have another means beyond context menus. If your form/subform is a datasheet, one way is to put command buttons on top of the fields and sort by the field below a button, which I have done but that's a 1-field sort (unless you used toggle buttons, which I've never done but it came to mind).

When code doesn't work as expected, always step through it and monitor what's going on in the hopes it reveals what's being bypassed and what happens to properties when it does. It's almost as if your sort isn't actually applied, which I asked about but....? Last but not least, if you're using pass through query, the resulting recordset was processed at the server end. I don't know how that is handled when you try to manipulate it when it gets back in to Access. If you're going to have issues with linked table field data types, an option might be to import a query result into a staging table and sort on that.

[orange]

I saw this recently and it may offer some help/insight.