comparing records in a recordset

[jimday1982]

I'm trying to write an if statement that compares the next record in my recordset to the current record and if the email field is different, do blah, if it's the same, end the if statement. So, in pseduocode, it looks like this:

if rst1.current("") <> rst1.next("[email]") then
blah
else
end if

Thanks for any help!

 

[The_Doc_Man]

You need to look up "RecordsetClone" as a way to have two viewpoints in the same recordset.

You can write a loop. In the loop, you make the clone, advance the record in the original recordset, and then compare the current record in the clone to the current record in the original. They are, of course, different by one position. When the ORIGINAL is advanced and hits EOF, you don't do the comparison any more 'cause there is no further basis for it.

So you wouldn't use PROPERTIES of the recordsets. Instead, you would look at two "different" recordsets - the original (advanced by one step) and its clone (not yet advanced) - and do your comparions, field by field. If you are set on doing it in VBA, this is the easiest way I can think of to do it.

 

[jimday1982]

We'll I've looked everywhere and I cannot find out how to create a recordsetclone (of rst1)...I tried to do this without a recordsetclone, but the recordset is going one record too far (my e-mails are incorrect and are including one extra record) using the code below:

If Not rst1.EOF Then
Do While Not rst1.EOF

    emailto = rst1("[email]")
    strbody1 = strbody1 & rst1("[ponumber]") & "," & rst1("[odr_date]") & "," & rst1("[supplierid]") & "," & rst1("[name]") & "," & rst1("[fax]") & "," & rst1("[contact]") & vbCrLf
    
    If rst1("email") <> strPrevEmail And i <> 1 Then
    
        If Len(emailto) <> 0 And Left(emailto, 1) <> " " Then
            DoCmd.SendObject acSendNoObject, , , emailto, , , "Open POs from Dynadirect", "Open PO(s)" & vbCrLf & vbCrLf & strbody1, True
            
            Else
        End If
    'reset the data
    strbody1 = ""
    Else
    End If
    
    strPrevEmail = rst1("Email")
    rst1.MoveNext
    i = i + 1
Loop
End If

If anyone could offer any guidance, I'd really appreciate it.

 

[Jon K]

It seems you just want to send an email to the first person at each email address.

I think it would be easier if you use a Totals Query to build an email list and send the emails by opening the list as a recordset. See query in attached database.
.

 

[jimday1982]

Thanks, but the problem is that each e-mail that goes out, should contain ALL the records associated with that e-mail address, not just the first one...any ideas on how to do that?