Error in password protecting a form

[Dawg62]

I am trying to password protect a form in my database. I'm using SQL as my back end. The password form comes up as I open the form I want. Once I enter the password it sends me to visual basic. When I hover over the script I get some errors. The errors are next to the scipt in brackets in area 16. Here is the password script I'm using so you have an idea what I've done:

----------------------------------

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 procedureublic 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 = 1 To 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
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."
Me!Text0.SetFocus
Else
MyPassword = Me!Text0.Value
DoCmd.Close acForm, "frmPassword"
End If


14. Open the Orders form in Design view.
15. If the property sheet is not visible, click Properties on the View menu.
16. Type the following event procedure in the module for the OnOpen property of the formrivate Sub Form_Open(Cancel as Integer)
Dim Hold As Variant
Dim tmpKey As Long
Dim I As Integer
Dim rs As DAO.Recordset (rs = Nothing)
Dim db As DAO.Database (dm = Nothing)

' On Error GoTo Error_Handler
' Prompt the user for the Password.
DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
Hold = MyPassword
' Open the table that contains the password.
Set db = CurrentDb
Set rs = db.OpenRecordset("tblPassword", dbOpenTable) (ERROR is here)
rs.Index = "PrimaryKey"
rs.Seek "=", Me.Name
If rs.NoMatch Then
MsgBox "Sorry cannot find password information. Try Again"
Cancel = -1
Else
' Test to see if the key generated matches the key in
' the table; if there is not a match, stop the form
' from opening.
If Not (rs![keycode] = KeyCode(Cstr(Hold))) Then
MsgBox "Sorry you entered the wrong password." & _
"Try again.", vbOKOnly, "Incorrect Password"
Cancel = -1
End If
End If
rs.Close
db.Close
Exit Sub

Error_Handler:
MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
Exit Sub
End Sub

-------------------------------------

I've fixed some of the problems I had but I am stumped on this one. It seems like it can't reference the database but I'm not sure how to do that. TIA

 

[PHV]

Have you referenced the Microsoft DAO 3.x Object Library ?
menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dawg62]

Yes, it was the first thing I checked. I found a script that is put into a command button that protects the form with a password but it is not what I'm looking for. I want to be able to go into a hidden table and change passwords when I need to but everything I find is for .mdb database types instead of .adp type project databases.

 

[PHV]

For me, if you're playing with ADP your best choice is ADODB instead of DAO ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dawg62]

Would you happen to know where I can get a good script to do what I need or how to rewrite this one?