Solved

Recordset.MoveLast - gives runtime error "Object variable or with block variable not set.

Posted on 2004-11-01
386 Views
Last Modified: 2010-05-18
I am using the following code to create and open a recordset, recrd.  When I try to move to the last record, it gives me the mentioned runtime error message.  The code above it has no With block. The expression recrd.MoveLast will work many lines after the code written below:

Dim qryRD As QueryDef
Dim recrd As Recordset
Dim strSQL As String

'Construct SQL statement for qryRD (Relevant Diagnoses)
    strSQL = "SELECT LastName, FirstName, tblRelevantDiagnoses.* " & _
           " FROM tblPatientInformation, tblRelevantDiagnoses" & _
           " WHERE (tblRelevantDiagnoses.PatientID = tblPatientInformation.PatientID) AND (tblRelevantDiagnoses.PatientID = "
    strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
    strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.EvaluationID;"

    'Create qryRD
    Set qryRD = CurrentDb.CreateQueryDef("qryRD", strSQL)
    'Create qryRD Recordset
    Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)

    recrd.MoveLast
   
    'Hospice Related Diagnoses - Header
    With objWD.Selection
        .Font.Bold = True
        .Font.Size = 12
        .TypeText vbLf + vbLf + vbLf
        .ParagraphFormat.Alignment = wdAlignParagraphCenter
        .TypeText "Hospice Related Diagnoses" + vbLf + vbLf
    End With

Thanks,
Jim Botts
0
Question by:jbotts
    16 Comments
     
    LVL 34

    Accepted Solution

    by:
    Hi jbotts,

    wooo,... there is no need to create a querydef to do this...

    also, you should specify if you are using ADO or DAO (u are using DAO from the code above)

    also you never specified where dbsCurr comes from, maybe this isnt set

    try this

    Dim recrd As dao.Recordset
    dim dbsCurr as dao.database
    Dim strSQL As String

    set dbsCurr = currentdb
    'Construct SQL statement for qryRD (Relevant Diagnoses)
        strSQL = "SELECT LastName, FirstName, tblRelevantDiagnoses.* " & _
               " FROM tblPatientInformation, tblRelevantDiagnoses" & _
               " WHERE (tblRelevantDiagnoses.PatientID = tblPatientInformation.PatientID) AND (tblRelevantDiagnoses.PatientID = "
        strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
        strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.EvaluationID;"

        Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)
       
        if not recrd.eof then  'stops error if not records retruned
            recrd.MoveLast
        end if

    Idea???

    Dave
    0
     

    Author Comment

    by:jbotts
    Dave,
       Thanks for the response.  I deleted the Dim qryRD As QueryDef, and got the same message.  The dbsCurr was previously set: Set dbsCurr = CurrentDb and I did chage to Dim recrd As dao.recordset.  Still get the same message.
    Thanks,
    Jim
    0
     
    LVL 34

    Expert Comment

    by:flavo
    the error says you had decalred dbscurr already...

    remove these lines

    dim dbsCurr as dao.database
    set dbsCurr = currentdb

    from my code above..  
    0
     
    LVL 34

    Expert Comment

    by:flavo
    woops.. and this line

    Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)

    should be

    Set recrd = dbsCurr.OpenRecordset( strSQL, dbOpenDynaset)
    0
     
    LVL 44

    Expert Comment

    by:Arthur_Wood
    this code:

       Set qryRD = CurrentDb.CreateQueryDef("qryRD", strSQL)
        'Create qryRD Recordset
        Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)

    can be changed to:


        Set recrd = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    the problem with the original code was here:

      Set qryRD = CurrentDb.CreateQueryDef("qryRD", strSQL)
        'Create qryRD Recordset
        Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)
    which should read:

      Set qryRD = CurrentDb.CreateQueryDef("qryRD", strSQL)
        'Create qryRD Recordset
        Set recrd = CurrentDb.OpenRecordset(qryRD, dbOpenDynaset)  ' note the  missing "..." around qryRD

    AW


    0
     
    LVL 12

    Expert Comment

    by:pique_tech
    This line
        strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
    may also be causing a problem.  First, the "+" sign, when used as a string concatenator, will propagate NULLs.  So if Me.PatientID were null, you'd end up with a malformed SQL string (no closing parenthesis).  Second, if Me.PatientID is numeric, you'll never get a match because you're asking your recordset to find records where the PatientID is a string:  "1" <> 1

    FWIW.  Hope this helps.
    0
     

    Author Comment

    by:jbotts
    Dave and Arthur,
       I have made the suggested changes and still get the same error message.  What I don't understand (among many other things) is why I don't get the error message if I put the line recrd.MoveLast further down in the code... way past the code I have shown you.
    Thanks,
    Jim
    0
     

    Author Comment

    by:jbotts
    pique_tech,
         Thanks for the input on the + vs. &.  I made the change but still get the same error message.
    Thanks
    Jim
    0
     
    LVL 12

    Expert Comment

    by:pique_tech
    What do you get if you put a
         debug.print strSQL
    before
         Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)
    ?

    Do you get a "sensible" sql query that returns records if you copy/paste and run in query designer?
    0
     

    Author Comment

    by:jbotts
    I get:

    SELECT LastName, FirstName, tblRelevantDiagnoses.*  FROM tblPatientInformation, tblRelevantDiagnoses WHERE (tblRelevantDiagnoses.PatientID = tblPatientInformation.PatientID) AND (tblRelevantDiagnoses.PatientID = 'BottsJam')  ORDER BY tblRelevantDiagnoses.EvaluationID;

    'BottsJam' is the correct PatientID
    0
     
    LVL 12

    Expert Comment

    by:pique_tech
    Bummer (in a sense).  I was hoping for something obviously odd.  But then you'd get a failure trying to open the recordset, not navigate it.
    0
     
    LVL 34

    Expert Comment

    by:flavo
    maybe its taking too long to get the resutls???? may need a doevents in there (scraping the bottom of the barrel here)

     Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)
     doevents  'add this line
    0
     

    Author Comment

    by:jbotts
    DoEvents did not work, returned "Item not in this collection"
    0
     
    LVL 12

    Expert Comment

    by:pique_tech
    My last suggestion for the evening:  try the line
        Set recrd = dbsCurr.OpenRecordset("qryRD", dbOpenDynaset)
    without any of the optional arguments, like so:
        Set recrd = dbsCurr.OpenRecordset("qryRD")
    or
        Set recrd = dbsCurr.OpenRecordset(strSQL)

    My fading brain and some quick research suggest that the Set operation may misbehave if any of the arguments are inconsistent, and I'm not certain your strSQL provides an updateable recordset, which is what dbOpenDynaset would expect.
    0
     

    Author Comment

    by:jbotts
    pique tech,
        Thanks again for the response. I still get the same message.  I am writing a database which generates the report in MS Word.  Prior to the code I have shown you there is an access form with checkboxes.  If the first checkbox , cbALS is checked the code works fine and the report generates informaton about ALS.  If the checkbox is not checked, the message occurs.

    The code is:

    If recrd.EOF = False Then
         recrd.MoveLast
    End IF

    If recrd("ALS").value = True Then
        ....code to write to the Word document.
    End If

         It is late and my brain is getting mushie.
    Thanks,
    Jim

    Thanks,
    Jim
    0
     
    LVL 9

    Expert Comment

    by:Bat17
    I would create a fresh sub with just the relevant bits for the SQL so that you can test it seperatly. This will at least help you pin down where the error is comming from.

    The line "Set qryRD = CurrentDb.CreateQueryDef("qryRD", strSQL)" would have been creating a phsical query in the DB when it ran so I am surprised that you were not getting an error there when it ran again!

    HTh

    Peter
       

    Sub TestSQL()
    Dim dbsCurr As dao.Database
    Dim strSQL As String
    Dim recrd As dao.Recordset

    Set dbsCurr = CurrentDb()

    'Construct SQL statement for qryRD (Relevant Diagnoses)
        strSQL = "SELECT LastName, FirstName, tblRelevantDiagnoses.* " & _
               " FROM tblPatientInformation, tblRelevantDiagnoses" & _
               " WHERE (tblRelevantDiagnoses.PatientID = tblPatientInformation.PatientID) AND (tblRelevantDiagnoses.PatientID = "
        strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
        strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.EvaluationID;"

    Set recrd = dbsCurr.OpenRecordset(strSQL)

    If recrd.EOF Then ' no records found
       Debug.Print "No records Found"
    Else
        recrd.MoveLast
       Debug.Print recrd.RecordCount
    End If

    'clean up
    Set recrd = Nothing
    Set dbsCurr = Nothing
    End Sub
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: From Zero to Hero with Nodejs & MongoDB

    Interested in Node.js, but don't know where to start or how to learn it properly? Confused about how the MEAN stack pieces of MongoDB, Expressjs, Angularjs, and Nodejs fit together? Or how it's even possible to run JavaScript outside of the browser?

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    884 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

    19 Experts available now in Live!

    Get 1:1 Help Now