Help! Error: Object Variable or With Block variable Not Set

[Iashia06]

Hi,

I've created a login form based on a Users table. My intention is for the user to type in their username/password and the VBA is supposed to loop through the table until that username/password record is reached. Once the username/password is verified, the app is supposed to Open an Approval form. However, I keep getting an error message. Can some please help. The code is as follows:

Dim rs As ADODB.Recordset
rs.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\" & _
"CapitalProjects$\CapitalProjects.mdb;"

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Users;"
rs.MoveFirst
Do
Debug.Print rs.Fields(0), rs.Fields(1)
If rs.EOF = True Or rs.BOF = True Then
Exit Sub
End If
If Not txtUserName.Value > "" Or
(Not txtPassword.Value > "") Then
MsgBox "You cannot enter a blank
Username/Password. Try again."
txtUserName.SetFocus
Exit Sub
ElseIf UserName = txtUserName.Value And
Password = txtPassword.Value Then
MsgBox "You are logged in as " & UserName
stDocName = "Approval"
stDocName2 = "Login"
DoCmd.Close acForm, "Login"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
End If
rs.MoveNext
Loop
If txtUserName.Value <> UserName Or
txtPassword.Value <> Password Then
MsgBox "You are not authorized to access this form!"
MsgBox UserName
stDocName = "Selection"
stDocName2 = "Login"
DoCmd.Close acForm, "Login"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
End If
rs.Close
Set rs = Nothing

 

[jebry]

Hi!

Try something like this:

Dim rs As ADODB.Recordset
rs.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\" & _
"CapitalProjects$\CapitalProjects.mdb;"

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Users Where UserName = '" & tstUserName.Value & "';"

If rs.EOF=True And rs.BOF = True Then
MsgBox "The name you submitted is not in the database, please check your spelling and try again. If you need help please contact the system administrator."
Else
If rs.Password = txtPassword.Value Then
Open your form
Else
MsgBox "The password you entered does not match the user. Please try again. If you need your password reset, contact the system administrator"
End If
End If

Set rs = Nothing

You can also add a form level counter variable that will allow the user a limited number of attempts before they are kicked out of the database.

hth




Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CaptainD]

Why not change your select statment to:
"Select [UserName], [Password] From Users Where [UserName] = Trim(txtUserName.Text)" and then compare the password.

Or

You could use "Select Count(*) From Users Where [UserName] = Trim(txtUserName.text)" and [Password] = Trim(txtPassword.Text)"
If it returns 0 then Username and / or password is incorrect.

Why pull all users across and then sort through the list?

 

[Craig0201]

Dim rs As ADODB.Recordset
rs.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\" & _
"CapitalProjects$\CapitalProjects.mdb;"

Set rs = New ADODB.Recordset

Accesses rs before it's initialised.

 

[JoeAtWork]

To add to the suggestions, I would check to see if the Login or Password textboxes are blank before doing any database calls (there's no need if the user hasn't provided the criteria to validate against).