MS ACCESS Sql Query Returning (Run-Time Error 91)

I am making a form that has drop downs that populate based on the contents of another drop down on the form. I am setting this in the ON_Click event of the first drop down.

Dim rsa As ADODB.Recordset
Dim intResult As Integer
strSQL = "SELECT top 1(tblLicensorID.LicensorID) as Item FROM tblLicensorID INNER JOIN tblProperty ON tblLicensorID.LicensorID = tblProperty.LicensorID WHERE tblProperty.PropertyID = " & Me.cboProperty.Value
    rsa.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    intResult = rs.Fields(0)
    rsa.Close

I added a watch to look at the SQL statement, and the statement is all there. I also tried running it in a separate query (with the value that was in the drop-down box as the WHERE clause) and it returned the correct record. I'm not sure what I'm doing wrong.
BluAvi8Asked:
Who is Participating?
 
mbizupCommented:
Try adding this line as follows:


Dim rsa As ADODB.Recordset
Dim intResult As Integer
Set rsa = New ADODB.Recordset    '<----------------------------- *** add this
strSQL = "SELECT tblLicensorID.LicensorID FROM tblLicensorID RIGHT JOIN tblProperty ON tblLicensorID.LicensorID = tblProperty.LicensorID WHERE tblProperty.PropertyID = " & Me.cboProperty.Value
    rsa.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    intResult = rsa.Fields(0)
    rsa.Close
0
 
TWBitCommented:
Check your SQL string.  Put a break after it but before the rsa.Open.  When the code stops, copy strSQL and paste it into a blank query's SQL view and run it. You might see the error when pasting it, or Access might give a better description.  I'd say that the reference isn't valid.
0
 
BluAvi8Author Commented:
Actually, thats what I've already done. I copied and pasted it, and ran it. It ran just fine and only returned one result.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BluAvi8Author Commented:
oh, the SQL Statement Looks like this (copied from the srtSQL variable): SELECT tblLicensorID.LicensorID FROM tblLicensorID RIGHT JOIN tblProperty ON tblLicensorID.LicensorID = tblProperty.LicensorID WHERE tblProperty.PropertyID = 110
0
 
TWBitCommented:
Thanks for that.  But am I missing something? That SQL statement isn't close to what is being created in code.
0
 
mbizupCommented:
Error 91 is "Object Variable Not Set".  That means some object variable is unrecognized (in this case, a recordset object).  

It looks like you might have a typo in your code:
>Dim rsa As ADODB.Recordset            '<---- you declare rsa
>intResult = rs.Fields(0)      '<--- but you are using rs here.

Try this instead:

intResult = rsa.Fields(0)   '<---- change to rsa

0
 
BluAvi8Author Commented:
ah, i appologize. While wrestling with this problem ive changed a few things in the code and forgot take that into consideration when i sent the sql Snippet. The code now looks like this:

Dim rsa As ADODB.Recordset
Dim intResult As Integer
strSQL = "SELECT tblLicensorID.LicensorID FROM tblLicensorID RIGHT JOIN tblProperty ON tblLicensorID.LicensorID = tblProperty.LicensorID WHERE tblProperty.PropertyID = " & Me.cboProperty.Value
    rsa.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    intResult = rsa.Fields(0)
    rsa.Close

Either way, The SQL that has been produced (by the old and new code) worked in it's own Query.
0
 
BluAvi8Author Commented:
I also noticed the typo. I corrected it (see code above), but am still having the same problem...
0
 
mbizupCommented:
What line is the debugger stopping on?
0
 
BluAvi8Author Commented:
rsa.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
0
 
TWBitCommented:
Here is a msoft link showing several ways to open the recordset. Have you declared and set everything?

http://support.microsoft.com/kb/168336
0
 
BluAvi8Author Commented:
Thanks a Ton
0
 
mbizupCommented:
Glad to help out!
And thanks for the "grading comments" :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.