Re: Double RS

[Seb]

Re: Double RS

Hi Guys

I need to find out how to have 2 recordsets

as an example:
I have a function that needs to get info from 1 table and send it via email to everyone in another table....

Would I have

rstContact = db.openrecordset("qryContacts") and another called rstPermits = db.openrecordset("qryExpiringPermits")

I've never had 2 recordsets before so not sure how to handle it.....am I going about it the wrong way?

I've done some searches but havent found anything yet

 

[Seb]

This is the code I've done so far. I know both queries (qryContacts & qryExpiringPermits) work ok - ie. they both produce results
But now when I run the code below it says "No current record"

Dim db As DAO.Database
Dim rstContact As DAO.Recordset
Dim rstPermit As DAO.Recordset
Dim strCount
    
    Set db = CurrentDb
    Set rstContact = db.OpenRecordset("qryContacts")
                     

    While Not rstContact.EOF And Not rstContact.BOF
        Set rstPermit = db.OpenRecordset("qryExpiringPermits")
        strTo = rstContact.Fields("Email")
        strSubject = "Monthly report on expiring permits"
        
        rstPermit.MoveFirst
        While rstPermit.EOF = False
        
            strbody = strbody & "Fleet #: " & rstPermit.Fields("Fleet_Num") & vbNewLine
            strbody = strbody & "Permit Type #: " & rstPermit.Fields("PermitType") & vbNewLine
            strbody = strbody & "Permit Duration: " & rstPermit.Fields("DateFrom") & " To " & rst.Fields("DateTo")
            strbody = strbody & "Permit Number: " & rstPermit.Fields("PermitNumber")
            MsgBox strbody
            
            DoCmd.SendObject , , acFormatHTML, strTo, , , strSubject, strbody, False
        rstPermit.MoveNext
     
        Wend
        
        rstContact.MoveNext
       
        
        Wend
        

        Set rstContact = Nothing
        Set rstPermit = Nothing


Set db = Nothing

Mailend:
Exit Function
Mailerr:
MsgBox Err.Description
Resume Mailend

End Function

 

[Seb]

This is the actual DB. The module is called mdlEmail2
please be gentle though as this is a work in progress and not very neat!!!