Looping through recordsets

[beckyr51]

Is there a way to do something to only part of a recordset. I want to send a mail only to a certain number of people and then go back to a form. Ive tried the following but if doesnt work:

Set rs = db.OpenRecordset("Select * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';")
rs.MoveLast
total = rs.RecordCount
half = total / num


Do While rs.RecordCount >= half
If Not IsNull(rs("STU_EMAIL")) Then
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
addresses = rs("STU_EMAIL")
With objOutlookMsg
.Recipients.add (addresses)
.subject = "Change of Tutor"
.Body = strbod
.Send
End With
End If

rs.MoveNext
Loop

Any ideas?

 

[SkipVought]

Hi,

nothing is changing in your WHILE...

Do While rs.RecordCount >= half
    If Not IsNull(rs("STU_EMAIL")) Then
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    addresses = rs("STU_EMAIL")
    With objOutlookMsg
        .Recipients.add (addresses)
        .subject = "Change of Tutor"
        .Body = strbod
        .Send
    End With
    End If
    
    rs.MoveNext[b]
    half = half + 1[/b]
Loop

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Remou]

I think you want:

[tt]Set rs = db.OpenRecordset("Select * from [tblStudents] where tblStudents.[STU_TU_CODE] = 'retiring'")[/tt]

This assumes that STU_TU_CODE contains the word 'retiring', rather than a code.

 

[PWise]

try:

Set rs = db.OpenRecordset("Select [COLOR=red]TOP 50 PERCENT[/color] * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';")
[COLOR=green] 'rs.MoveLast
'total = rs.RecordCount
'half = total / num

'Do While rs.RecordCount >= half
[/color]
[COLOR=red]Do While not rs.eof[/color]
    If Not IsNull(rs("STU_EMAIL")) Then
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    addresses = rs("STU_EMAIL")
    With objOutlookMsg
        .Recipients.add (addresses)
        .subject = "Change of Tutor"
        .Body = strbod
        .Send
    End With
    End If
    
    rs.MoveNext
Loop

 

[beckyr51]

'retiring' is a code entered by use of an input box.

I see now that i wasnt changing anything in my while loop, i adjusted it but im getting an error: no current record and its highlighting addresses = rs("STU_EMAIL"). So for example i have 4 students with the same STU_TU_CODE, i want to send an email to two of them first but its sending the email to one and then im getting this error??

 

[PWise]

Set rs = db.OpenRecordset("Select * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';")
rs.MoveLast
'you are now at the last record
total = rs.RecordCount
half = total / num


Do While rs.RecordCount >= half
If Not IsNull(rs("STU_EMAIL")) Then
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
addresses = rs("STU_EMAIL")
With objOutlookMsg
.Recipients.add (addresses)
.subject = "Change of Tutor"
.Body = strbod
.Send
End With
End If

rs.MoveNext
' you moved past the last record so after on email you are getting an error

Loop

 

[beckyr51]

Thanks pwise, your first suggestion works great but is there a way to also implement this into my append query below; change only those students that i emailed?

strsql = "Update [tblStudents] set tblStudents.[STU_TU_CODE] = " & "'" & replacecode & "' where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';"
db.Execute (strsql)

 

[PWise]

if tblStudents has a primary key
try
"Update [tblStudents]
INNER JOIN [Select TOP 50 PERCENT * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';"]. AS tblStudents1 ON tblStudents.pk= tblStudents1.pk

set tblStudents.[STU_TU_CODE] = " & "'" & replacecode & "'"

pk=primary key field name

 

[beckyr51]

Yes the primary key for the student table is STU_ID; ive tried implementing the above but i get a syntax error which i have been trying to figure out to no avail

 

[Remou]

i would be inclined to update the field as you step through the email:

Do While rs.RecordCount >= half
    If Not IsNull(rs("STU_EMAIL")) Then
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    addresses = rs("STU_EMAIL")
    With objOutlookMsg
        .Recipients.add (addresses)
        .subject = "Change of Tutor"
        .Body = strbod
        .Send
    End With
    End If
  [red]  
    rs.Edit
    rs!STU_TU_CODE = replacecode & ""
    rs.Update[/red]

    rs.MoveNext
     ' you moved past the last record so after on email you are getting an error

Loop

 

[PWise]

try

"Update [tblStudents]
INNER JOIN [Select TOP 50 PERCENT * from [tblStudents] where tblStudents.[STU_TU_CODE] = '" & retiring & "']. AS tblStudents1 ON tblStudents.pk= tblStudents1.pk

set tblStudents.[STU_TU_CODE] = '" & replacecode & "'

 

[beckyr51]

Thanks for all the help that works now as well. Im now trying to send an email to the bottom half and i understand that counter to the intuitive BOTTOM 50 PERCENT its still TOP 50 PERCENT but ordered desc, where do i put this order by bit - ive tried:

Set rs = db.OpenRecordset("Select BOTTOM 50 PERCENT * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';" order by tblStudent.[STU_ID] desc)

but get a syntax error

 

[beckyr51]

sorry its meant to be TOP 50 PERCENT in the above statement

 

[JoeAtWork]

I'll give you a hint. You just updated a field for all the students you emailed. Would this not suggest you have a way of selecting all records that have not been emailed yet?