Error #91 Object Variable or with block variable not set

[Brian62]

I'm running an access 2003 project and I'm trying to password protect one of my forms but I keep getting that error when I open the form. Once I click OK the form opens that I want to protect. I followed this to the "T". Here is the script I'm using.

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 = 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 form:Private Sub Form_Open(Cancel as Integer)

Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database
 
   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)
   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

[MartinLiss]

Moved. I also added some VBCode tags to make it easier to read.

[zaza]

Welcome to the Forums

Can you tell us on which line you are getting the error? When the error message appears, push Ctrl-Break to go to the debugger and look at which line you are at. It will be on this line that the error is occurring.
Another trick is to put a line like:

Msgbox("Cobblers")

as the first line in your Open proc. Again, you can then push Ctrl-Break to ge the debugger up and step through line by line (using F8) to see where the error occurs.

Offhand, I can't see where you've defined DoCmd, for example. It will be a problem like this which is causing the error. Try it and see what happens.


zaza

[Brian62]

Here is the script that I'm using: Private Sub Form_Open(Cancel As Integer)
Dim Hold As Variant
Dim tmpKey As Long
Dim I As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database

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)
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 (The error is here. If I delete it then the error is the next Line)
End Sub

[zaza]

Ah, I neglected to spot your error handler. Comment out the error handling line:

On Error GoTo Error_Handler

and then the actual error line will become apparent...

zaza

[Brian62]

Thanks. It took care of that error but now I have another. The error is in bold.

Private Sub Form_Open(Cancel As Integer)
Dim Hold As Variant
Dim tmpKey As Long
Dim I As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database

' 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)
rs.Index = "PrimaryKey"
rs.Seek "=", Me.Name
If rs.NoMatch Then
MsgBox "You entered the wrong password. Please 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

[zaza]

Hover your mouse over db when the error has occurred. I bet it says "db = Nothing". Where have you set CurrentDb?

[Brian62]

Nope. It says: dbOpenTable =1

I don't understand what you mean as to where I set currentdb.

[zaza]

No, not dbopentable. db. On the previous line, you Set db = CurrentDb. What is current db, and where are you getting it from? Since this is the form open event, you'd better have defined it somewhere...

[Brian62]

This is what I used to setup the password protection on my form:

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 = 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 form:Private Sub Form_Open(Cancel as Integer)
Dim Hold As Variant
Dim tmpKey As Long
Dim I As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database

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)
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

[zaza]

The object variable Not set error on that line almost certainly refers to the db. If, when you get the error, you hover the mouse over "CurrentDb" on the previous line, does it say "Nothing"?
If you right-click "CurrentDb" and choose Definition, does it go anywhere? If you do a search for "CurrentDb" in the current project (Edit, Find menu), does it come up anywhere else? I can only think that this CurrentDb variable is not being allocated somewhere.

[Brian62]

When I hover over "Set db = CurrentDb" it does say db=nothing. When I right click on Set db = CurrentDb it goes to object browser saying currentDB on the Members of "Application" but that's about it. I hope that is what you are asking for.

[zaza]

Can you do a search for CurrentDb in the entire project? Where, apart from there, does it come up? You are attempting to set the database in this piece of code to whatever database is being referenced by CurrentDb. If you have not set CurrentDb to be anything, then it will not work.

[Brian62]

I checked the entire project and CurrentDb is only referenced once. How would I setup the CurrentDb to reference to the name of the database? The form does comes up after the error.

[Brian62]

Here is some more info. I wrote in DOA infront of the recordset and database to keep it from erroring out on:

If rs.NoMatch Then
MsgBox "Invalid Password. Try Again"

When I hover over recordset and database it shows rs = nothing and db = nothing and when I hover over (If rs.NoMatch Then) it shows:

rs.noMatch = <objectvariable or with block variable not set>