Solved

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

Posted on 2007-11-29
13
304 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now