Custom Right-click Toolbar 1

[trevorwilliams2]

Access 2003

I am trying to design a custom right-click reports menu for a projects page in my access database.

I have multiple groupings of reports.

So far with, the custom right-click menu I have created, I have only been able to create a long continous list.

What I would like to do is create report groups with the right pointing arrow to expand that group to display reports within in that group.

For instance:

Project data reports >
Data report 001
Data report 002
Data report 003
Data report 004
Directory Reports >
Document Reports >

The built in access right-click menus have this but I cant seem to do this in a custom right click popup menu.

Any suggestions would be much appreciated!

 

[MajP]

i normally build this by looping through the inputs in a table. See if you can follow this.

But you build a command bar, add a command bar control for each category, then add a command bar controls for each product in each category. I hilited the important steps

Public Sub createProductCommandBar()
  Const conBarName = "cbProducts"
  
  Dim rsCat As DAO.Recordset
  Dim rsProducts As DAO.Recordset
  Dim rsOrders As DAO.Recordset
  Dim strSql As String
  Dim catCaption As String
  Dim catValue As Long
  Dim prodCaption As String
  Dim prodValue As Long
  Dim cbCat As Office.CommandBar
  Dim cbCatCtrl As Office.CommandBarControl
  Dim cbProdCtl As Office.CommandBarControl
  Dim ctl As CommandBarControl
  Dim cb As CommandBar
  
  
  Set rsCat = CurrentDb.OpenRecordset("qryCategories", dbReadOnly)
  If isCommandBar(conBarName) Then
    Application.CommandBars(conBarName).Delete
  End If
  'create the command bar
  [b]Set cbCat = CommandBars.Add(conBarName, msoBarPopup, False, True)[/b]
  
  Do While Not rsCat.EOF
    catCaption = rsCat!CategoryName
    catValue = rsCat!CategoryID
    strSql = "Select * from qryProducts where CategoryID = " & catValue
    'create a command bar control for each category
    [b]Set cbCatCtrl = cbCat.Controls.Add(msoControlPopup)[/b]
    cbCatCtrl.caption = catCaption
    
    Set rsProducts = CurrentDb.OpenRecordset(strSql, dbReadOnly)
    Do While Not rsProducts.EOF
      'create a command bar ctrl for each product
      [b]Set cbProdCtl = cbCatCtrl.Controls.Add()[/b]
      prodCaption = rsProducts!ProductName
      prodValue = rsProducts!productID
      cbProdCtl.caption = prodCaption
      cbProdCtl.Tag = prodValue
      cbProdCtl.OnAction = "subFilterOrders"
      rsProducts.MoveNext
    Loop
    rsCat.MoveNext
  Loop
End Sub

 

[trevorwilliams2]

bit hung up on "isCommandBar". Is it a seperate function in your DB?

 

[trevorwilliams2]

MajP, works like a charm!

Still could not get around "isCommandBar" & the refernce to "subFilterOrders" in the on action for the menu selection, but I can code around this.

Saved me a lot of time...thanks much!!

 

[MajP]

sorry, that is a custom function I built to determine if the command bar exists. If not you get an error trying to delete one that does not exist or adding one that does exist. In this example I was actually using the short cut menu like a cascading combobox for data entry. So I was populating the menu based off some tables.

The subFilterOrders is the name of another function that executes when you select a menu choice. That sub routines then determines which menu choice was selected.