Form Menu Bar

Question

Dear All,

I am trying to replace the command buttons on my forms by a general menu bar that has submenus performing the functions encoded in the buttons but i did not find a solution.(ie file, edit, view, etc)

Can anybody help me?

I am using Access 2010

Thanks

Answer 1

Then you are out of luck - Office 2007 and later do not provide a way to display menus except in the Add-Ins tab of the toolbar.

If you don't want to create and display a custom ribbon (which would be far from trivial), I'd stick with controls on the form itself. You might use combo boxes in the form header to simulate dropdown menus...

Answer 2

Here is an example:

Sub MakeMenu()

    Dim cbr As CommandBar

    Dim cbx As CommandBarComboBox

    On Error Resume Next

    CommandBars("MyMenu").Delete

    On Error GoTo ErrHandler

    Set cbr = CommandBars.Add(Name:="MyMenu", MenuBar:=True)

    Set cbx = cbr.Controls.Add(msoControlDropdown)

    With cbx

        .Caption = "Reports"

        .Style = msoComboLabel

        .AddItem "Report 1"

        .AddItem "Report 2"

        .AddItem "Report 3"

        .AddItem "Report 4"

        .ListIndex = 1

        .OnAction = "=OpenMyReport()"

    End With

ExitHandler:

    Set cbr = Nothing

    Set cbx = Nothing

    Exit Sub

ErrHandler:

    MsgBox Err.Description, vbExclamation

    Resume ExitHandler

End Sub

Function OpenMyReport()

    Dim strChoice As String

    On Error GoTo ErrHandler

    DoCmd.Hourglass True

    strChoice = CommandBars("MyMenu").Controls("Reports").Text

    DoCmd.OpenReport ReportName:=strChoice, View:=acViewPreview

ExitHandler:

    DoCmd.Hourglass False

    Exit Function

ErrHandler:

    MsgBox Err.Description, vbExclamation

    Resume ExitHandler

End Function

Sub RemoveMenu()

    On Error Resume Next

    CommandBars("MyMenu").Delete

End Sub

The MakeMenu procedure will create a custom menu bar with one menu, titled My Menu.

This menu has 4 items, labeled Report 1 to Report 4.

You only need to run this procedure once; the custom menu bar will be stored in the database.

It will normally be invisible. To make it visible for a specific form, set the Menu Bar property of the form to MyMenu (you must have run MakeMenu before you do this).

When you open the form, Access will display the Add-Ins tab in the ribbon; this tab will contain the menu.

When you select an item from the dropdown menu, the OpenMyReport function will be executed. It inspects the selected item and tries to open the corresponding report. (This is just an example - you can make the function do whatever you want)

If you want to delete the menu from the database, run the procedure RemoveMenu.

Answer 3

Thanks but multi tab control works in vertical manner, can I adjust it to work in horizontal fashion ?

Answer 4

Hi,

Up to Office 2003, you could create custom menus bars on the main application interface only.

Since the introduction of the Ribbon ...

Maybe a Multi tab control could mimic this.

Regards

JY

Answer 5

Hi Ahmad,

For better suggestions related to forms in Office applications, you may post your query in the forum link below:

http://social.technet.microsoft.com/Forums/office/en-US/home

Thank you.