By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

MS Access:vba Build Custom Menus 2007 problem

P: 3
I have developed an MS Access Application that has many users on different versions of Office. It has custom menus that are built via vba code every time it is started. The menus and their contents vary based on the user permissions, which profiles they are using, which backends are currently attached etc. I do my development using MS Access 2000, so that I can be compatible back to that version. The distributed version is an mde. This application, or some version of it has been in use for 6 years.

Before Access 2007, there was not a problem with the menus rebuilding themselves every time the user started it up. But with Access 2007, I had to add a Tool bar button that they could use to Rebuild the Menus whenever they switched profiles or other situations. The strange thing is that the button calls exactly the same routine as is called in the startup code. It can also be run by the user clicking a button on the startup form. The problem is that often the User switches profiles and forgets to click the Build Menus button and then are confused by the menus.

Does anyone have any clues why my BuildMenus routine works perfectly when the user clicks some kind of button, but the same routine can be called from code and even though it appears to run (writes to a log file) with no errors (has good error handling) the menus do not appear to be rebuilt without the the user interaction.

I've wasted days making new builds trying to figure this out, and am very perplexed. Would very much appreciate help.

TIA, Christine
Dec 12 '11 #1

✓ answered by TheSmileyCoder

Seems perplexing....
When you say its run from code, how exactly is that? Startup macro? Open/Load event of a form?

Can you post your code for the Build Menu, as well as how you run it? I realise you have tried to give alot of information, but there is not much specific details to work from, nor test from.

Share this Question
Share on Google+
5 Replies

Expert Mod 100+
P: 2,321
Seems perplexing....
When you say its run from code, how exactly is that? Startup macro? Open/Load event of a form?

Can you post your code for the Build Menu, as well as how you run it? I realise you have tried to give alot of information, but there is not much specific details to work from, nor test from.
Dec 12 '11 #2

P: 3
Thanks for responding. You ask some good questions and I really appreciate your interest.

The first thing that happens on Start Up is the frmWelcome opens up - just a basic welcome splash screen to give the user something to look at while all the actual startup activity takes place. The Form_Load event of frmWelcome calls routine MyStartup (that is in a module) that controls all the startup activity. MyStartup calls routine BuildMainMenu near the tail end of it.

The actual menu building involves many routines, based on many factors. I will show you the top menu building routine and then an example of lower level routines, at the end of this post. I am also going to try to add an attachment that shows all the database properties. You will notice that the AllowFullMenus, AllowBuiltinToolbars and AllowToolbarChanges proprties are all false, which prevents the Add-Ins business in 2007, as it should. Also will attach a picture of what the top level of my buttons/menus looks like.

As I mentioned in the original post, the entire menu building works flawlessly in 2007 when the user clicks button. It also works fine from the startup code in Access 2000, 2002, 2003.

I look forward to your thoughts on this.
---- sample menu building routines -------
Expand|Select|Wrap|Line Numbers
  1. Function BuildMainMenu()
  2.    Call mProcStack.EnterProc(mcModName, "BuildMainMenu")
  3.    Dim cbMenu As CommandBar, cbSubMenu As CommandBar, cbTools As CommandBar
  4.    Dim cbToolMenu As CommandBar
  5.    Dim sStatus As String
  6. On Error GoTo ErrH
  7.    PutLog "BuildMainMenu:enter"
  8.    Set cbMenu = NewMainMenu
  9.    'Add the menus to the Main Menu
  10.    sStatus = " before MenuFile"
  11.    MenuFile cbMenu
  12.    sStatus = " before MenuEdit"
  13.    MenuEdit cbMenu
  14.    sStatus = " before MenuView"
  15.    MenuView cbMenu
  17.    sStatus = " before MenuLists"
  18.    MenuLists cbMenu
  19.    sStatus = " before MenuReports"
  20.    MenuReports cbMenu
  22.    sStatus = " before MenuTools"
  23.    MenuTools cbMenu
  24.    sStatus = " after MenuTools"
  25.    Set cbToolMenu = cbMenu.Controls("&Tools").CommandBar
  26.    AddMenuControl cbToolMenu, "View System Log File", _
  27.                            "=ShowLog('" & SysLogFile & "')", , True
  29.    sStatus = " before MenuPreferences"
  30.    MenuPreferences cbMenu
  32.    sStatus = " before MenuDevelopment"
  33.    MenuDevelopment cbMenu, , True 'True means Forms only (speed up Menu build)
  34.    sStatus = " before MenuWindow"
  35.    MenuWindow cbMenu
  36.    sStatus = " before MenuHelp"
  37.    MenuHelp cbMenu, , "About " & AppSysName
  38.    sStatus = " after MenuHelp"
  39.    'make sure the Menu Bar is visible in case it got turned off somehow.
  40.    Application.CommandBars("mnuUI").Visible = True
  41.    sStatus = "Before mnuTB Visible True"
  42.    Application.CommandBars("mnuTB").Visible = True
  43. Ex:
  44.    Debug.Print "End BuildMainMenu"
  45.    PutLog "BuildMainMenu:exit"
  46.    Call mProcStack.ExitProc(mcModName, "BuildMainMenu")
  47.    Exit Function
  48. ErrH:
  49.    If SysCmd(acSysCmdRuntime) Then
  50.       sStatus = sStatus & vbCrLf & "Using RunTime version of Access v" & SysCmd(acSysCmdAccessVer)
  51.    Else
  52.       sStatus = sStatus & vbCrLf & "Using Access v" & SysCmd(acSysCmdAccessVer)
  53.    End If
  55.    sStatus = sStatus & vbCrLf & " ErrH " & Err.Number & " " & Err.Description
  56.    Err_Handler Err, sStatus
  57.    Resume Next
  58. End Function
  60. 'Example Mid Level Routine:
  61. Sub MenuLists(cbMenu As CommandBar, Optional fUpdate As Boolean)
  62. '' Builds the Application's Lists Menu
  64.    Call mProcStack.EnterProc(mcModName, "MenuLists")
  65.    Dim cbSubMenu As CommandBar
  67.    If Not fUpdate Then
  68.       Set cbSubMenu = AddMenu(cbMenu, "&Lists")
  69.    Else
  70.       Set cbSubMenu = cbMenu.Controls("&Lists").CommandBar
  71.       ClearMenu cbSubMenu
  72.    End If
  74.    AddMenuControl cbSubMenu, "Job List Form...", "=MnuOpnForm(""frmJobList"")"
  75.    AddMenuControl cbSubMenu, "Error Definition List Form...", "=MnuOpnForm(""frmErrorList"")"  ', , True
  76.    AddMenuControl cbSubMenu, "Audit Types List Form...", "=MnuOpnForm(""frmAuditTypeList"")"
  77.    AddMenuControl cbSubMenu, "Names List Form...", "=MnuOpnForm(""frmNameList"")", , True
  78.    AddMenuControl cbSubMenu, "Accounts...", "=MnuOpnForm(""frmNameList"")", , True
  79.    AddMenuControl cbSubMenu, "Dictators...", "=MnuOpnForm(""frmNameList"",0," & gcNT_Dicts & ")"
  80.    AddMenuControl cbSubMenu, "Staff Names...", "=MnuOpnForm(""frmNameList"",0," & gcNT_StaffNames & ")"
  81.    AddMenuControl cbSubMenu, "Staff Contact Information...", "=MnuOpnForm(""frmNameList"",0," & gcNT_StaffCont & ")"
  82.    AddMenuControl cbSubMenu, "Staff Permissions...", "=MnuOpnForm(""frmNameList"",0," & gcNT_StaffPerms & ")"
  83.    AddMenuControl cbSubMenu, "Departments...", "=MnuOpnForm(""frmNameList"",0," & gcNT_Dept & ")"
  84.    AddMenuControl cbSubMenu, "Transcription Groups...", "=MnuOpnForm(""frmNameList"",0," & gcNT_TGroups & ")"
  86.    If IsTrainingProfile Then
  87.       AddMenuControl cbSubMenu, "Classes...", "=MnuOpnForm(""frmNameList"",0," & gcNT_Classes & ")"
  88.       AddMenuControl cbSubMenu, "Students...", "=MnuOpnForm(""frmNameList"",0," & gcNT_Students & ")"
  89.    End If
  91.    AddMenuControl cbSubMenu, "Specialties...", "=MnuOpnForm(""frmNameList"",0," & gcNT_Specialties & ")"
  92.    AddMenuControl cbSubMenu, "Work Types...", "=MnuOpnForm(""frmNameList"",0," & gcNT_WorkTypes & ")"
  94.    AddMenuControl cbSubMenu, "Reconcile List Form...", _
  95.                               "=MnuOpnForm(""frmReconcile"")", , True
  97.    AddMenuControl cbSubMenu, "Release Changes - In Last Release...", _
  98.                               "=MnuOpnForm(""frmShowList"",0,""RelChangesPrevious"")", , True
  99.    AddMenuControl cbSubMenu, "Release Changes - Since Last Release...", _
  100.                               "=MnuOpnForm(""frmShowList"",0,""RelChangesCurrent"")"
  101.    AddMenuControl cbSubMenu, "Error Type Changes - Since Last Synchronize...", _
  102.                               "=MnuOpnForm(""frmShowList"",0,""SyncError View"")", , True
  103.    AddMenuControl cbSubMenu, "Staff Changes - Since Last Synchronize...", _
  104.                               "=MnuOpnForm(""frmShowList"",0,""SyncStaff View"")"
  105.    AddMenuControl cbSubMenu, "Specialty && Work Type Changes - Since Last Synchronize...", _
  106.                               "=MnuOpnForm(""frmShowList"",0,""SyncSpecialty and Work Type View"")"
  108.    Debug.Print "MenuLists done"
  109.    Call mProcStack.ExitProc(mcModName, "MenuLists")
  110. End Sub 'MenuLists
  112. 'Example Low Level Routine:
  113. Function AddMenuControl(cbr As CommandBar, sCaption As String, sOnAction As String, _
  114.                Optional iIcon As Integer, Optional fBeginGroup As Boolean) As Integer
  115.    Call mProcStack.EnterProc(mcModName, "AddMenuControl")
  117. '' Adds a custom control to the specified menu, with the given Caption
  118. '' and returns the index the control has on its menu
  119. '| sOnAction: The action, as a string, "=function()"
  120. '| sIcon: The FaceID representing the icon to use.
  121. '| (Get this number by checking the FaceID property of any control
  122. ''     on any built-in or custom command bar. For example,
  123. ''     ? commandbars("Edit").Controls("Copy").faceid
  124. ''     Note that for Builtin Menu Controls FaceID always seems to match ID.
  125. '| fBeginGroup: If True, adds a line above the new control
  127.    Dim cbbutton As CommandBarButton
  129.    Set cbbutton = cbr.Controls.add(msoControlButton)
  130.    With cbbutton
  131.       .Caption = sCaption     'GetCustomName(sCaption)
  132.       .FaceId = iIcon
  133.       .Style = msoButtonIconAndCaption
  134.       .OnAction = sOnAction
  135.       .BeginGroup = fBeginGroup
  136.       AddMenuControl = .Index
  137.    End With
  139.    Call mProcStack.ExitProc(mcModName, "AddMenuControl")
  140. End Function
  141. Function MnuOpnForm(sFormName As String, Optional aView As AcFormView, _
  142.       Optional vOpenArgs As Variant, Optional sWhere As String)
  143. '' Function to open the given Form, in the given View, with
  144. '' the given Openargs and Criteria. This is used as the
  145. '' onAction for Form-opening Menu items since only
  146. '' functions can be called from Menus and Subs cannot.
  148.    Call mProcStack.EnterProc(mcModName, "MnuOpnForm")
  150.    'On Error Goto ErrH
  151.    If IsOpen(sFormName) Then
  152.       DoCmd.Close acForm, sFormName
  153.    End If
  154.    Open_Form sFormName, aView, , sWhere, , , vOpenArgs
  155. Ex:
  156.    Call mProcStack.ExitProc(mcModName, "MnuOpnForm")
  157.    Exit Function
  158. ErrH:
  159.    If Err.Number <> 2501 Then
  160.       Err_Handler Err.Number
  161.    End If
  162.    Resume Ex
  163. End Function
Attached Images
File Type: jpg ScreenHunter_02 Dec. 12 21.51.jpg (64.6 KB, 398 views)
File Type: jpg ScreenHunter_03 Dec. 12 22.17.jpg (15.6 KB, 194 views)
Dec 13 '11 #3

Expert Mod 100+
P: 2,321
Allthough there are code parts missing, the parts I see look good enough, and written with a high degree of experience. I wasn't really expecting to find any obvious flaws of course, since Its working in earlier versions of Access.

Im guessing that
Expand|Select|Wrap|Line Numbers
  1.    PutLog "BuildMainMenu:exit"
  2.    Call mProcStack.ExitProc(mcModName, "BuildMainMenu")
are both part of your error tracking/handling, and as such can probably be ignored for this question, but please confirm.

As you are probably allready aware, tracking that cross-version issues is quite hard, especially if no error is being raised.

The only thing I can do is make some suggestions on how you can try to track down the problem.

Do a Decompile/recompile, if you have not tried allready.

Simplify the code until you track down the issue. In the frmWelcome build a menu bar without all the bells and whistles, without using any low level routines. This might at least help eliminate any suspicion as to your low level routines.

After building the bar, try some checks to see what access "knows" of the bar.
Use this to check if the bar was actually created:
Expand|Select|Wrap|Line Numbers
  1. Public Function CheckForBarExistance(strBarName As String) As Boolean
  2.     On Error GoTo err_Handler
  3.     With CommandBars(strBarName)
  4.         CheckForBarExistance = .Name <> ""
  5.     End With
  6. Exit Function
  7. err_Handler:
  8.     CheckForBarExistance = False
  9. End Function
Use this to check whether access thinks its visible and enabled:

Expand|Select|Wrap|Line Numbers
  1. Public Function CheckForBar(strBarName As String) As Boolean
  2.     With CommandBars(strBarName)
  3.         CheckForBar = .Visible And .Enabled
  4.     End With
  5. End Function
or finally for lack of anything better to suggest look at the output from this;
Expand|Select|Wrap|Line Numbers
  1. Public Sub ListBars()
  2.     Dim intI As Integer
  3.     For intI = 1 To CommandBars.Count
  4.         Debug.Print CommandBars(intI).Name
  5.     Next
  6. End Sub

If all of that leads nowhere try creating a AC2007 version, compiling and saving, and see if that makes any differnce.

I realise that this might not be much to go on, but thats the best I can really come with at the moment.
Dec 13 '11 #4

P: 3
Thank you for your questions and feedback. I believe I have resolved this issue by moving the call to BuildMainMenu so that it runs after the the Form Load, and not in the Form Load of the Welcome form. Your questions led me to explore the possibility that it had to do with this. For some reason, when called fromForm_Load, the menus do not appear updated even though all the code runs with no error.

Moving the call until later fixed this problem. I accomplished this by putting the call into the routine that gets run from AutoExec after the Welcome form is done.

Thank you very much for your help, Christine
Dec 17 '11 #5

Expert Mod 15k+
P: 31,308
The Form_Load() event, and associated procedure, occur at a time when the form has not finished preparing everything. You can use Context-Sensitive Help to see the order the events occur in. Load occurs before all of the Resize, Activate and Current events. Some items are not available until all these events have completed their processing and preparations.
Dec 17 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.