Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-11-01
16
Medium Priority
?
410 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
Comment
Question by:jbotts
[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
  • 4
  • +2
16 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 500 total points
ID: 12469755
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
ID: 12469877
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
ID: 12469882
the error says you had decalred dbscurr already...

remove these lines

dim dbsCurr as dao.database
set dbsCurr = currentdb

from my code above..  
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 34

Expert Comment

by:flavo
ID: 12469884
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
ID: 12469898
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
ID: 12469915
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
ID: 12469993
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
ID: 12470030
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
ID: 12470053
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
ID: 12470079
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
ID: 12470104
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
ID: 12470119
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
ID: 12470170
DoEvents did not work, returned "Item not in this collection"
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12470235
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
ID: 12470381
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
ID: 12472214
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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