By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Creating a string by looping through records

P: 33
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.


Expand|Select|Wrap|Line Numbers
  1. Dim strEmailList As String
  2. Dim rst As DAO.Recordset
  3.  
  4. Set rst = CurrentDb.OpenRecordset("SELECT * FROM TblMailingList "WHERE [ID] = " & Me.LbxReportList)
  5.  
  6. Do Until rst.EOF
  7. strEmailList = strEmailList & rst.Fields(EmailID) & "; "
  8. rst.MoveNext
  9.  
  10. Loop
  11.  
  12. rst.Close
  13. Set rst = Nothing
I'd appreciate any help from anyone who can shed some light on this for me! Thanks!
Jun 2 '12 #1

✓ answered by NeoPa

You need to use :
Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
5 Replies


100+
P: 759
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.
Jun 2 '12 #2

P: 33
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:

Expand|Select|Wrap|Line Numbers
  1. Set miMail = appOL.CreateItem(olMailItem)
  2. With miMail
  3. .To = strEmailList
  4. .Subject = "NICU Patient Safety Team: End of Cycle Progress Report"
  5. .Body = ""
  6. .ReadReceiptRequested = False
  7. Set oMyAtt = miMail.Attachments.Add("C:\AccessReports\" & strReportTitle & ".pdf")
  8. .Save
  9. End With
  10. GetOut:
  11. 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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Jun 2 '12 #3

NeoPa
Expert Mod 15k+
P: 31,491
You need to use :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 2 '12 #4

P: 33
And as per usual, NeoPa, you da man!
Jun 3 '12 #5

NeoPa
Expert Mod 15k+
P: 31,491
Very kind of you to say so Jennifer :-)
Jun 3 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.