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

Code to pull results of one field with multiple answers

P: 93
I have a query I am using to create an email. I have a situation where for one location I have three people that have to receive the information. In the query it creates three records however in my code it only recognizes the first person and does not pick up the other two. How can I get either the code or the query to combine that field for all three records.
Sep 11 '08 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,494
As we have no idea what your query says OR what your code says - how can we possibly answer that.

Please give some consideration to your question before posting. It can save so much time.
Sep 11 '08 #2

P: 93
My code is not the problem. I pulls the field in just fine. It is as follows:

Expand|Select|Wrap|Line Numbers
  1.  Me![email] _
  2.  
My problem is in the query. I have three records as a result of my request thus three contacts that need to pull in my email from the email field. Because they are seperate records it will only pull in the first one it finds and not the other two.

Does this explanation help?
Sep 15 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
No code (an isolated reference to a single object is NOT code), no SQL, no context, no table information.

Did you really think that would help? I find it hard to credit.
Sep 15 '08 #4

P: 50
If you have 3 records then you will need to send out 3 emails. You go through the recordset with a loop until you get to the last record. For each new record, you process the email sending code.

And as Neo said, actually posting either the query or the processing code is going to make your life easier and get a much quicker response.
Sep 16 '08 #5

P: 93
I am sorry to have upset you however consider that I am still new at this.

I don't know if this will help or not. I understand I need some type of looping but just can't figure out how to get there.

My tables are as follows: One (Contacts) holds the contact persons information the other table is linked by the Contacts ID and Location number which identifies what location they are responsible for. One location can have three contact people.

Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub EmailReport_Click()
  2. On Error GoTo Err_EmailReport_Click
  3.  
  4.     Dim Loc_No As String
  5.     Dim stLinkCriteria As String
  6.  
  7.  
  8.     stEmailTo = DLookup("[MailingsEMail]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'")
  9.     stLinkCriteria = "[Loc No]=" & "'" & ("[Loc No]=Query![qselReportDistForm]![Loc No]") & "'"
  10.     DoCmd.SendObject acSendNoObject, stDocName, , stEmailTo, ReportMailingCC, , _
  11.     Me![MailingEmailTextSubjectLine] & "," & " " & DLookup("[Client Name]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'") & "," & " " & DLookup("[Physical City]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'") & "," & " " & DLookup("[Physical State]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'") & "," & " " & DLookup("[Physical Country]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'") & " " & "Loc No" & " " & Me![Loc No], _
  12. "The following Facility Specific Copies personel listed needs to be moved to the CC" & " " & Me![email] _
  13.     & vbCr & "  " _
  14.     & vbCr & "  " _
  15.     & vbCr & Me![MailingEmailText]
  16.  
  17.  
  18. Exit_EmailReport_Click:
  19.     Exit Sub
  20.  
  21. Err_EmailReport_Click:
  22.     MsgBox Err.Description
  23.     Resume Exit_EmailReport_Click
  24.     DoCmd.Close
  25.  
  26. End Sub
The Me![email] field is the one that can have multiple records for one location.
Sep 18 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
If Me.Email can possibly have multiple records, then surely we have a logic problem. I'm having to read between the lines somewhat, but I suspect the form will only show one of these at a time. That makes life a little more complicated. We either need to design things such that a ";" separated string is available on the form, or we could possibly look at opening a DAO.Recordset in the code to access each in turn within the routine.

Have a look at Basic DAO recordset loop using two recordsets for some basic use of Recordset coding. We can get into more detail if and when required, but this may point you in the right direction.
Sep 19 '08 #7

P: 93
Thank you I will and get back to you. I may not have explained this very well and apologise for that. I have to have two tables one is the Location and a location can have multiple safety people assigned that have their own table with their personal information. In sending an email for a location I have to pull in multiple safety people. You are correct in that it will only pull in the first one it sees. As I mentioned I will review this informtion you provided and let you know. Thanks
Sep 19 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
No worries. Take your time.

When you better understand which way you want to go forward we can cover it in more detail :)
Sep 19 '08 #9

Post your reply

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