Code to pull results of one field with multiple answers

[clloyd]

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.

[NeoPa]

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.

[clloyd]

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

 Me![Email] _

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?

[NeoPa]

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.

[Krandor]

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.

[clloyd]

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:

Private Sub EmailReport_Click()
On Error GoTo Err_EmailReport_Click

    Dim Loc_No As String
    Dim stLinkCriteria As String

     
    stEmailTo = DLookup("[MailingsEMail]", "[Facilities]", "[Loc No] = '" & Me![Loc No] & "'")
    stLinkCriteria = "[Loc No]=" & "'" & ("[Loc No]=Query![qselReportDistForm]![Loc No]") & "'"
    DoCmd.SendObject acSendNoObject, stDocName, , stEmailTo, ReportMailingCC, , _
    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], _
"The following Facility Specific Copies personel listed needs to be moved to the CC" & " " & Me![Email] _
    & vbCr & "  " _
    & vbCr & "  " _
    & vbCr & Me![MailingEmailText]
    
         
Exit_EmailReport_Click:
    Exit Sub

Err_EmailReport_Click:
    MsgBox Err.Description
    Resume Exit_EmailReport_Click
    DoCmd.Close
    
End Sub

The Me![Email] field is the one that can have multiple records for one location.

[NeoPa]

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.

[clloyd]

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

[NeoPa]

No worries. Take your time.

When you better understand which way you want to go forward we can cover it in more detail :)