Looping Nested RST Error.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to send out a lot (200 or more) of emails via CDO. In RST1 I am
getting the name and some totals. Counting the number of Classes attended
per quarter. In RST2 I am getting details, like the class and the date of
the class for all classes attended by that person.

So I get RST1 and then base RST2 on the LifeStep ID in RST1. Then I loop
through RST2 to get all the details. Next Record in RST1 and all its details
and so on. I found that the querydefs query that I am using gets messed up.
I up in a copy object through each loop to try and fix it.

Well... After about 20 iterations in the RST1 loop, I get an error that:

Syntax error in the FROM clause.

I run the queries manually and they work fine. The code I am using follows.
Any suggestions/help will be very much appreciated

tia

Vanya

*********************Code Start**************************
Dim CDOConf As Object
Dim CDOFlds As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Dim rst2x As DAO.Recordset
Dim qdf2 As DAO.QueryDef
Dim strLP As String, strLPD As String, strLPpv As String
Dim strLPDate As String, strEmpSpo As String, strDetails As String
Dim BaseSQL2 As String, strSQL2 As String
Dim strBody, strFrom, strEmail, strEmpID, strTo As String
Dim strBodyStart, strBodyEnd, strSubject As String
Dim sqtot1, sqtot2, sqtot3, sqtot4 As String
Dim strBodyLPDetail As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTotalsEmailNNdel"
DoCmd.OpenQuery "qryTotalsEmailNNapp"
DoCmd.OpenQuery "qryTotalsEmailNNDetdel"
DoCmd.OpenQuery "qryTotalsEmailNNDetapp"
DoCmd.SetWarnings True
Set CDOConf = CreateObject("CDO.Configuration")
Set CDOFlds = CDOConf.Fields
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTotalsEmailNN")
Set qdf = dbs.QueryDefs("qryTotalsEmailNN")
BaseSQL = qdf.SQL
Set rst2x = dbs.OpenRecordset("SELECT * from tblTotalsEmailNNDet Where
LIFESTEPID =" & rst!LifeStepID)
'DoCmd.SetWarnings False
'DoCmd.CopyObject , "qryTotalsEmailNN", acQuery, "qryTotalsEmailNN_Z"
'DoCmd.SetWarnings True
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
BaseSQL2 = qdf2.SQL
strFrom = "BOB"
strSubject = "BOB is BOB"
strBodyEnd = vbCrLf & _
"Thank you for participating in Life...
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL) - 1)
qdf.SQL = strSQL
Set rst2x = Nothing
Set qdf2 = Nothing
DoCmd.SetWarnings False
DoCmd.CopyObject , "qryTotalsEmailNNDet", acQuery,
"qryTotalsEmailNNDet_Z"
DoCmd.SetWarnings True
Set rst2x = dbs.OpenRecordset("SELECT * from tblTotalsEmailNNDet
Where LIFESTEPID =" & rst!LifeStepID)
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
qdf2.SQL = strSQL2
strBodyLPDetail = ""
BaseSQL2 = qdf2.SQL
strEmail = !HWorkEmail
strTo = !FirstName
sqtot1 = IIf(IsNull(rst!qtot1), " ", rst!qtot1)
sqtot1 = String(2 - Len(sqtot1), " ") & sqtot1
strTo = StrConv([strTo], vbProperCase)
' strLPpv = String(2 - Len(strLPpv), " ") & strLPpv
strBodyStart = "" & strTo & ", " & vbCrLf & vbCrLf & _
....
With rst2x
Do Until rst2x.EOF
' strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
' qdf2.SQL = strSQL2
....
strBodyLPDetail = strBodyLPDetail & strLP & " " &
strLPD & " " & strLPpv & " " & strLPDate & " " & strEmpSpo & vbCrLf
.MoveNext
Loop
End With

strBody = strBodyStart & strBodyLPDetail & strBodyEnd
Call CDOMassMail(strFrom, strBody, strEmail, strSubject)
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing
qdf2.SQL = BaseSQL2
Set qdf2 = Nothing
'Set rst2 = Nothing
Set rst2x = Nothing
'Set dbs2 = Nothing
Set CDOConf = Nothing
Set CDOFlds = Nothing

************************Code End*******************
 
Might LifeStepID be Null?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ivan Grozney said:
I am trying to send out a lot (200 or more) of emails via CDO. In RST1 I
am
getting the name and some totals. Counting the number of Classes attended
per quarter. In RST2 I am getting details, like the class and the date of
the class for all classes attended by that person.

So I get RST1 and then base RST2 on the LifeStep ID in RST1. Then I loop
through RST2 to get all the details. Next Record in RST1 and all its
details
and so on. I found that the querydefs query that I am using gets messed
up.
I up in a copy object through each loop to try and fix it.

Well... After about 20 iterations in the RST1 loop, I get an error that:

Syntax error in the FROM clause.

I run the queries manually and they work fine. The code I am using
follows.
Any suggestions/help will be very much appreciated

tia

Vanya

*********************Code Start**************************
Dim CDOConf As Object
Dim CDOFlds As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Dim rst2x As DAO.Recordset
Dim qdf2 As DAO.QueryDef
Dim strLP As String, strLPD As String, strLPpv As String
Dim strLPDate As String, strEmpSpo As String, strDetails As String
Dim BaseSQL2 As String, strSQL2 As String
Dim strBody, strFrom, strEmail, strEmpID, strTo As String
Dim strBodyStart, strBodyEnd, strSubject As String
Dim sqtot1, sqtot2, sqtot3, sqtot4 As String
Dim strBodyLPDetail As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTotalsEmailNNdel"
DoCmd.OpenQuery "qryTotalsEmailNNapp"
DoCmd.OpenQuery "qryTotalsEmailNNDetdel"
DoCmd.OpenQuery "qryTotalsEmailNNDetapp"
DoCmd.SetWarnings True
Set CDOConf = CreateObject("CDO.Configuration")
Set CDOFlds = CDOConf.Fields
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTotalsEmailNN")
Set qdf = dbs.QueryDefs("qryTotalsEmailNN")
BaseSQL = qdf.SQL
Set rst2x = dbs.OpenRecordset("SELECT * from tblTotalsEmailNNDet Where
LIFESTEPID =" & rst!LifeStepID)
'DoCmd.SetWarnings False
'DoCmd.CopyObject , "qryTotalsEmailNN", acQuery, "qryTotalsEmailNN_Z"
'DoCmd.SetWarnings True
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
BaseSQL2 = qdf2.SQL
strFrom = "BOB"
strSubject = "BOB is BOB"
strBodyEnd = vbCrLf & _
"Thank you for participating in Life...
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL) - 1)
qdf.SQL = strSQL
Set rst2x = Nothing
Set qdf2 = Nothing
DoCmd.SetWarnings False
DoCmd.CopyObject , "qryTotalsEmailNNDet", acQuery,
"qryTotalsEmailNNDet_Z"
DoCmd.SetWarnings True
Set rst2x = dbs.OpenRecordset("SELECT * from
tblTotalsEmailNNDet
Where LIFESTEPID =" & rst!LifeStepID)
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
qdf2.SQL = strSQL2
strBodyLPDetail = ""
BaseSQL2 = qdf2.SQL
strEmail = !HWorkEmail
strTo = !FirstName
sqtot1 = IIf(IsNull(rst!qtot1), " ", rst!qtot1)
sqtot1 = String(2 - Len(sqtot1), " ") & sqtot1
strTo = StrConv([strTo], vbProperCase)
' strLPpv = String(2 - Len(strLPpv), " ") & strLPpv
strBodyStart = "" & strTo & ", " & vbCrLf & vbCrLf & _
...
With rst2x
Do Until rst2x.EOF
' strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
' qdf2.SQL = strSQL2
...
strBodyLPDetail = strBodyLPDetail & strLP & " " &
strLPD & " " & strLPpv & " " & strLPDate & " " & strEmpSpo & vbCrLf
.MoveNext
Loop
End With

strBody = strBodyStart & strBodyLPDetail & strBodyEnd
Call CDOMassMail(strFrom, strBody, strEmail, strSubject)
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing
qdf2.SQL = BaseSQL2
Set qdf2 = Nothing
'Set rst2 = Nothing
Set rst2x = Nothing
'Set dbs2 = Nothing
Set CDOConf = Nothing
Set CDOFlds = Nothing

************************Code End*******************
Click to expand...
 
Douglas,

It is an autonumber field. What I did notice is that the query in the
second RST would have and extra item in the WHERE clause something like:

LifeStepsID = "P"

Since I am writing things to the strBody structure I cannot figure out
how that is happening.

The 1st RST was to get the unique name and the 2nd was to get the
detail. So I just got all the info in to one query, sorted on the LifeStepID
and did some IF logic to get the same thing without the using the 2nd
RecordSet. That seems to have solved it except that I have 3 records that
have blank for the email address and they still show up even though I say NOT
NULL or <> "" or <> " ". Still need to get that part figured out...

To me this is an interesting problem that I would like to figure out but
alas, it will have to wait for another day...

Thanks,
Vanya


Douglas J. Steele said:
Might LifeStepID be Null?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ivan Grozney said:
I am trying to send out a lot (200 or more) of emails via CDO. In RST1 I
am
getting the name and some totals. Counting the number of Classes attended
per quarter. In RST2 I am getting details, like the class and the date of
the class for all classes attended by that person.

So I get RST1 and then base RST2 on the LifeStep ID in RST1. Then I loop
through RST2 to get all the details. Next Record in RST1 and all its
details
and so on. I found that the querydefs query that I am using gets messed
up.
I up in a copy object through each loop to try and fix it.

Well... After about 20 iterations in the RST1 loop, I get an error that:

Syntax error in the FROM clause.

I run the queries manually and they work fine. The code I am using
follows.
Any suggestions/help will be very much appreciated

tia

Vanya

*********************Code Start**************************
Dim CDOConf As Object
Dim CDOFlds As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Dim rst2x As DAO.Recordset
Dim qdf2 As DAO.QueryDef
Dim strLP As String, strLPD As String, strLPpv As String
Dim strLPDate As String, strEmpSpo As String, strDetails As String
Dim BaseSQL2 As String, strSQL2 As String
Dim strBody, strFrom, strEmail, strEmpID, strTo As String
Dim strBodyStart, strBodyEnd, strSubject As String
Dim sqtot1, sqtot2, sqtot3, sqtot4 As String
Dim strBodyLPDetail As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTotalsEmailNNdel"
DoCmd.OpenQuery "qryTotalsEmailNNapp"
DoCmd.OpenQuery "qryTotalsEmailNNDetdel"
DoCmd.OpenQuery "qryTotalsEmailNNDetapp"
DoCmd.SetWarnings True
Set CDOConf = CreateObject("CDO.Configuration")
Set CDOFlds = CDOConf.Fields
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTotalsEmailNN")
Set qdf = dbs.QueryDefs("qryTotalsEmailNN")
BaseSQL = qdf.SQL
Set rst2x = dbs.OpenRecordset("SELECT * from tblTotalsEmailNNDet Where
LIFESTEPID =" & rst!LifeStepID)
'DoCmd.SetWarnings False
'DoCmd.CopyObject , "qryTotalsEmailNN", acQuery, "qryTotalsEmailNN_Z"
'DoCmd.SetWarnings True
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
BaseSQL2 = qdf2.SQL
strFrom = "BOB"
strSubject = "BOB is BOB"
strBodyEnd = vbCrLf & _
"Thank you for participating in Life...
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL) - 1)
qdf.SQL = strSQL
Set rst2x = Nothing
Set qdf2 = Nothing
DoCmd.SetWarnings False
DoCmd.CopyObject , "qryTotalsEmailNNDet", acQuery,
"qryTotalsEmailNNDet_Z"
DoCmd.SetWarnings True
Set rst2x = dbs.OpenRecordset("SELECT * from
tblTotalsEmailNNDet
Where LIFESTEPID =" & rst!LifeStepID)
Set qdf2 = dbs.QueryDefs("qryTotalsEmailNNDet")
strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
qdf2.SQL = strSQL2
strBodyLPDetail = ""
BaseSQL2 = qdf2.SQL
strEmail = !HWorkEmail
strTo = !FirstName
sqtot1 = IIf(IsNull(rst!qtot1), " ", rst!qtot1)
sqtot1 = String(2 - Len(sqtot1), " ") & sqtot1
strTo = StrConv([strTo], vbProperCase)
' strLPpv = String(2 - Len(strLPpv), " ") & strLPpv
strBodyStart = "" & strTo & ", " & vbCrLf & vbCrLf & _
...
With rst2x
Do Until rst2x.EOF
' strSQL2 = Left(BaseSQL2, Len(BaseSQL2) - 1)
' qdf2.SQL = strSQL2
...
strBodyLPDetail = strBodyLPDetail & strLP & " " &
strLPD & " " & strLPpv & " " & strLPDate & " " & strEmpSpo & vbCrLf
.MoveNext
Loop
End With

strBody = strBodyStart & strBodyLPDetail & strBodyEnd
Call CDOMassMail(strFrom, strBody, strEmail, strSubject)
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing
qdf2.SQL = BaseSQL2
Set qdf2 = Nothing
'Set rst2 = Nothing
Set rst2x = Nothing
'Set dbs2 = Nothing
Set CDOConf = Nothing
Set CDOFlds = Nothing

************************Code End*******************
Click to expand...
Click to expand...
 
Back
Top