Hi Simon,
Sure, we can do this pretty easily. Just remember that this technique for
password protecting forms and reports can be easily circumvented by
people with sufficient Access knowledge. For the *best* security you
should use User Level Security. Just thought I should point that out.
Ok, moving on. Follow the steps below to achieve your goal:
(These steps assume you are following article 209871 to the letter)
1. Make a backup of your database first
2. Repeat step 1 (don't make me come over there Simon <g>)
3. Open the tblPassword in Design View
4. Remove the PrimaryKey for ObjectName
5. Create a compound PrimaryKey using ObjectName and KeyCode
Double check the Indexes - they should look exactly like this:
Index Name Field Name Sort Order
PrimaryKey ObjectName Ascending
KeyCode Ascending
(at the bottom of the Index box you should see)
Primary Yes
Unique Yes
Ignore Nulls No
6. Close and save the changes to the table
7. Add additional records for one of your forms or reports. Access
should allow you enter multiple records for each object, provided of
course, you have different KeyCodes.
8. Make sure you have a referenc set to the DAO object library in using
Access 2000 or 2002. You probably already have it anyway.
9. Open one of your test forms or reports and REPLACE the Form_Open
code with the following (watch out for any line wrapping):
'*************Code Start**************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
' This code is from Microsoft Knowledge Base Article 209871
'
http://support.microsoft.com/?id=209871
'
' Code Adapted by Jeff Conrad - Access Junkie
' Copyright © 2005 Conrad Systems Development
'
' Adapted code allows for more than one password for objects
' Need to make a compound PrimaryKey using ObjectName
' and KeyCode
'
' You are free to use this code in any projects providing
' you agree to the following two conditions:
' 1. This copyright information remains intact
' 2. You admit you are an Access Junkie
' (Why else would you be looking at this?)
Dim varHold As Variant
Dim lngTmpKey As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strObjectName As String
' Capture the name of this object
strObjectName = Me.Name
' Prompt the user for the Password.
DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
' Assign the password they entered to our variant
varHold = MyPassword
' Run the module code to covert the password to a number
lngTmpKey = KeyCode(CStr(varHold))
' Open the table that contains the password information
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblPassword", dbOpenTable)
' Look for a PrimaryKey match using object name and password they used
rst.Index = "PrimaryKey"
rst.Seek "=", strObjectName, lngTmpKey
If rst.NoMatch Then
' No password match found in the table for this object
MsgBox "The password you have entered is incorrect." _
& vbNewLine & "Please try again or contact an " _
& "Administrator.", vbExclamation + vbOKOnly, _
"Incorrect Password"
' Stop the form from opening
Cancel = True
End If
ExitPoint:
' Cleanup Code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub
ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & err.Number _
& vbNewLine & "Error Description: " & err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
'*************Code Start**************
10. Compile the code, save, and close the form/report.
11. Now test opening the form/report with the different passwords
you entered into the table. The form/report will now allow more than
one password to be entered (provided it matches of course).
12. Repeat copying/pasting the Open code above to all the forms and reports
that you want to password protect.
That's it!
