Group Query results in VBA

[Daniel]

Hello,

This is a Query/VBA question.

I have a query which is returning a listing of people past due by month.
Thus, an individual's name may appear more than once if they are past due for
more than one month. I am using vba to perform this query and then wish to
send an e-mail automatically to each individual.

---
strSQL = ...
Set rst = db.OpenRecordset(strSQL)

lngCount = 0
If rst.RecordCount > 0 Then 'ensure there are members defined
'Build Email Recipient Listing to send the email to
rst.MoveFirst
Do While Not rst.EOF
With rst
'Send email
.MoveNext
End With
Loop

'CleanUp
rst.Close
Set rst = Nothing
---

My current method will send an email for each month returned instead of
grouping them together. How can I modify my code to only send one email
specifying which months are owed?

Thank you,

Daniel P

 

[Daniel]

Here is my original SQL, In case my problem lies there.

SELECT tbl_Members.MemLastName, tbl_Members.MemFirstName,
tbl_Members.MemEmail, tbl_Dues.DuesYear,
DLookUp("[MonthName]","tbl_Months","[MonthNo]=" & [DuesMonth]) AS [Month]
FROM tbl_Members LEFT JOIN tbl_Dues ON tbl_Members.MemId = tbl_Dues.MemId
WHERE (((tbl_Dues.DuesPaid)=False))
ORDER BY tbl_Members.MemLastName, tbl_Members.MemFirstName,
tbl_Dues.DuesYear, DLookUp("[MonthName]","tbl_Months","[MonthNo]=" &
[DuesMonth]);


Daniel P

 

[pietlinden]

Hello,

This is a Query/VBA question.

I have a query which is returning a listing of people past due by month.
Thus, an individual's name may appear more than once if they are past due for
more than one month. I am using vba to perform this query and then wish to
send an e-mail automatically to each individual.

---
strSQL = ...
Set rst = db.OpenRecordset(strSQL)

lngCount = 0
If rst.RecordCount > 0 Then 'ensure there are members defined
'Build Email Recipient Listing to send the email to
rst.MoveFirst
Do While Not rst.EOF
With rst
'Send email
.MoveNext
End With
Loop

'CleanUp
rst.Close
Set rst = Nothing
---

My current method will send an email for each month returned instead of
grouping them together. How can I modify my code to only send one email
specifying which months are owed?

Thank you,

Daniel P

Use fConcatChild from www.mvps.org/access and then use the e-mail
address and the concatenated result in a single e-mail. It basically
flattens a 1-M relationship to a single record.

 

[Daniel]

Thank you! I wasn't aware of that function. Another one to remember.

Daniel P