none
How to recreate built-in CommandBarPopup in Access for Runtime RRS feed

  • Question

  • (You can find this question also on Stack Overflow)

    Currently I'm developing an application with Access 2013/2016. This software should be runnable on client machines with the Access RUNTIME engine only. Now i noticed that all Ribbons/Context Menus are deactivated in RUNTIME mode.

    Now I'm trying to create the necessary context menus by myself. This article helped a lot, but I have a problem with CommandBarPopups.

    I'm trying to create the same context menu, that you can see in table cells/input fields on forms. In that, there is a very nice filter button/popup, which consider the underlying data type of the clicked cell. For Date types it shows "Date Filters", for Text types "Text Filters" and so on...

    I found the internal control Id of this menu item (31581), but it doesn't show all the related sub-menu items of CommandBarPop. Then I tryied to include this sub-items manually, but then it will be shown all the sub-items all the time. The standard context menu is clever enough, to hide all unrelated sub-items (e.g. for Text types all the Date menus like Next Month, etc... )

    This is my code until now. The commented lines are my problem.

    Public Function CreateShortcutMenus()
    
    On Error Resume Next
    CommandBars("cmdFormFiltering").Delete
    On Error GoTo 0
    
    
    ' Create the shortcut menu.
    Dim cmdFormFiltering As Office.CommandBar
    Set cmdFormFiltering = CommandBars.Add("cmdFormFiltering", msoBarPopup, False, True)
    
    With cmdFormFiltering
        ' Add the Find command.
        .Controls.Add msoControlButton, 141, , , 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 FilterBySelection
        .Controls.Add(msoControlButton, 640, , , True).BeginGroup = False
    
        Dim popUpFilter As Office.CommandBarPopup
        Set popUpFilter = .Controls.Add(msoControlPopup, 31581, , , True)
        popUpFilter.BeginGroup = True
    
    ' popUpFilter.Controls.Add(msoControlButton, 10077, , , True).BeginGroup = False
    ' popUpFilter.Controls.Add(msoControlButton, 10078, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10079, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 12696, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10080, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10081, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10088, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 12697, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 12698, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 12699, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10082, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10083, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10062, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10063, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10064, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10065, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 16206, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10067, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10066, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10058, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10069, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10070, , , True).BeginGroup = False
    '        popUpFilter.Controls.Add(msoControlButton, 10059, , , True).BeginGroup = False
    
        ' Add the Filter FilterEqualsSelection
        .Controls.Add(msoControlButton, 10068, , , True).BeginGroup = True
    
        ' Add the Filter FilterNotEqualsSelection
        .Controls.Add msoControlButton, 10071, , , True
    
    
    End With
    
    Set cmdFormFiltering = Nothing
    
    End Function

    What have I todo, that automatically all sub-items will be shown with control id 31581?

    Regards,
    Daniel

    Tuesday, August 16, 2016 12:13 PM

Answers

  • Hi Daniel,

    as it is your custom code to call this function. you have to check the value and identify it programmatically.

    then you can enable the options that are related only with that data and other options will be disabled.

    other then we can't do anything in Access Run Time.

    you can create a function that can find and differentiate the data in the cells of the table.

    you can try to use the code below. it can identify the numeric datatype in the table.

    for other datatypes you have to modify the code.

    Sub dem()
    
    Dim intNumberofFields As Integer, intFieldType As Integer, strTypeName As String
     
     Dim fld As Field, intCounter As Integer, strFieldName As String
     
     intNumberofFields = CurrentDb.TableDefs("ToraPurches").Fields.Count
     
     For intCounter = 0 To intNumberofFields - 1
       strFieldName = CurrentDb.TableDefs("ToraPurches").Fields(intCounter).Name
       intFieldType = CurrentDb.TableDefs("ToraPurches").Fields(intCounter).Type
         Select Case intFieldType
           Case 2    'Byte
             strTypeName = "Byte"
           Case 3    'Integer
             strTypeName = "Integer"
           Case 4    'Long
             strTypeName = "Long"
           Case 6    'Single
             strTypeName = "Single"
           Case 7    'Double
             strTypeName = "Double"
           Case Else 'Not a Number
             strTypeName = "N/A"
         End Select
          Debug.Print Format(intCounter + 1, "00") & ") " & strFieldName & " - " & strTypeName
     Next intCounter
    
    End Sub
     

    Find Field Data Type in VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, August 17, 2016 5:30 AM
    Moderator