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

How to modify an Access 2003 Switchboard for use in Access 2010?

beacon
100+
P: 579
Hi everybody,

My title may not exactly describe my issue, so please forgive my lack of creativity today.

I've got a database that was created in Access 2003 that I've opened in Access 2010. I intend on keeping the .mdb format for the time being, so I'm not performing a true conversion...just trying to get it to work in the new version. I'm familiar with a number of areas in 2003 that have to be modified to work in 2010, but my issue today is not something I would have thought would cause problems.

In the 2003 version, I needed a Switchboard that could handle different user roles and display different options on the Switchboard based on the logged in user (using a simple login form). So, I created a Switchboard using the Switchboard Manager, then copied the table twice, and modified the contents accordingly for the three user roles.

Then, I went into the code behind the Switchbaord form, added code to determine the logged in user's user role, and modified it to set the record source of the Switchboard form to the correct table. All in all, I added maybe 10 lines of code to the stock Switchboard code and modified another 3-4 lines. For the past 4 years, this has worked like a charm.

When I opened the database using Access 2010, the Switchboard would do nothing when I would click on a handle button that was set to open another Switchboard menu. However, when I click on any of the other handle buttons that have code associated with them to perform a function, they work just fine.

I don't know why this would work in 2003 and won't work in 2010. I tried creating a new Switchboard and the items that were set to direct the user to another Switchboard menu worked just fine, but stopped working once I tried to introduce a variable record source.

I know I could create two more forms so that there's one form for each table of Switchboard items and add some code to the login form to direct the user to the correct Switchboard form, but I'd really like to get this to work so I don't have to create new forms.

Any ideas?

Here's my code (the login form sets the strGlobalUserRole variable and this variable is used to determine the correct table to use as the record source):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '------------------------------------------------------------
  5. ' Form_Load
  6. '
  7. ' This subroutine changes the color based on the active
  8. ' user role and sets the record source to the correct table
  9. '------------------------------------------------------------
  10. Private Sub Form_Load()
  11.  
  12.     Select Case strGlobalUserRole
  13.         Case "Auditor"
  14.             Me.RecordSource = "Switchboard Items - Auditor"
  15.             Me.HorizontalHeaderBox.BackColor = RGB(51, 51, 153)
  16.         Case "OSC"
  17.             Me.RecordSource = "Switchboard Items - OSC"
  18.             Me.HorizontalHeaderBox.BackColor = RGB(157, 2, 2)
  19.         Case "Reports"
  20.             Me.RecordSource = "Switchboard Items - Reports"
  21.             Me.HorizontalHeaderBox.BackColor = RGB(140, 0, 140)
  22.     End Select
  23.  
  24. End Sub
  25.  
  26. '------------------------------------------------------------
  27. ' Form_Open
  28. '
  29. ' This subroutine sets the Switchboard to the default
  30. ' switchboard item
  31. '------------------------------------------------------------
  32. Private Sub Form_Open(Cancel As Integer)
  33. ' Minimize the database window and initialize the form.
  34.  
  35.     ' Move to the switchboard page that is marked as the default.
  36.     Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
  37.     Me.FilterOn = True
  38.  
  39. End Sub
  40.  
  41. '------------------------------------------------------------
  42. ' Form_Current
  43. '
  44. ' This subroutine sets the caption of the current
  45. ' switchboard to the related item in the table and calls
  46. ' the FillOptions subroutine
  47. '------------------------------------------------------------
  48. Private Sub Form_Current()
  49. ' Update the caption and fill in the list of options.
  50.  
  51.     Me.Caption = Nz(Me![ItemText], "")
  52.     FillOptions
  53.  
  54. End Sub
  55.  
  56. '------------------------------------------------------------
  57. ' FillOptions
  58. '
  59. ' This subroutine loops through the items in the table
  60. ' that match the current switchboard ID and outputs the
  61. ' name of the item to the associated button on the form
  62. '------------------------------------------------------------
  63. Private Sub FillOptions()
  64. ' Fill in the options for this switchboard page.
  65.  
  66.     ' The number of buttons on the form.
  67.     Const conNumButtons = 8
  68.  
  69.     Dim con As Object
  70.     Dim rs As Object
  71.     Dim stSql As String
  72.     Dim intOption As Integer
  73.     Dim strSwitchboardVersion As String
  74.  
  75.     'Determine the correct Switchboard table to use
  76.     Select Case strGlobalUserRole
  77.         Case "Auditor"
  78.             strSwitchboardVersion = "[Switchboard Items - Auditor]"
  79.         Case "OSC"
  80.             strSwitchboardVersion = "[Switchboard Items - OSC]"
  81.         Case "Reports"
  82.             strSwitchboardVersion = "[Switchboard Items - Reports]"
  83.     End Select
  84.  
  85.     ' Set the focus to the first button on the form,
  86.     ' and then hide all of the buttons on the form
  87.     ' but the first.  You can't hide the field with the focus.
  88.     Me![Option1].SetFocus
  89.     For intOption = 2 To conNumButtons
  90.         Me("Option" & intOption).Visible = False
  91.         Me("OptionLabel" & intOption).Visible = False
  92.     Next intOption
  93.  
  94.     ' Open the table of Switchboard Items, and find
  95.     ' the first item for this Switchboard Page.
  96.     Set con = Application.CurrentProject.Connection
  97.     stSql = "SELECT * FROM " & strSwitchboardVersion
  98.     stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
  99.     stSql = stSql & " ORDER BY [ItemNumber];"
  100.     Set rs = CreateObject("ADODB.Recordset")
  101.     rs.Open stSql, con, 1   ' 1 = adOpenKeyset
  102.  
  103.     ' If there are no options for this Switchboard Page,
  104.     ' display a message.  Otherwise, fill the page with the items.
  105.     If (rs.EOF) Then
  106.         Me![OptionLabel1].Caption = "There are no items for this switchboard page"
  107.     Else
  108.         While (Not (rs.EOF))
  109.             Me("Option" & rs![ItemNumber]).Visible = True
  110.             Me("OptionLabel" & rs![ItemNumber]).Visible = True
  111.             Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
  112.             If Me("OptionLabel" & rs![ItemNumber]).Caption = "&Previous Menu" Then
  113.                 Me("OptionLabel" & rs![ItemNumber]).FontItalic = True
  114.             ElseIf Me("OptionLabel" & rs![ItemNumber]).Caption = "&Return to Main" Then
  115.                 Me("OptionLabel" & rs![ItemNumber]).FontItalic = True
  116.             ElseIf Me("OptionLabel" & rs![ItemNumber]).Caption = "E&xit Application" Then
  117.                 Me("OptionLabel" & rs![ItemNumber]).FontBold = True
  118.             Else
  119.                 Me("OptionLabel" & rs![ItemNumber]).FontItalic = False
  120.                 Me("OptionLabel" & rs![ItemNumber]).FontBold = False
  121.             End If
  122.             rs.MoveNext
  123.         Wend
  124.     End If
  125.  
  126.     ' Close the recordset and the database.
  127.     rs.Close
  128.     Set rs = Nothing
  129.     Set con = Nothing
  130.  
  131. End Sub
  132.  
  133. '------------------------------------------------------------
  134. ' HandleButtonClick
  135. '
  136. ' This function accepts the user's selection and attempts
  137. ' to carry out the action associated with that button as
  138. ' it's defined in the Argument field in the table
  139. '------------------------------------------------------------
  140. Private Function HandleButtonClick(intBtn As Integer)
  141. ' This function is called when a button is clicked.
  142. ' intBtn indicates which button was clicked.
  143.  
  144.     ' Constants for the commands that can be executed.
  145.     Const conCmdGotoSwitchboard = 1
  146.     Const conCmdOpenFormAdd = 2
  147.     Const conCmdOpenFormBrowse = 3
  148.     Const conCmdOpenReport = 4
  149.     Const conCmdCustomizeSwitchboard = 5
  150.     Const conCmdExitApplication = 6
  151.     Const conCmdRunMacro = 7
  152.     Const conCmdRunCode = 8
  153.     Const conCmdOpenPage = 9
  154.  
  155.     ' An error that is special cased.
  156.     Const conErrDoCmdCancelled = 2501
  157.  
  158.     Dim con As Object
  159.     Dim rs As Object
  160.     Dim stSql As String
  161.     Dim strSwitchboardVersion As String
  162.  
  163.     'Determine the correct Switchboard table to use
  164.     Select Case strGlobalUserRole
  165.         Case "Auditor"
  166.             strSwitchboardVersion = "[Switchboard Items - Auditor]"
  167.         Case "OSC"
  168.             strSwitchboardVersion = "[Switchboard Items - OSC]"
  169.         Case "Reports"
  170.             strSwitchboardVersion = "[Switchboard Items - Reports]"
  171.         Case Else
  172.             GoTo HandleButtonClick_Err
  173.     End Select
  174.  
  175. On Error GoTo HandleButtonClick_Err
  176.  
  177.     ' Find the item in the Switchboard Items table
  178.     ' that corresponds to the button that was clicked.
  179.     Set con = Application.CurrentProject.Connection
  180.     Set rs = CreateObject("ADODB.Recordset")
  181.     stSql = "SELECT * FROM " & strSwitchboardVersion
  182.     stSql = stSql & " WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
  183.     rs.Open stSql, con, 1    ' 1 = adOpenKeyset
  184.  
  185.     ' If no item matches, report the error and exit the function.
  186.     If (rs.EOF) Then
  187.         MsgBox "There was an error reading the Switchboard Items table."
  188.         rs.Close
  189.         Set rs = Nothing
  190.         Set con = Nothing
  191.         Exit Function
  192.     End If
  193.  
  194.     Select Case rs![Command]
  195.  
  196.         ' Go to another switchboard.
  197.         Case conCmdGotoSwitchboard
  198.             Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]
  199.  
  200.         ' Open a form in Add mode.
  201.         Case conCmdOpenFormAdd
  202.             DoCmd.OpenForm rs![Argument], , , , acAdd
  203.  
  204.         ' Open a form.
  205.         Case conCmdOpenFormBrowse
  206.             DoCmd.OpenForm rs![Argument]
  207.  
  208.         ' Open a report.
  209.         Case conCmdOpenReport
  210.             DoCmd.OpenReport rs![Argument], acPreview
  211.  
  212.         ' Customize the Switchboard.
  213.         Case conCmdCustomizeSwitchboard
  214.             ' Handle the case where the Switchboard Manager
  215.             ' is not installed (e.g. Minimal Install).
  216.             On Error Resume Next
  217.             Application.Run "ACWZMAIN.sbm_Entry"
  218.             If (Err <> 0) Then MsgBox "Command not available."
  219.             On Error GoTo 0
  220.             ' Update the form.
  221.             Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
  222.             Me.Caption = Nz(Me![ItemText], "")
  223.             FillOptions
  224.  
  225.         ' Exit the application.
  226.         Case conCmdExitApplication
  227.             CloseCurrentDatabase
  228.  
  229.         ' Run a macro.
  230.         Case conCmdRunMacro
  231.             DoCmd.RunMacro rs![Argument]
  232.  
  233.         ' Run code.
  234.         Case conCmdRunCode
  235.             Application.Run rs![Argument]
  236.  
  237.         ' Open a Data Access Page
  238.         Case conCmdOpenPage
  239.             DoCmd.OpenDataAccessPage rs![Argument]
  240.  
  241.         ' Any other command is unrecognized.
  242.         Case Else
  243.             MsgBox "Unknown option."
  244.  
  245.     End Select
  246.  
  247.     ' Close the recordset and the database.
  248.     rs.Close
  249.  
  250. HandleButtonClick_Exit:
  251. On Error Resume Next
  252.     Set rs = Nothing
  253.     Set con = Nothing
  254.     Exit Function
  255.  
  256. HandleButtonClick_Err:
  257.     ' If the action was cancelled by the user for
  258.     ' some reason, don't display an error message.
  259.     ' Instead, resume on the next line.
  260.     If (Err = conErrDoCmdCancelled) Then
  261.         Resume Next
  262.     Else
  263.         MsgBox "There was an error executing the command.", vbCritical
  264.         Resume HandleButtonClick_Exit
  265.     End If
  266.  
  267. End Function
  268.  
Thanks,
beacon
Apr 23 '12 #1

✓ answered by beacon

Ok, so I moved the code that I had in the On_Load Event to the On_Open Event and now everything is working just the way it was before.

I'm not sure why the code worked in the On_Load Event before in 2003 and won't in 2010, but at least it's working.

Thanks,
beacon

Share this Question
Share on Google+
4 Replies


Narender Sagar
100+
P: 189
Hi Beacon,
Even I was having a 2003 version database with switchboard, and when I converted it completely in 2010 version, it was working fine. Is there any specific reason, you don't want to convert it for the time being..?
Apr 24 '12 #2

Expert Mod 2.5K+
P: 2,545
@Narender: I would be VERY cautious about converting fully to A2010 at present. A2010 switchboards are implemented using macros, not VBA, and I guess Beacon's difficulties with handling calls to his other switchboards relate to this change.

A2010 takes this approach as it fits in with its new 'publish to the web via sharepoint' facilities. Control and page handling for its new web facilities is done using its new macro capability, not VBA code.

If you look at the way A2010 designs switchboards you will see that the previous approach is not compatible.

I would be careful in experimenting with A2010. For example, if you take an A2007 database with a built-in switchboard and convert to A2010 with its new macro-based switchboard, you'll find that the switchboard is not compatible with A2007 even though the .accdb file format is nominally the same.

There is at present no means of publishing back to A2007 format from A2010, so undoing such inadvertent changes is not straightforward.

Beacon's question relates to A2003 files in .mdb format - I'd keep them as such for the present until all ramifications of the change in file format are clear.

-Stewart
Apr 24 '12 #3

beacon
100+
P: 579
@Stewart - the reasons you listed are exactly some of the reasons I didn't want to fully convert at this time.

However, I didn't see anything that would help me resolve my issue...unless you're telling me that it's no longer possible for me to do what I'm trying to do. If that's the case, I don't understand why the Switchboard I created as a test still uses VBA code behind the form or why my custom VBA functions I used to attach to the handle buttons still work.

Is there no way to get the Switchboard to handle switching to other Switchboard menus? I think the use of multiple Switchboard tables is working...it's just the command that's supposed to switch to a different Switchboard menu that doesn't appear to be functioning properly.

Thanks,
beacon
Apr 24 '12 #4

beacon
100+
P: 579
Ok, so I moved the code that I had in the On_Load Event to the On_Open Event and now everything is working just the way it was before.

I'm not sure why the code worked in the On_Load Event before in 2003 and won't in 2010, but at least it's working.

Thanks,
beacon
Apr 24 '12 #5

Post your reply

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