Dual Password for a Separate Sub-Switchboard 1

[awl]

I am using Microsoft Office Access 2003. Does Microsoft Access have the capability of using a dual password entry? I have a current database that has a single password and when used, the main switchboard opens. The main switchboard has several sub-switchboards. Currently, the database is set up for editing selected tables via forms. New Idea: On the main switchboard, I wanted to link a sub-switchboard which will contain a separate password which will enable a select group of individuals having access to update the tables. All other individuals entering the main switchboard will be linked to the reports, thus not having access to edit the tables. Is this possible? Thanks…..

 

[MD5150]

It's possible. I do it in my apps. Most of my customers don't want to spend a lot of money so we rarely bother with implementing user/group security and just lock down the interface from getting to the db container, create an "admin" switchboard, and throw a password on it.

You can edit the code behind the switchboard, find which button number your "admin" button is, and either hard code a password or store one in a table.

I store mine in a hidden table and use a "enter password" pop-up form to validate. If wrong password, then exit out of the switchboard code before it goes to the admin switchboard. Hope this helps.

Mike Dorthick

 

[awl]

Thank you for the initial response. I am not an experienced VBA code user. Is it possible to provide a sample of what the code behind the switchboard would be? Maybe there is already a sample db that has this coding; if possible, would you know of that link? Thanks very much.

 

[MD5150]

Well, there are various ways to do it, but I will note a few options here for you. Most important before doing this is to show you where you'll insert the code in the switchboards code module. In the Switchboard's code module, scroll down to the "HandleButtonClick" function. For clarity, I will first paste the entire function with comments where the additional code will go. Look for the "'*******..."

Keep in mind that in these samples, the button number and switchboard numbers are hard coded, so if you add buttons or change things around on your switchboard, then you'll need to edit the code. A better way would probably be to look up the admin menu button number in the Switchboard table during the password validation, but I'm often too lazy and just change the number manually.

Anyway, here's the switchboard function.

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
    

    '************* BEGIN PASSWORD PROMPT CODE ***********************************

    'Place your code here....

    '************* END PASSWORD PROMPT CODE ***********************************
    


    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

Okay, now for some options.


OPTION 1:
Use a simple InputBox function to prompt for a password. Where indicated in the switchboard code module above, place the following code:

    If intBtn = 5 Then 'The admin button
        If SwitchboardID = 1 Then 'The main switchboard
            If IsNull(Me.txtCurrentPassword) Then
                MsgBox "No current Password recorded!  Notify your System Administrator", vbExclamation, "ERROR"
                rs.Close
                Set rs = Nothing
                Set con = Nothing
                Exit Function
            Else
		Dim x As String
		x = InputBox("Please enter password")
		If Not x = "yourpassword" Then
			MsgBox "Incorrect Password", vbExclamation, "OOPS!"
		        rs.Close
		        Set rs = Nothing
		        Set con = Nothing
		        Exit Function
		End If
        End if
    End if

But the InputBox won't mask the entered password, so using a custom form for the password prompt looks best and hides the password entered. But unfortunately, you have to then deal with passing the "entered password" between forms.


OPTION 2:
Very little coding, except for some form control references. Just RecordSource your switchboard to your password table and store the password in a hidden locked control on the switchboard. Then compare the entered password to the hidden password control. Also put a hidden control for holding the password entered.

The code behind your "enter password" pop-up form would simply be:

Private Sub txtAdminPassword_AfterUpdate()
  Forms!frm_Switchboard!txtPasswordEntered = Me.txtAdminPassword
  DoCmd.Close
End Sub

This throws the entered password over to the switchboard for evaluation.

Then in the Switchboard's code module, paste the following code where indicated above:

    '************* BEGIN PASSWORD PROMPT CODE ***********************************
    If intBtn = 5 Then 'admin button
        If SwitchboardID = 1 Then 'main switchboard
            If IsNull(Me.txtCurrentPassword) Then 'the locked hidden password control on the switchboard
                MsgBox "No current Password!  Notify your System Administrator", vbExclamation, "ERROR"
                rs.Close
                Set rs = Nothing
                Set con = Nothing
                Exit Function
            Else
                DoCmd.OpenForm "frm_AdministrationEntry", , , , , acDialog 'open the password form
                    If IsNull(Me.txtPasswordEntered) Then
                        rs.Close
                        Set rs = Nothing
                        Set con = Nothing
                        Exit Function
                    Else
                        If Me.txtPasswordEntered = Me.txtCurrentPassword Then
                            Me.txtPasswordEntered = Null
                        Else
                            MsgBox "Incorrect Password", vbInformation, "ERROR"
                            rs.Close
                            Set rs = Nothing
                            Set con = Nothing
                            Exit Function
                        End If
                    End If
            End If
         End If
    End If
    '************* END PASSWORD PROMPT CODE ***********************************

But this method maintains an open link to the password table via your switchboard. It would probably be better to use a DLookUp function instead to throw the current password over to the hidden switchboard controls.


OPTION 3:
Create a global variable in a public module:

Public strGlobalPassword As String

In the code module behind the "password prompt" pop-up form put:

Private Sub cmdCancel_Click()
    DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.txtPassword.SetFocus
End Sub

Private Sub txtPassword_AfterUpdate()
    Dim strEnteredPassword As String
        strEnteredPassword = Me.txtPassword
    If Not IsNull(txtPassword) Then
        strGlobalPassword = strEnteredPassword
        DoCmd.Close
    End If
End Sub

In a public module add a function. In this case I had:

Function AdminPassword(Password As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strTablePassword

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tbl_adm_Password")
        
rs.MoveFirst
    strTablePassword = rs!Password
rs.Close
Set rs = Nothing
Set db = Nothing

If Password = strTablePassword Then
    AdminPassword = "Good"
Else
    AdminPassword = "invalid"
End If

End Function

Then in the Switchboard's code module, paste the following code where indicated above:

    '************* BEGIN PASSWORD PROMPT CODE ***********************************
    'Prompt for password
    If intBtn = 4 Then 'admin button
        If SwitchboardID = 1 Then 'main Switchboard
            Dim strPassword As String
            DoCmd.OpenForm "frm_PasswordEveryone", , , , , acDialog
            strPassword = AdminPassword(strGlobalPassword)
                If strPassword = "Good" Then
                    'Do nothing and move to the admin switchboard
                Else
                    MsgBox "Invalid Password", vbInformation, "PASSWORD DENIED"
                    strGlobalPassword = ""
                    rs.Close
                    Set rs = Nothing
                    Set con = Nothing
                    Exit Function
                End If
         End If
    End If
    '************* END PASSWORD PROMPT CODE ***********************************

You'll also eventually want to create a "change password" form.

I'm not an expert coder and I did this a while back, so I'm sure that there are many ways to validate the password. Some of the expert coders on the list probably would have used better and more efficient code. When time permits I'd certainly like to clean this up a bit and make it more professional and more dynamic in regards to switchboard editing. But you get the idea. Good Luck.

Mike Dorthick

 

[awl]

Thank you Mike for your much "detailed" suggestions. I will go through this in detail to understand all the options and try to put it to work. Again thanks for your responsiveness.