Hi all

I'm tearing my hair out on this one, and I know its simple.....but....
I have 2 queries:

qryContact = tblContact.contactname & tblContactEmail
qryExpiringPermit = tblFleet.Fleet_Num, tblPermits.DateFrom, tblPermits.DateTo, tblPermits.PermitNumber, tblPermitTypes.PermitType

Here's a module I want to use that sends an email based on qryExpiringpermit to each record in qryContact....here's the module:

Function EMail()
On Error GoTo Mailerr

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strCount
Set db = CurrentDb
Set rst = db.OpenRecordset("qryContacts")
rst.MoveFirst
Do Until rst.EOF
strTo = rst.Fields("Email")
strSubject = "The following Permits are expiring within the next 30 days"
ContactNAme = rst.Fields("ContactNAme")
strCount = 0
Set rst = Nothing
Set rst = db.OpenRecordset("qryExpiringPermits")
While Not rst.EOF And Not rst.BOF
strCount = strCount + 1
rst.MoveNext
Wend
If strCount = "" Then
strBody = ContactNAme & vbNewLine
strBody = strBody & "There are no permits expiring in the next 30 days!"
Else
strBody = "Good morning " & ContactNAme & vbNewLine
strBody = strBody & "The following permits are expyring within the next 30 days:" & vbNewLine
rst.MoveFirst
Do Until rst.EOF = True
strBody = strBody & "Fleet #: " & rst.Fields("Fleet_Num") & vbNewLine
strBody = strBody & "Permit Type #: " & rst.Fields("PermitType") & vbNewLine
strBody = strBody & "Permit Duration: " & rst.Fields("DateFrom") & " To " & rst.Fields("DateTo")
strBody = strBody & "Permit Number: " & rst.Fields("PermitNumber")
rst.MoveNext
Loop
End If
DoCmd.SendObject , , acFormatHTML, strTo, , , strSubject, strBody, False

Set rst = Nothing
Set rst = db.OpenRecordset("qryContacts")
rst.MoveNext
Loop




Set rst = Nothing
Set rst2 = Nothing

Set db = Nothing

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

End Function

any ideas?

It would help if you told us what the problem and error message is. Based on quick look at the code this part looks suspect:


Set rst = db.OpenRecordset("qryContacts")
rst.MoveNext


Try changing that to rst.movefirst

Hi Chergh

I know its probably not right...but changing it to Movefirst still doesnt work. All it does is pops up saying "No Current Record."
I think I'm confused as to how to handle 2 different recordsets in a query

It may be that your query is not returning any records. Have you checked this if you run the query in freestanding mode? I have had that error message in those circumstances

yeah.....Mayb I'm not explaining properly?
I think I'll post another thread and start on another note.....

thanks for your help guys