run time error 3265 help

[ifoundgoldbug]

greetings I am trying to run a pair of sql queries on form load (i know
I should move the second inside the IF statement i just wanna get it
working first) anyways here is the code that I have and it makes it
through the Do loop once but i get a 3265 error on the second loop
through.

<code>
Private Sub Form_Load()

Dim saveme As Boolean
Dim LastDate As Date
Dim today As Date
Dim rs As ADODB.Recordset
Dim rsquery As ADODB.Recordset
Dim X As Integer
Dim MsgBody As String


'gets todays date

today = Date


' creates a recordset to work from
Set rs = New ADODB.Recordset
Set rsquery = New ADODB.Recordset
' looks through the database and returns all e-mail dates that are
less than or equal to today
rs.Open "SELECT pmtable.EmailDate FROM pmtable WHERE
(((pmtable.EmailDate)<=Date()))", _
CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

rsquery.Open "SELECT pmtable.[Tool #]FROM pmtable WHERE
(((pmtable.pmDate) Between Date() And Date()+7))", _
CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic


' grabs the last day that an e-mail was sent and sets it to last
day
LastDate = rs.Fields.Item(0)

Do While Not rsquery.EOF

'Debug.Print rsquery.Fields.Item(X)
MsgBody = MsgBody + rsquery.Fields.Item(X)
X = X + 1
Loop
'compares todays date vs the last day an e-mail was sent
'it also looks to see to see if today is monday
'finally it looks to make sure that today is not equal to the last
day an e-mail was sent
'today - the last e-mail was more than a week AND today is not the
day an e-mail was sent. send an e-mail
'also send an e-mail if today is monday as long as today is not the
day the last e-mail was sent.

If (today - LastDate) > 6 Or Weekday(today) = vbMonday And Not
today = LastDate Then
'saves a copy in the sent folder in lotus notes
saveme = True
'sends an e-mail (subject, attachment, address, body, save
e-mail)
Call SendNotesMail("Preventative Maintence", "",
"(e-mail address removed)", MsgBody, saveme)
'sets the last day an e-mail was sent to today
rs.Fields.Item(0) = Date
End If

'the rest of the code is for tiding up the db afterwards

rs.UpdateBatch
rs.Close
rsquery.Close
Set rs = Nothing
Set rsquery = Nothing

end Sub

</Code>

 

[Douglas J Steele]

You only have one field in your recordset. Fields.Item(X) returns a specific
field from the recordset's Fields collection. As soon as X becomes greater
than 1, that's going to refer to a non-existant field, which is what's
causing your error 3265 ("Item not found in this collection.")

If what you're trying to do is report the value for that field on each row
in the recordset, use:

Do While Not rsquery.EOF
MsgBody = MsgBody + rsquery.Fields.Item(0)
rsquery.MoveNext
Loop


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

greetings I am trying to run a pair of sql queries on form load (i know
I should move the second inside the IF statement i just wanna get it
working first) anyways here is the code that I have and it makes it
through the Do loop once but i get a 3265 error on the second loop
through.

<code>
Private Sub Form_Load()

Dim saveme As Boolean
Dim LastDate As Date
Dim today As Date
Dim rs As ADODB.Recordset
Dim rsquery As ADODB.Recordset
Dim X As Integer
Dim MsgBody As String


'gets todays date

today = Date


' creates a recordset to work from
Set rs = New ADODB.Recordset
Set rsquery = New ADODB.Recordset
' looks through the database and returns all e-mail dates that are
less than or equal to today
rs.Open "SELECT pmtable.EmailDate FROM pmtable WHERE
(((pmtable.EmailDate)<=Date()))", _
CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

rsquery.Open "SELECT pmtable.[Tool #]FROM pmtable WHERE
(((pmtable.pmDate) Between Date() And Date()+7))", _
CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic


' grabs the last day that an e-mail was sent and sets it to last
day
LastDate = rs.Fields.Item(0)

Do While Not rsquery.EOF

'Debug.Print rsquery.Fields.Item(X)
MsgBody = MsgBody + rsquery.Fields.Item(X)
X = X + 1
Loop
'compares todays date vs the last day an e-mail was sent
'it also looks to see to see if today is monday
'finally it looks to make sure that today is not equal to the last
day an e-mail was sent
'today - the last e-mail was more than a week AND today is not the
day an e-mail was sent. send an e-mail
'also send an e-mail if today is monday as long as today is not the
day the last e-mail was sent.

If (today - LastDate) > 6 Or Weekday(today) = vbMonday And Not
today = LastDate Then
'saves a copy in the sent folder in lotus notes
saveme = True
'sends an e-mail (subject, attachment, address, body, save
e-mail)
Call SendNotesMail("Preventative Maintence", "",
"(e-mail address removed)", MsgBody, saveme)
'sets the last day an e-mail was sent to today
rs.Fields.Item(0) = Date
End If

'the rest of the code is for tiding up the db afterwards

rs.UpdateBatch
rs.Close
rsquery.Close
Set rs = Nothing
Set rsquery = Nothing

end Sub

</Code>