Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 22nd, 2006, 08:36 AM
Registered User
 
Join Date: Apr 2006
Location: Springfield, MA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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


  #2 (permalink)  
Old May 22nd, 2006, 10:24 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Object variable or With block variable not set haidee_mccaffrey Classic ASP Professional 5 March 8th, 2007 03:34 PM
"object variable or with block variable not set" netfresher ASP.NET 1.0 and 1.1 Basics 1 June 12th, 2006 03:50 PM
object variable or with block variable not set Aoude BOOK: Beginning VB.NET Databases 1 February 24th, 2006 05:21 PM
Object variable or With block variable not set tparrish VB Databases Basics 1 May 25th, 2005 10:25 AM





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