Solved

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

Posted on 2007-11-29
13
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 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