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

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
jbottsAsked:
Who is Participating?
 
flavoCommented:
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
 
jbottsAuthor Commented:
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
 
flavoCommented:
the error says you had decalred dbscurr already...

remove these lines

dim dbsCurr as dao.database
set dbsCurr = currentdb

from my code above..  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
flavoCommented:
woops.. and this line

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

should be

Set recrd = dbsCurr.OpenRecordset( strSQL, dbOpenDynaset)
0
 
Arthur_WoodCommented:
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
 
pique_techCommented:
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
 
jbottsAuthor Commented:
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
 
jbottsAuthor Commented:
pique_tech,
     Thanks for the input on the + vs. &.  I made the change but still get the same error message.
Thanks
Jim
0
 
pique_techCommented:
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
 
jbottsAuthor Commented:
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
 
pique_techCommented:
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
 
flavoCommented:
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
 
jbottsAuthor Commented:
DoEvents did not work, returned "Item not in this collection"
0
 
pique_techCommented:
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
 
jbottsAuthor Commented:
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
 
Bat17Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.