Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-29
13
Medium Priority
?
313 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 800 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

662 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