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...ju st 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 strGlobalUserRo le variable and this variable is used to determine the correct table to use as the record source):
Thanks,
beacon
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...ju st 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 strGlobalUserRo le variable and this variable is used to determine the correct table to use as the record source):
Code:
Option Compare Database
Option Explicit
'------------------------------------------------------------
' Form_Load
'
' This subroutine changes the color based on the active
' user role and sets the record source to the correct table
'------------------------------------------------------------
Private Sub Form_Load()
Select Case strGlobalUserRole
Case "Auditor"
Me.RecordSource = "Switchboard Items - Auditor"
Me.HorizontalHeaderBox.BackColor = RGB(51, 51, 153)
Case "OSC"
Me.RecordSource = "Switchboard Items - OSC"
Me.HorizontalHeaderBox.BackColor = RGB(157, 2, 2)
Case "Reports"
Me.RecordSource = "Switchboard Items - Reports"
Me.HorizontalHeaderBox.BackColor = RGB(140, 0, 140)
End Select
End Sub
'------------------------------------------------------------
' Form_Open
'
' This subroutine sets the Switchboard to the default
' switchboard item
'------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
End Sub
'------------------------------------------------------------
' Form_Current
'
' This subroutine sets the caption of the current
' switchboard to the related item in the table and calls
' the FillOptions subroutine
'------------------------------------------------------------
Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub
'------------------------------------------------------------
' FillOptions
'
' This subroutine loops through the items in the table
' that match the current switchboard ID and outputs the
' name of the item to the associated button on the form
'------------------------------------------------------------
Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 8
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer
Dim strSwitchboardVersion As String
'Determine the correct Switchboard table to use
Select Case strGlobalUserRole
Case "Auditor"
strSwitchboardVersion = "[Switchboard Items - Auditor]"
Case "OSC"
strSwitchboardVersion = "[Switchboard Items - OSC]"
Case "Reports"
strSwitchboardVersion = "[Switchboard Items - Reports]"
End Select
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM " & strSwitchboardVersion
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
If Me("OptionLabel" & rs![ItemNumber]).Caption = "&Previous Menu" Then
Me("OptionLabel" & rs![ItemNumber]).FontItalic = True
ElseIf Me("OptionLabel" & rs![ItemNumber]).Caption = "&Return to Main" Then
Me("OptionLabel" & rs![ItemNumber]).FontItalic = True
ElseIf Me("OptionLabel" & rs![ItemNumber]).Caption = "E&xit Application" Then
Me("OptionLabel" & rs![ItemNumber]).FontBold = True
Else
Me("OptionLabel" & rs![ItemNumber]).FontItalic = False
Me("OptionLabel" & rs![ItemNumber]).FontBold = False
End If
rs.MoveNext
Wend
End If
' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub
'------------------------------------------------------------
' HandleButtonClick
'
' This function accepts the user's selection and attempts
' to carry out the action associated with that button as
' it's defined in the Argument field in the table
'------------------------------------------------------------
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
' An error that is special cased.
Const conErrDoCmdCancelled = 2501
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim strSwitchboardVersion As String
'Determine the correct Switchboard table to use
Select Case strGlobalUserRole
Case "Auditor"
strSwitchboardVersion = "[Switchboard Items - Auditor]"
Case "OSC"
strSwitchboardVersion = "[Switchboard Items - OSC]"
Case "Reports"
strSwitchboardVersion = "[Switchboard Items - Reports]"
Case Else
GoTo HandleButtonClick_Err
End Select
On Error GoTo HandleButtonClick_Err
' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM " & strSwitchboardVersion
stSql = stSql & " WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If
Select Case rs![Command]
' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]
' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd
' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]
' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview
' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions
' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase
' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]
' Run code.
Case conCmdRunCode
Application.Run rs![Argument]
' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]
' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."
End Select
' Close the recordset and the database.
rs.Close
HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function
HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If
End Function
beacon
Comment