p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Object Variable or With Block Variable Not Set (http://p2p.wrox.com/showthread.php?t=42858)

Iashia06 May 22nd, 2006 08:36 AM

Object Variable or With Block Variable Not Set
 
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



mmcdonal May 22nd, 2006 10:24 AM

Which line is giving you the error?

I see you might want to remove the ";" at the end of the SQL statement if it is an Access back end.

Also, you might want to limit the SQL statement to:

"SELECT * FROM Users WHERE [UserName] = " & "'" & sUserNameVariable & "'"

This will give you a recordset with 1 record, which is easier to work with. Start with:

If rs.RecordCount = 0 Then
   Exit Sub
End If



mmcdonal


All times are GMT -4. The time now is 02:32 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.