Looping Code includes duplicates

[tizwaz]

Can anyone help. I have the following code which loops through a recordset and emails a report covering the departments to their budget holder. Some budget holders have more than one department. The details of the departments are in a table. I have the following on the click event of a command button. it works fine in that it emails each budget holder the relevant report but as it loops through the list it emails the same report more than once to the same person as they appear in the list of dept and budget holders more than once. how can I exclude the duplicates?

Private Sub Command3_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryCostCentreMultiple", dbOpenDynaset)
rs.MoveFirst
Do
vOfficeID = rs("email")
DoCmd.SendObject acSendReport, "rptAutoEmailMultiple", "pdfFormat(*.pdf)", rs("Email"), "", "", "Monthly Report", "Please find attached a breakdown of the current details for your Cost Centres., True
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub

 

[PHV]

What is the SQL code of qryCostCentreMultiple ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tizwaz]

SELECT TblVodafoneList.[Mobile Number], TblVodafoneList.[Subscriber Name], TblStockLocation.StockLocation, TblVodafoneList.[Next Upgrade Date], TblVodafoneList.[SIM Number], TblVodafoneList.[Subscriber status], TblPricePlan.Device, TblStockLocation.StockLocation, TblVodafoneList.[Price Plan], TblCostCentre.[Cost Centre], TblCostCentre.Dept, qryCostCentreMultiple.Email
FROM (((TblVodafoneList LEFT JOIN TblStockLocation ON TblVodafoneList.Stock = TblStockLocation.StockLocationCode) INNER JOIN TblPricePlan ON TblVodafoneList.[Price Plan] = TblPricePlan.PricePlan) INNER JOIN qryCostCentreMultiple ON TblVodafoneList.[Cost Code 1] = qryCostCentreMultiple.[Cost Centre]) INNER JOIN TblCostCentre ON (qryCostCentreMultiple.[Cost Centre] = TblCostCentre.[Cost Centre]) AND (TblVodafoneList.[Cost Code 1] = TblCostCentre.[Cost Centre])
WHERE (((qryCostCentreMultiple.Email)=OfficeID()))
ORDER BY TblVodafoneList.[Subscriber Name];

 

[hdingman]

Would "SELECT DISTINCT" do what you need?