Access 2003 – Custom Menu – Opening Form

Topic

New Reply

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?

Reply | Quote

Replies

Simon,

Try adding:

Code:

cbtButton.OnAction = "[COLOR="#0000FF"]ProcedureName[/COLOR]"

Then in a Standard Module add a subroutine:

Code:

Sub [COLOR="#0000FF"]ProcedureName[/COLOR]()

   DoCmd.OpenForm "[COLOR="#0000FF"]dlgFormName[/COLOR]", acNormal

End Sub 'ProcedureName

Of course replacing all the items in blue with the appropriate values.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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 :evilgrin:

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:
44053-ExcelMenu

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote