Creating a string by looping through records

[JenniferM]

I sort of know what I need to do, I just can't seem to find any references online that are giving me exactly what I need... Or maybe I'm just not understanding it (which is probably likely).

So here's what I've got:
I have 3 separate tables (TblContactInfo and TblMailingList which hold contact information, and TblEntries with primary key ID that holds the data for each report I want to create and send). TblContactInfo holds names and corresponding email addresses (Primary key - EmailID), and TblMailingList stores which contacts are paired with which report (Primary keys - EmailID and ID).

I need to be able to loop through TblMailingList and be able to add each EmailID to a string that has a given value for the ID field.

Right now, this is what I've put together. I've somehow managed to never need to use a loop before, so the syntax is not familiar to me. LbxReportList is how the value for ID is selected.

Dim strEmailList As String
Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT * FROM TblMailingList "WHERE [ID] = " & Me.LbxReportList)
 
Do Until rst.EOF
strEmailList = strEmailList & rst.Fields(EmailID) & "; "
rst.MoveNext
 
Loop
 
rst.Close
Set rst = Nothing

I'd appreciate any help from anyone who can shed some light on this for me! Thanks!

[Mihail]

If I understand well your question I think you can solve that more elegant (and easier) by using a sub-report in your main report.

[JenniferM]

The main reason I was looking at creating a string here is that I want to be able to create a list of email recipients to send via Outlook. I've already got code that successfully exports the report to PDF form, I just want to be able to send that report to the intended recipients in an automated fashion.

Right now, I have this to send the email:

Set miMail = appOL.CreateItem(olMailItem)
With miMail
.To = strEmailList
.Subject = "NICU Patient Safety Team: End of Cycle Progress Report"
.Body = ""
.ReadReceiptRequested = False
Set oMyAtt = miMail.Attachments.Add("C:\AccessReports\" & strReportTitle & ".pdf")
.Save
End With
GetOut:
Set miMail = Nothing

Unfortunately though, it's not getting that far. Right now, it's hanging up on this line in the original post, and giving me an "Item not found in this collection" error:

strEmailList = strEmailList & rst.Fields(EmailID) & "; "

Any ideas for this? I totally get your point though... If this was a report, using a subreport to handle this would be 100 times easier!

[NeoPa]

You need to use :

strEmailList = strEmailList & rst.Fields("EmailID") & "; "

The existing code is looking for a field which has a name of the value of a variable called EmailID. I suspect you have no such variable.

Please read Before Posting (VBA or SQL) Code for some handy tips that will help you to avoid ever getting into such a situation again. Option Explicit in all your modules is the main point here, but others too, can prove helpful.

[JenniferM]

And as per usual, NeoPa, you da man!

[NeoPa]

Very kind of you to say so Jennifer :-)