Access Menus'

[Micron]

You are saying it's called Custom Menu after all?
So what exactly is it you want to do to it?


Remove or hide one of the menus (like File, Tools, etc) or one of the sub menus (like ...Print, Options)?
Change what they say? What they do?
I've re-read your posts and am not sure.

[jo15765]

Yes - the name that pops up in the list is "Custom Menu" - I want to add a new item to it. For example, I want to add the option to run a new query from that menu dropdown.

[Micron]

Well, this might be embarrassing for me. Based on what I just found in my old code, it looks like we can reference the menu bar menu item without knowing the name of the menubar. If that's the case, sorry, but maybe you picked up a smidgen of commandbar code knowledge in the process. Try this that I wrote for you (need I say on a copy of your db??) NameOfYourMenu is like "File" or whatever the menu name is for the menu you want to add to. If you want to underline a letter in the name of the menu ITEM you are adding so that keyboard shortcuts can be used (like File), use the ampersand in front of the letter you want to designate for the shortcut (&File). If the name of the existing menu that we are adding an item to already has a letter underlined (like File), that is part of the name and you must use it in your reference (e.g. Purchase Orders is Purchase O&rders).

You could make this a function if you wanted to add items without having to write code for each. You could pass the menu name, item name, caption, etc. to it.

Private Sub AddMenuItem()
Dim newItem As CommandBarControl

On Error GoTo errHandler

Set newItem = CommandBars("NameOfYourMenuItem").Controls.Add(Type:=msoControlButton)
With newItem
  .BeginGroup = False 'creates, or not, a dividing line
  .Caption = "Run A Query..."
  .FaceId = 0
  .OnAction = "RunAQuery" 'needs to be the name of a function, not a sub, that either runs the query, or calls the routine that runs it
End With

exitHere:
Exit Sub

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

[jo15765]

@Micron - that syntax executes no problem. However, I do not see where my new addition is actually added? I have gone through each drop down menu and it is not there, nor is it displayed anywhere

Did I Miss something?

[Micron]

Post your actual code or a zipped copy of the db, or see if it was added to the Quick Access toolbar or above/below the ribbon somewhere.

[jo15765]

@Micron - unfortunately I am unable to post a copy of my database as there is sensative data embedded as headers in this custom menu -- this is the actual stynax that I ran, that runs error free, but is not added to the menu anywhere?

Private Sub AddMenuItem()
Dim newItem As CommandBarControl


On Error GoTo errHandler


Set newItem = CommandBars("Custom Menu").Controls.Add(Type:=msoControlButton)
With newItem
  .BeginGroup = False 'creates, or not, a dividing line
  .Caption = "Run A Query..."
  .FaceId = 0
  .OnAction = "_Test" 'needs to be the name of a function, not a sub, that either runs the query, or calls the routine that runs it
End With


exitHere:
Exit Sub


errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

And _Test is the name of a query that I created in the database that I would like run when the user clicks the option "Run A Query" from the menu.

[Micron]

I've reviewed all the posts here again to recall what you wanted to do with this and see the menu bar again but you removed the image from where you had it posted. Post the image here instead, and clarify - if a menu has these menus
File | Edit | Tools | Etc.
you want to add this query running capability to the menu bar as a new menu
File | Edit | Tools | Run Query
OR to one of the existing menus as a new menu option? I'm not sure that a menu bar menu can execute code.

After reviewing, I'll note that the entire menu bar appears on the ribbon on the Add Ins tab as an add in. Make sure the added item is not displayed there.

[jo15765]

I am using Access 2013 so the menu options are HOME | Create | External Data | Database Tools | Add-Ins

The Add-ins is where the custom menu options are being added and drop down menu etc, that I want to add options to.

Here is an image of what I see - and under the ADD-INS it shows the text Custom Toolbar.

Yes, I am wanting to add to the current custom toolbar, by adding clicking text to the drop down menu(s) that will either open a form, or run a query.

EDIT -- I actually just executed the code on the ONE computer I have found in the Company with Access 2003 installed on it, and the "new" item I am attempting to add was not added.

[jo15765]

WAIT!!!! The syntax below in Access 2003 and Access 2013 does actually add the new menu item. It just does not add any text or an image to it so I had to hover the mouse over the very far right option and now I see it.

Is there a way for me to control the display text and which sub-menu this item goes to?

[Micron]

what I wanted to see is the custom menu, not the ribbon. The view from the add ins tab should be fine.
looks like now I'm going to have to figure out how to modify the name from nothing to something. Running the code again will likely add the item again, which would not be good. A screen shot of what you see now that you found it might help.

[jo15765]

what I wanted to see is the custom menu, not the ribbon. The view from the add ins tab should be fine.
looks like now I'm going to have to figure out how to modify the name from nothing to something. Running the code again will likely add the item again, which would not be good. A screen shot of what you see now that you found it might help.

I found that if I modify this

.FaceId = 263

it will add an image so I actually see something was added. Do you know how to add text with it?

It actually looks like the default set-up for Acc2003 -> this is the same look of the custom toolbar, image to the left in the blue and text on the right with a white background.

[Micron]

I think what you need is the caption property:
.Caption="Run query"
I will have to check that out unless you get to it first.