Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access 2003 - Custom Menu - Opening Form

    I know that when you're in menu "Customize" mode, you can drag a form from a list of forms onto a menu bar or sub menu and that will provide you with a menu option that will open the form.

    But is it possible to do the same thing from code?

    With cb set up as a CommandBar object I can execute

    Set cbrButton = cb.Controls.Add(msoControlButton, , "dlgFormName")
    cbtButton.Caption = "dlgFormName"

    and, barring the icon, the resulting menu item looks like, and at first glance appears to have the same properties as, one created by dragging the form onto the menu. But this one doesn't do anything. It doesn't error, but nothing happens when you click on it.

    A closer look at the properties in code and I can find two differences. One is that the one I created in code has a BuiltIn value of false whereas the one dragged there has a BuiltIn value of true. But this is a readonly property, so I can't explicitly set it in code.

    The other property is the ID. While the one created in code has the default ID of 1, the ID of the dragged item is 1837. Now I know that you can add built-in items to custom menus by using the ID parameter in the Add command, but adding with an ID of 1837 produces an automation error when I try it. So something like this, for example

    Set cbrButton = cb.Controls.Add(msoControlButton, 1837, "dlgFormName")
    cbtButton.Caption = "dlgFormName"

    doesn't work.

    Does anyone know if what I'm trying to do is possible and, if so, what the trick is?
    Last edited by SimonC; 2016-03-28 at 22:28.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Simon,

    Try adding:
    Code:
    cbtButton.OnAction = "ProcedureName"
    Then in a Standard Module add a subroutine:
    Code:
    Sub ProcedureName()
    
       DoCmd.OpenForm "dlgFormName", acNormal
    
    End Sub 'ProcedureName
    Of course replacing all the items in blue with the appropriate values.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks RetiredGeek, I'd considered that as a possibility but this is intended to solve a problem on one particular PC and I was hoping not to make any changes to the application.

    The reason I'm trying to do this is that just one computer, of a number that run this database, seems to have developed a problem whereby it has an issue with the existing menu which, on that computer alone, keeps disappearing. I've tried recreating the menu by hand and from scratch and that seems to work, but so far I've only created a much simpler version of the menu with fewer options. So I was just experimenting with a way of recreating the full menu to see whether, if created anew on that machine, that would also stay put.

    If I knew that it would, then it would be worth the effort re-creating it manually. But as I don't it would be very helpful to have a way of easily re-running the creation while I experiment and see if perhaps the problem can be pinned down to any particular part of the menu.

    EDIT: I guess I could get away with having a single generic function, along the lines of

    Code:
    Public Function MenuOpenForm(ByVal FormName As String)
        DoCmd.OpenForm FormName, acNormal
    End Function
    and making the OnAction property equal to

    Code:
    =MenuOpenForm("dlgFormName")
    By the way, I've noticed that the same problem affects any reports run straight from the menu, but the same kind of solution would work there too.
    Last edited by SimonC; 2016-03-29 at 06:32.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Simon,

    It's always been my practice to create all menus in code. That way every time the application is used the menu is recreated and any thing the user might be doing is of no consequence. It's all about control of the operating environment which IMHO takes a lot of the load off of support personnel.

    FWIW, here's sample copy of the menu code I use in Excel (It's VBA so it should work in any Office App). Of course in Access I usually use a Switchboard and completely hide the user interface, as I said it's all about control

    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|   ApplicationMenu()     |-------------| 02/18/16 |
    '                         +-------------------------+             +----------+
    'Called by: Auto_Open
    
    Sub ApplicationMenu()
    
       Dim myMenuBar As Object
       Dim newMenu   As Object
       Dim ctrl      As Object
       Dim oWkBk     As Workbook
        
       KillApplicationMenu "Images Menu"  'Deletes menu bar if it exists before recreating
    
       Set oWkBk = ActiveWorkbook
    
       Set myMenuBar = _
          CommandBars.Add("Images Menu", msoBarLeft + msoBarTop, , True)
       myMenuBar.Visible = True
       Set newMenu = _
          myMenuBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
       newMenu.Caption = "Images Menu"
    
       If Not oWkBk.ReadOnly Then
         Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
         With ctrl
             .Caption = "&Read Image drive"
             .TooltipText = "Scan selected drive for image files."
             .Style = msoButtonCaption
             .OnAction = "ImportImageBackupList"
         End With
       End If
       
       Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
       With ctrl
           .Caption = "&Delete Image Drive"
           .TooltipText = "Remove Image Drive from Table"
           .Style = msoButtonCaption
           .OnAction = "DeleteImageDrive"
       End With
       
       Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
       With ctrl
           .Caption = "&Setup"
           .TooltipText = "Add/Remove Drive(s) to Image"
           .Style = msoButtonCaption
           .OnAction = "Setup"
       End With
    
       Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
       With ctrl
          .Caption = "&Quit - No Save"
          .TooltipText = "Exit Auto-Image Backup Log w/o saving changes."
          .Style = msoButtonCaption
          .OnAction = "ExitQuit"
       End With
    
       Set ctrl = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
       With ctrl
           If ActiveWorkbook.ReadOnly Then
             .Caption = "&Exit - No Save"
           Else
             .Caption = "Save and &Exit"
           End If
          .TooltipText = "Exit Auto-Image Backup Log and save data"
          .Style = msoButtonCaption
          .OnAction = "ExitSave"
       End With
       
    End Sub                   'ApplicationMenu()
    What the above produces:
    ExcelMenu.PNG

    BTW: the above code works in Office 2003-2010, last version I have, the only difference is in 2007+ it shows up on the Add-ins tab vs it's own menu item.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    That's an interesting way of handling menus which would probably have meant that the problem couldn't have reared itself in the first place.

    The application I was referring to already uses code to create some popup menus from scratch, so it wouldn't be a big step to doing the lot. Hmm. I will ponder.

    Thanks very much for the suggestions. I would still love to know the root cause of the problem I had, but switching to function calls from the menu was a good workaround.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •