HTML Email look through two recordsets help (1 Viewer)

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Hello, I am using Office 365 and am trying to create an email from my Access database in HTML using two linked queries. The email generated looks like this:

1592242135902.png


But my preference is to look like this with each CM following after each issue:

1592242280271.png


Below is my code... can anyone point me in the right direction?

Code:
 strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                                
           strBody3 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
              "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody3 = strBody3 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _                       
                                 "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            
                            rs2.MoveNext
                            Loop
                        rs2.Close

Thank you.
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
For your issue, I don't see any closing table tags in your code. You should close the table for each issue prior to starting a table for each CM. Then after all CM's for an issue have been added, close that table as well.

To that, you don't really need strBody2 and strBody3. Just use one strBody and add to it the HTML that goes next until you've built the whole email.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Thank you for your response. I had the closing tags in here:

Code:
.HTMLBody = strBody2 & "</Table><br>" + strBody3 & "</Table><br>"

I have since moved them here:

Code:
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody3 = strBody3 & "</table>"

strBody2 and strBody3 are coming from queries on two separate but related tables (PK), so I am not clear on how I can use one strBody to get the results I am looking for....?
 

Cronk

Registered User.
Local time
Today, 18:30
Joined
Jul 4, 2013
Messages
2,514
The first line of your code in #1 is
Code:
strBody2 = "<TABLE Border=""1"......
That's associated with the outer loop.

The code in #3 is associating strBody2 with the inner loop. Swap strBody2 with strBody3
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
I think Cronked solved it in the prior post.

strBody2 and strBody3 are strings unrelated to queries and primary keys. They are where you are building your HTML. You're going thru your your data in the order you want it to appear in your HTML, so only 1 variable is needed to build all the HTML.

Code:
Enter Issue Loop
--strBody += html for 1 issue (opening tags, header row, 1 row of data, closing tags)
--strBody+=html to start related CM table (opening tags, header row)
--Start related CM Loop
----strBody+=html for 1 CM row of data
----Next CM item
--strBody += html to close related CM table (closing tags)
--Next Issue Item

If you had just 1 variable to store HTML it would have made misplaced closing <table> tags a little easier to identify.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Cronk, are you asking me to change to this?

Code:
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody3 = strBody3 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"

When I do that, it makes no difference to the email. I still get the same results as I got in image #1 above.
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
I see it now. You need to go with just 1 variable like I mentioned instead of 2.

strBody2 contains nothing but Issue records, strBody3 contains only CM records. When you concatenate them you get all Issues then all CMs. To get an issue and it's CMs then another issue and it's CMs, then another issue, etc, you need to use just 1 strBody variable.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Yes, I am getting closer..... made it all strBody2
Code:
                '*********** display issues & CMs
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                                
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
              "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _
                                 "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
'                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
'                            strBody2 = strBody2 & "</table>"

But I am not clear where to put my "</Table>" tag(s) .... and do I move my
Code:
rs2.MoveNext
Loop
rs2.Close

it's also generating all countermeasures for each Issue... so, I think I need to adjust my strSQL....
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
The HTML is fixed, now you need to fix your second (CM) recordset. You need to use the issue you are own as criteria in generating the second recordset. I don't know where strSQL3 is set, but it at least needs to be modified before this line:

Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
The "</table>" tags are not in the correct place. Don't I need to close the first table? If I leave them where they are the CMs table gets embedded in the issues table.
1592320958184.png

And If I close the first table by doing this:

Code:
                '*********** display issues & CMs
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                            strBody2 = strBody2 & "</table>" 'Move it here!
                                
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
                         "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _
                                                        "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"

Then I get this:

1592321261277.png


I fear I've made a muddled mess of this..... :-(
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
The HTML is fixed, now you need to fix your second (CM) recordset. You need to use the issue you are own as criteria in generating the second recordset. I don't know where strSQL3 is set, but it at least needs to be modified before this line:

Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)

Thanks.... strSQL3 is set just before I build the tables....

Code:
            strSQL3 = "SELECT * FROM qryZonePermCM " & _
                    " WHERE qryZonePermCM.ZoneMeetingID = " & HoldMeetingID & "AND ZoneIssueID = " & HoldZoneIssueID

however the ZoneIssueID will change as I loop through rs2 so I cannot point to the form to "Hold" it.... as I initially tried....
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
Your closing table tags were in the right place in post #8--you need one just before rs2.MoveNext

strSQL3 needs to take into account the current issue you are looking at. That is determined by the first recordset. So you need to compose strSQL3 with an element of the first recordset.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Your closing table tags were in the right place in post #8--you need one just before rs2.MoveNext
But I got the second table embedded in the first table when I did that....

strSQL3 needs to take into account the current issue you are looking at. That is determined by the first recordset. So you need to compose strSQL3 with an element of the first recordset.
I do not know how to do this.... any suggestions on how I can accomplish it? I am starting to Google it....
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
But I got the second table embedded in the first table when I did that....

That's because you used 2 different variables for your HTML.

I do not know how to do this.... any suggestions on how I can accomplish it?

You need to reconfigure the WHERE clause of strSQL3 to use whatever issue you are on. I'm not familiar with your data but I would assume that you would swap out HoldZoneIssueID with whatever Issue you are currently on in the first loop.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
That's because you used 2 different variables for your HTML.
Sorry, I thought it was where the "</table>" flag was... what do I do to overcome this?

You need to reconfigure the WHERE clause of strSQL3 to use whatever issue you are on. I'm not familiar with your data but I would assume that you would swap out HoldZoneIssueID with whatever Issue you are currently on in the first loop.

So should I be filtering by rs3 to what I am on in the first loop? I thought maybe this, but it doesn't work:
Code:
            strSQL3 = "SELECT * FROM qryZonePermCM " & _
                    " WHERE qryZonePermCM.ZoneMeetingID = " & HoldMeetingID & "AND ZoneIssueID = " & rs2!ZoneIssueID
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
Sorry, I thought it was where the "</table>" flag was... what do I do to overcome this?

Something like the below code is what you should have for the closing of your loops:

Code:
                       rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody = strBody & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody = strBody & "</table>"


So should I be filtering by rs3 to what I am on in the first loop?

Yes. In prior code you are referencing the first recordset data like this:


rs2.Fields![ZoneIssueNo].Value
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
Thank you....so much.... I am almost there! I updated the code as you suggested and I am still getting the 2nd table embedded in the first.... Sorry but I am confused by your comment:

That's because you used 2 different variables for your HTML.

1592333296877.png
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
Your probably not closing a table tag. Either you are starting the CM table prior to closing the issue tag, or you are generating too many opening table tags.

The best way to debug this is to dig into the HTML generated. Paste it into a text file then add tabs and spaces to make it readable.

Code:
<table>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
</table>
<table>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
</table>
A good text editor (Sublime, Notepad++) will help you find the closing tag for every open tag and even flag those without a partner.
 

Indigo

Registered User.
Local time
Today, 06:00
Joined
Nov 12, 2008
Messages
238
So, I have neither of those text editors ... so I pasted into Word and did a Find for "table" I have 2 open table tags and 2 closed table tags....

Code:
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           .
           .
           .
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           .
           .
           .
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"
 

plog

Banishment Pending
Local time
Today, 03:30
Joined
May 11, 2011
Messages
10,479
Hopefully that gets it, but its not what I meant. You pasted the VBA code into Word, I said you should post the actual HTML code it generates into a text editor.
 

Users who are viewing this thread

Top Bottom