Solved Can i make a shortcut menu with levels ? (1 Viewer)

[MajP]

This idea works so well, not sure why everyone has not been doing more of this. And this is just scratching the surface. You can add all types of menu items. The code is actually rather easy and would work on any database, you just have to build the queries. I loaded a large database with lots of forms, reports, and queries. You can launch any of these from the command bar.

I made 3 queries. One with forms, one with reports, one with queries.

Here is the whole code

Public Sub LoadCommandBar()
  Const conBarName = "cbObjects"
 
  Dim rsForms As DAO.Recordset
  Dim rsQueries As DAO.Recordset
  Dim rsReports As DAO.Recordset
    
  Dim cbCat As Office.CommandBar
  Dim cbCatCtrl As Office.CommandBarControl
  Dim cbObjectCtrl As Office.CommandBarControl
 
  Set rsForms = CurrentDb.OpenRecordset("Select * from qryforms")
  Set rsQueries = CurrentDb.OpenRecordset("Select * from qryQueries")
  Set rsReports = CurrentDb.OpenRecordset("Select * from qryReports")

 
  If isCommandBar(conBarName) Then
    Application.CommandBars(conBarName).Delete
  End If
  'Build tha bar
  Set cbCat = CommandBars.Add(conBarName, msoBarPopup, False, False)
 
  'Build the first forms selection
  Set cbCatCtrl = cbCat.Controls.Add(msocontrolpopup)
  cbCatCtrl.Caption = "Forms"
  Do While Not rsForms.EOF
      Set cbObjectCtrl = cbCatCtrl.Controls.Add()
      With cbObjectCtrl
       .Caption = rsForms!Name
       .Tag = rsForms!Name
       .OnAction = "OpenForm"
      End With
      rsForms.MoveNext
  Loop
 
  Set cbCatCtrl = cbCat.Controls.Add(msocontrolpopup)
  cbCatCtrl.Caption = "Queries"
  Do While Not rsQueries.EOF
      Set cbObjectCtrl = cbCatCtrl.Controls.Add()
      With cbObjectCtrl
       .Caption = rsQueries!Name
       .Tag = rsQueries!Name
       .OnAction = "OpenQuery"
      End With
      rsQueries.MoveNext
  Loop
 
  Set cbCatCtrl = cbCat.Controls.Add(msocontrolpopup)
  cbCatCtrl.Caption = "Reports"
 
  Do While Not rsReports.EOF
      Set cbObjectCtrl = cbCatCtrl.Controls.Add()
      With cbObjectCtrl
       .Caption = rsReports!Name
       .Tag = rsReports!Name
       .OnAction = "OpenReport"
      End With
      rsReports.MoveNext
  Loop
End Sub
Public Function isCommandBar(strBarName As String) As Boolean
  Dim cb As CommandBar
  For Each cb In Application.CommandBars
    If cb.Name = strBarName Then
      isCommandBar = True
    End If
  Next cb
End Function

Public Sub OpenForm()
  DoCmd.OpenForm CommandBars.ActionControl.Tag
End Sub

Public Sub OpenQuery()
  DoCmd.OpenQuery CommandBars.ActionControl.Tag
End Sub
Public Sub OpenReport()
  DoCmd.OpenReport CommandBars.ActionControl.Tag
End Sub

 

[VBANewBie :)]

Here is the whole code

You are a magician , Simple and perfect , Thank you so much exactly what i needed .

 

[VBANewBie :)]

Here is the whole code

Hello , Another question please .
Does msocontrolpopup support adding icons ? i tried FaceID it works only with the submenus and i also tried .Picture didn’t work either .
I need to add icons besides the main controls (Forms,Queries,Etc,) Thanks again.

Edit # It can’t be done after all thank you .

 

[MajP]

You can add icons. Set the FACEID property. Not sure why this is hidden in intellisense. You cannot do it with a "Forms, Queries, Reports" since these are popup menus. Just like on your originally posted image, you can have it for non "popup". You will have to Google to find where to get the correct IDs. I just added 1 each time starting at 242 for some random icons. (FYI I counted and found what Excel was after the fact)

I would recommend if really doing this to create a table. I looped all the objects in the hidden system table to get the forms, reports, and queries. However, in reality this would be bad. This could include action queries, subforms, subreports that you would not want the user to open. Then in your table you would have fields

DisplayName ' This is what you show in the menu. "Sales by Month"
ObjectName ' Real Name of the report, query, form. This gets stored in the tag property "rptSalesByMonth"
ObjectType ' Form, Query, Report
FaceID ' if you want an icon
Action ' Name of procedure to run if selected

Then you can make this simpler. Loop just this table. Check the object type and use an "if then" to add them to a form, report, or query. So you can just loop this one table, not the three queries I did. Then set these properties. Include only those forms, reports, queries of interest to the user.

Also you are not limited to 2 levels. You could have groups of reports.

Reports
---- Sales Reports
-------- This Month
-------- Last Month
-----Personnel Reports
------- Active Employees
------- Inactive Employees

Going back to the table you would have to add another field for MenuLevel and ParentMenu. With a little work this would be doable, if you want to get tricky.

 

[VBANewBie :)]

I would recommend if really doing this to create a table. I looped all the objects in the hidden system table to get the forms, reports, and queries. However, in reality this would be bad. This could include action queries, subforms, subreports that you would not want the user to open. Then in your table you would have fields

Hello , I already thought of that and will do it , Thank you so much for your help , I appreciate it .