Solved Part 2 of Email from Record Searches (1 Viewer)

[NearImpossible]

Since the first issue was resolved, I started a 2nd thread. Please feel free to merge the two if warranted


I would like to group the emails per facility,i.e.

Facility 1 - Test@email.com; Test2@email.com
Facility 3 - Test3@email.com; Test4@email.com; Test5@email.com

so I setup the following test:

    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim f As String
    Dim e As String

    Set rs = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail]'", dbOpenDynaset, dbSeeChanges)
    
            While Not rs.EOF
                f = rs("[Facility Name]")
                      MsgBox f
                
                    Set rs2 = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail] where [Facility Name] ='" & f, dbOpenDynaset, dbSeeChanges)
                        While Not rs2.EOF
                            e = e & rs2("[Email]") & "; "
                            MsgBox e
                            rs2.MoveNext
                         Wend
                        
                 rs.MoveNext
            Wend

However when I run it, I get Syntax error in string in query expression '[Facility Name] = 'Facility 1'.

Again, here is the table I am using

Facility Name	Email	mWeek	mDay	mTime
Facility 1	test@mail.com	4th	Friday	1am
Facility 1	test2@mail.com	4th	Friday	1am
Facility 3	test3@mail.com	4th	Friday	12pm
Facility 3	test4@mail.com	4th	Friday	12pm
Facility 3	test5@mail.com	4th	Friday	12pm

 

[pbaldy]

You need to close the quote around the value:

Set rs2 = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail] where [Facility Name] ='" & f & "'"

 

[NearImpossible]

You need to close the quote around the value:

Set rs2 = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail] where [Facility Name] ='" & f & "'"

Wow, so I thought I did that but it kept saying Expected: list separator or ), turns out I wasn't putting the first quote on there and only had & '"

Thanks again !!

 

[pbaldy]

Happy to help!