Adding Menu Option to Menu Bar 1

jefferytmc · Jun 8, 1999

I have found how to add a menu option to the Main Menu bar in Access. But I am still having a couple of problems. 
 
1) It only allows me to run a macro, I cannot choose to run any other file. 
 
2) It only works in the database that I created it in. Eventhough the Menu Option appears in all databases that I open. 
 
What I am trying to do is this. We use Access as a front end to our company's database. I would like to create a help file that contains the data dictionary for our database and give the user a menu option that allows them to open the help file. Ideally it would be beside the current help option and would work much like Help. 
 
Is this possible in Access 97? I can get close but cannot get exactly what I want.

bitbrain · Jun 10, 1999

I may not answer your questions directly but... 
 
I have an application with similar requirements. I put my "help" information in a table. It contains a "topic" text field as primary key and a memo field for the text. Compose the text in a word processor & cut & paste into the table. 
 
The application has a main form that is opened at start-up. In the main form's load event I put a call to a routine that dynamically buids the menu bar option EACH time the form is loaded. I also delete the menu when the form is unloaded. 
 
The fact that menu items run macros is not a limitation. You can do virtualy anything from a macro. For example you can execute a code module from the macro that fires off another application... In the example below the Help option executes a macro that opens a form that displays the help topics. The form acts as a menu, when you click the topic the help text is displayed. 
 
The code to build the menu follows: 
 
Sub BuildBillingMenuControl() 
 
' Object variables... 
Dim cbc As CommandBarControl 
Dim cbPop As CommandBarPopup 
Dim cbPopBilling As CommandBarPopup 
Dim cbcAdmin As CommandBarPopup 
' Other... 
Const FORM_FACE As Integer = 1837 
Const TABLE_FACE As Integer = 1835 
 
' See the "Billing/Collection Menu" Help topic for an overview of how the menu items 
' are structured. 
 
' This routine builds the Billing/Collection menu item on the "Menu Bars" command bar. 
' The menu item is deleted and completely re-built each time the application executes. 
' This routine is called from the OnLoad event of the the Main Menu (frmMain). 
 
 
' Note: to get access to the command bar objects, 
' establish pointer to MS Office object library; 
' select Tool / References, check MS Office 8.0 obj lib. 
 
 
 
' Delete Billing/Collection menu item so it can be rebuilt 
 
Utility.DeleteBillingMenuControl 
 
' =========================== 
' Add Billing/Collection menu 
' =========================== 
 
Set cbPopBilling = CommandBars("Menu Bar").Controls.Add(msoControlPopup) 
 
With cbPopBilling 
.Caption = BILLING_BAR_CAPTION 
.BeginGroup = True 
End With 
 
' ========================================= 
' Add menu items to Billing/Collection menu 
' ========================================= 
 
' Note: The OnAction property contains the name of the macro to execute when 
' the menu item is clicked. 
' The FaceId property (optional) defines the button face or icon that you see 
' to the left of the menu item. 
' The two Help properties (optional) contain standard settings for this 
' type of control. 
' The BeginGroup property (optional) adds a line to the control to separate it 
' from the other contols. 
 
 
'Debug.Print "add Main Menu button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Main Menu" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormMain" 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add agt bal ledger button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Agent's Balances Ledger" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormAgtBalLed" 
.BeginGroup = True 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add loss susp ledger button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Loss Suspense Ledger" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormLossSuspLed" 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add PT ledger button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&PT Ledger" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormPTLed" 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add Pool Counter ledger button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "P&ool Counter Signature Ledger" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormPoolCounterLed" 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add Reports button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Reports" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormReports" 
.BeginGroup = True 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add Help button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Help" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormHelp" 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add Administration MENU item" 
Set cbcAdmin = cbPopBilling.Controls.Add(msoControlPopup) 
 
With cbcAdmin 
.Caption = "A&dministration" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
End With 
 
 
'Debug.Print "add acct code button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Account Codes" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenAccountCodeTable" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add acct type button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "Account &Types" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenAccountTypeTable" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add Location code button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Location Codes" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenLocTable" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add loss code button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "Lo&ss Codes" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenLossTable" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add SPEC code button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "S&PEC Codes" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenSpecTable" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add Help button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Help Topics" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenHelp" 
.FaceId = TABLE_FACE 
End With 
 
 
'Debug.Print "add Report button to admin menu" 
Set cbc = cbcAdmin.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "&Report Menu Maint" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenFormReportMaint" 
.BeginGroup = True 
.FaceId = FORM_FACE 
End With 
 
 
'Debug.Print "add About button" 
Set cbc = cbPopBilling.Controls.Add(msoControlButton) 
 
With cbc 
.Caption = "A&bout Billing and Collection Ledgers" 
.HelpContextId = 120446977 
.HelpFile = "oftip8.hlp" 
.OnAction = "OpenAboutForm" 
.BeginGroup = True 
.FaceId = FORM_FACE 
End With 
 
 
 
 
' ============================ 
' Release all object variables 
' ============================ 
 
Set cbPop = Nothing 
Set cbPopBilling = Nothing 
Set cbc = Nothing 
Set cbcAdmin = Nothing 
 
End Sub 
 
 
 
 
The code to delete the menu is: 
 
Sub DeleteBillingMenuControl() 
Dim cb As CommandBar 
Dim cbc As CommandBarControl 
 
' turn off the Billing/Collection menu on the menu bar 
 
Set cb = CommandBars("Menu Bar") 
 
For Each cbc In cb.Controls 
If cbc.Caption = BILLING_BAR_CAPTION Then 
' Debug.Print cbc.Caption 
cbc.Delete 
End If 
Next cbc 
End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Adding Menu Option to Menu Bar 1

jefferytmc

MIS

bitbrain

Programmer

Similar threads

Part and Inventory Search

Sponsor