Apply security to command buttons on Switchboard form 1

[BxWill]

Trying to resolve the following without much success -
Have a switchboard form in MS Access 2003 db (that I inherited approx. 2 months ago) with 5 buttons titled Contacts, Employees, To Do List, Days Since Last Contact, Contact Count, and Exit.

The db is accessible by Managers and employees.
The objective is to enable the managers to have access to all the buttons while limiting the employees just to the buttons captioned "To Do List", "Contacts", and "Exit."

Based on the reading of several books, the easiest way to accomplish is to have a password for the command buttons.

Given the code below, how do I incorporate a request for a password when the Managers or Employees click on each of the command buttons?

Also, is there a easier way to accomplish the objective?

Thanks in advance for any assistance.

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

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 [Switchboard Items] "
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

 

[Fenrirshowl]

There seems to be no takers for this one so I will give what help I can with my limited experience of Access (so this will be simplistic).

I take it you know how to amend the switchboard to add/delete buttons to activate other switchboards or to show forms?

If this is the case, I would:
1) delete the buttons you want to restrict and add a button to open a new form
2) create a new switchboard to house the restricted options
3) create a new FORM for password entry.

The new form can have a password prompt and OK and CANCEL buttons. The CANCEL button can close the active form and take you back to the original switchboard (which you may have closed or not).
Clicking OK runs code to compare the text in the textbox to a hardcoded password (or a table entry if you want to be able to record it/them) - if it matches then close the current form and open a new switchboard with the restricted options on it, if it doesn't match it can run the code behind the cancel button.

Would this be too simplistic?

Regards

Fen