jbotts
asked on
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.Pati
strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.Evalu
'Create qryRD
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
recrd.MoveLast
'Hospice Related Diagnoses - Header
With objWD.Selection
.Font.Bold = True
.Font.Size = 12
.TypeText vbLf + vbLf + vbLf
.ParagraphFormat.Alignment
.TypeText "Hospice Related Diagnoses" + vbLf + vbLf
End With
Thanks,
Jim Botts
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.Pati
strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.Evalu
'Create qryRD
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
recrd.MoveLast
'Hospice Related Diagnoses - Header
With objWD.Selection
.Font.Bold = True
.Font.Size = 12
.TypeText vbLf + vbLf + vbLf
.ParagraphFormat.Alignment
.TypeText "Hospice Related Diagnoses" + vbLf + vbLf
End With
Thanks,
Jim Botts
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the error says you had decalred dbscurr already...
remove these lines
dim dbsCurr as dao.database
set dbsCurr = currentdb
from my code above..
remove these lines
dim dbsCurr as dao.database
set dbsCurr = currentdb
from my code above..
woops.. and this line
Set recrd = dbsCurr.OpenRecordset("qry
should be
Set recrd = dbsCurr.OpenRecordset( strSQL, dbOpenDynaset)
Set recrd = dbsCurr.OpenRecordset("qry
should be
Set recrd = dbsCurr.OpenRecordset( strSQL, dbOpenDynaset)
this code:
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
can be changed to:
Set recrd = CurrentDb.OpenRecordset(st
the problem with the original code was here:
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
which should read:
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = CurrentDb.OpenRecordset(qr
AW
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
can be changed to:
Set recrd = CurrentDb.OpenRecordset(st
the problem with the original code was here:
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = dbsCurr.OpenRecordset("qry
which should read:
Set qryRD = CurrentDb.CreateQueryDef("
'Create qryRD Recordset
Set recrd = CurrentDb.OpenRecordset(qr
AW
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.
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.
ASKER
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
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
ASKER
pique_tech,
Thanks for the input on the + vs. &. I made the change but still get the same error message.
Thanks
Jim
Thanks for the input on the + vs. &. I made the change but still get the same error message.
Thanks
Jim
What do you get if you put a
debug.print strSQL
before
Set recrd = dbsCurr.OpenRecordset("qry
?
Do you get a "sensible" sql query that returns records if you copy/paste and run in query designer?
debug.print strSQL
before
Set recrd = dbsCurr.OpenRecordset("qry
?
Do you get a "sensible" sql query that returns records if you copy/paste and run in query designer?
ASKER
I get:
SELECT LastName, FirstName, tblRelevantDiagnoses.* FROM tblPatientInformation, tblRelevantDiagnoses WHERE (tblRelevantDiagnoses.Pati
'BottsJam' is the correct PatientID
SELECT LastName, FirstName, tblRelevantDiagnoses.* FROM tblPatientInformation, tblRelevantDiagnoses WHERE (tblRelevantDiagnoses.Pati
'BottsJam' is the correct PatientID
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.
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("qry
doevents 'add this line
Set recrd = dbsCurr.OpenRecordset("qry
doevents 'add this line
ASKER
DoEvents did not work, returned "Item not in this collection"
My last suggestion for the evening: try the line
Set recrd = dbsCurr.OpenRecordset("qry
without any of the optional arguments, like so:
Set recrd = dbsCurr.OpenRecordset("qry
or
Set recrd = dbsCurr.OpenRecordset(strS
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.
Set recrd = dbsCurr.OpenRecordset("qry
without any of the optional arguments, like so:
Set recrd = dbsCurr.OpenRecordset("qry
or
Set recrd = dbsCurr.OpenRecordset(strS
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.
ASKER
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
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
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("
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.Pati
strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.Evalu
Set recrd = dbsCurr.OpenRecordset(strS
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
The line "Set qryRD = CurrentDb.CreateQueryDef("
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.Pati
strSQL = strSQL + "'" + Me.PatientID + "'" + ") "
strSQL = strSQL + " ORDER BY tblRelevantDiagnoses.Evalu
Set recrd = dbsCurr.OpenRecordset(strS
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
ASKER
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