Solved

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

Posted on 2007-11-29
13
307 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:BluAvi8
  • 6
  • 4
  • 3
13 Comments
 
LVL 11

Expert Comment

by:TWBit
ID: 20377593
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
 

Author Comment

by:BluAvi8
ID: 20377636
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
 

Author Comment

by:BluAvi8
ID: 20377666
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Expert Comment

by:TWBit
ID: 20377709
Thanks for that.  But am I missing something? That SQL statement isn't close to what is being created in code.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20377766
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
 

Author Comment

by:BluAvi8
ID: 20377771
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
 

Author Comment

by:BluAvi8
ID: 20377779
I also noticed the typo. I corrected it (see code above), but am still having the same problem...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20377785
What line is the debugger stopping on?
0
 

Author Comment

by:BluAvi8
ID: 20377830
rsa.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
0
 
LVL 61

Accepted Solution

by:
mbizup earned 200 total points
ID: 20377881
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
 
LVL 11

Expert Comment

by:TWBit
ID: 20377896
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
 

Author Closing Comment

by:BluAvi8
ID: 31411803
Thanks a Ton
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20378106
Glad to help out!
And thanks for the "grading comments" :-)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 43
Dlookup MSACCESS 5 25
Query Syntax 17 34
T-SQL Default value in Select? 5 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question