'NOTE: The sample code in this article uses Microsoft Data 
'Access Objects. For this code to run properly, you must reference 
'the Microsoft DAO 3.6 Object Library. To do so, click References 
'on the Tools menu in the Visual Basic Editor, and make sure
'that the Microsoft DAO 3.6 Object Library check box is selected.

'Using Code to Password Protect a Form
'By using code, you can prompt for a password when a user 
'opens a form or a report. If the correct password is entered, 
'the form or the report is opened.

'The following example shows you how you can password protect 
'the Orders form in the sample database Northwind.mdb: 
'1.	Start Access and then open the sample 
            'database Northwind.mdb.
'2.	Press ALT+F11 to start the Microsoft Visual Basic editor.
'3.	On the Insert menu, click Module.
'4.	In the module sheet, type the following procedure:

Public MyPassword Public Function KeyCode(Password As String) As Long 
' This function will produce a unique key for the ' string that is passed 
 'in as the Password. 

Dim I As Integer
Dim Hold As Long 

For I = 1To Len(Password)
 Select Case (Asc(Left(Password, 1)) * I) Mod 4
 Case Is = 0
 Hold = Hold + (Asc(Mid(Password, I, 1)) * I) 
Case Is = 1
 Hold = Hold - (Asc(Mid(Password, I, 1)) * I)
 Case Is = 2
 Hold = Hold + (Asc(Mid(Password, I, 1)) * _ (I - Asc(Mid(Password, I, 1)))) 
Case Is = 3 
Hold = Hold - (Asc(Mid(Password, I, 1)) * _ (I + Len(Password)))
 End Select 
 Next I
 KeyCode = Hold
 End Function

'5.	Press ALT+F11 to return to Access.
'6.	In the Database window, under Objects, click Tables, 
            'and then click New.
'7.	In the New Table dialog box, double-click Design View.
'8.	Create a new table as follows:

'Table: tblPassword
'Field Name: ObjectName Data Type: Text Field Size: 50
'Field Name: KeyCode Data Type: Text Field Size:
'25 Input Mask: Password Table Properties: tblPassword 
'PrimaryKey: ObjectName

'9.	Open the tblPassword table and then enter the following data:

'ObjectName: Orders (ObjectName is the name of the form you 
'want to go to)
 'KeyCode: 2818

'10.	Create a new form in design view and save the form 
            'as frmPassword.
'11.	Add a single textbox to frmPassword called Text0, 
            'and a command button called CheckPassword.
'12.	Set the Input Mask property of Text0 to "PASSWORD" 
            '(minus the quotation marks).
'13.	Add the following code to the OnClick Event of 
            'the CheckPassword button and then save the form:

If IsNull(Forms!frmPassword!Text0.Value) Then
 MsgBox "You cannot enter a blank Password. Try again." 
MyPassword = Me!Text0.Value 
DoCmd.Close acForm, "frmPassword" 
End If

'14.	Open Switchboard Table and Print it off You will need 
            'this information
'15.	Open the Main Switchboard form in Design view. 
            'Then open the VB Editor.
'16.	Apply the following code where indicated in the code. 
            'The thing you need to remember is the SwitchboardID 
            'is 0 for default and the ItemNumber is 2 for the 3rd button 
            'on the main switchboard. By changing these in the 
            'IF statement you can direct which button to have 
            'the Password on.

    Select Case rs![Command]
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
        Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]

 'Below is the modified code I put into the Switch board
    Dim Hold As Variant
    Dim tmpKey As Long
    Dim I As Integer
    Dim rs1 As DAO.Recordset
    Dim db As DAO.Database
    Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]
‘ Below is where you set the button

    If Val([ItemNumber]) = 0 And Val([SwitchboardID]) = 2 Then
            DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
            Hold = MyPassword
            Set db = CurrentDb
            Set rs1 = db.OpenRecordset("tblPassword", dbOpenTable)
           		 If rs1.NoMatch Then
           			 MsgBox "Sorry cannot find password    information. Try Again"
          			 Cancel = -1

            ' Move to the switchboard page that is marked as the default.

           			 Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
           			 Me.FilterOn = True

    ElseIf Not (rs1![KeyCode] = KeyCode(CStr(Hold))) Then
           			 MsgBox "Sorry password does not match Key Code." & _
           			 "Try again.", vbOKOnly, "Incorrect Password"
           			 Cancel = -1

            ' Move to the switchboard page that is marked as the default.

            		Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
           			Me.FilterOn = True
        	 End If
   End If

'17.	Close and then save the Switchboard.
'18.	Open the Switchboard and select your button it shoud 
            'open the frmPassword

'Notice that the Orders form opens. The KeyCode that is generated 
'by PASSWORD matches the KeyCode in the tblPassword table, 
'and is dependent on the case of the letters in the password entered.

'19.	Close and then reopen the Orders form and then 
            'type PassWord when you are prompted for a password.

'Notice that you receive the message: 

'Sorry you entered the wrong password. Try again. 
'The Switchboard does not open and is redirected to 
'Default Switchboard because the password procedure is case-sensitive.

'20.	To determine what the corresponding KeyCode is for 
            'a particular string, type the following in the Immediate 
            'window and then press ENTER: 


'The earlier example returns 5864.

'21.	To hide the tblPassword table in the Database window, 
            'right-click the tblPassword table, and then click Properties. 
            'In the Properties window, click to select the Hidden check 
            'box, and then click OK.

11 Years
Discussion Span
Last Post by rose_an

yeaq good work.but am getting an eror on this peice of line what do i do
Public MyPassword Public Function KeyCode(Password As String) As Long


How can the password, "PASSWORD" enhance security? Surely the code should allow the use of any text for a password, or am I being dumb today?


Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.