Object variable error in vba

Question

Trying to create a username/password validation form. getting the the following error "Object variable or with block variable not set" when i click login button i.e comamand1_click(). when i click debug it points to

> rs.ActiveConnection = conn

The code that i typed:

Dim a
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Command1_Click()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\VB Project\logindetails1.mdb"
conn.Open

If conn = Null Then
    MsgBox ("no db")
    End
End If

rs.ActiveConnection = conn
rs.Source = "select * from login"
rs.Open

rs.MoveFirst
While Not rs.EOF
If Text1.Text = rs.Fields("id") And Text2.Text = rs.Fields("password") Then
    a = 1
Else
    a = 0
End If
Wend
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

If a = 1 Then
    MsgBox ("Login Successful!!")
Else
    MsgBox ("Invalid Details!!")
End If
End Sub

Private Sub Command2_Click()
Text1.Text = ""
Text2.Text = ""
End Sub

Private Sub Command3_Click()
End
End Sub

I've add the same error before and I did a replace in the module of conn to sConn, then back and it worked. — nutsch
– nutsch, Commented Oct 29, 2012 at 16:35

qxn · Accepted Answer · 2012-10-29 16:35:35Z

6

I think your rs (ADODB.Recordset) variable is Nothing.

You need to create the object.

Set rs = New ADODB.Recordset

Also, I think you'll need to use the Set keyword when setting the recordset's connection:

Set rs.ActiveConnection = conn

answered Oct 29, 2012 at 16:35

qxn

17.6k3 gold badges55 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

theIronGiant Over a year ago

I did create the rs object at the very start of the code. Anyways, using set had no effect. Getting the same error on the same line.

qxn Over a year ago

@theIronGiant -- Creating rs isn't in your example. In your example, your "rs" variable will always be nothing (and even if it IS created somewhere at the start of your code, you are setting it to nothing at the end of your Command1_Click function, so it will be Nothing the next time that function is run). To test if it is really Nothing, you can do this: If rs Is Nothing Then MsgBox "rs is nothing"

Collectives™ on Stack Overflow

Object variable error in vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related