'Left Join' 2 tables together and loop through list-CodePudding

I'm making a function to format an email's body. It creates a HTMLtable one row at a time and loops/fills it with data from db tables. Its called from a Onclick event and everything gets Dim'd correctly with Outlook.Application.

The below code works but only pulls InputWalls table data. I do not know how to join 2 SQL statements together within VBA so I can pull data from two tables AND loop within the Do While Not ws.EOF.

This Left Join statement is located in a query somewhere else in my database but I cannot seem to reverse engineer it and loop it, Its perfect for what I need

FROM InputWalls LEFT JOIN SpecSheet ON (InputWalls.Client = SpecSheet.Client) AND (InputWalls.Code = SpecSheet.Code)

So combined InputWalls SpecSheet, This will output a table of data in the body of a email. It will be formatted as a table with mixed values from both sheets, but the relationship is with the 'Client' and 'Code' that links them together.

Below just shows that it outputs correctly. Its empty, but I have tested it with variables. Right now you can pull table data with ws!FIELDNAME

Function:

Public Sub SendHtmlMailFromAccess()
 
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olMessage As Outlook.MailItem

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olMessage = olApp.CreateItem(olMailItem)

With olMessage
    .subject = ""
    .HTMLBody = BuildHtmlBody()
    .bodyFormat = 2
    .Display
End With

End Sub

And my problem with the EOF line:

Function BuildHtmlBody()

    Dim html, ID, Plot, Ref, SpecCodes, Series, SIZE, BOXQTY, RateBox, Ext

    html = "<!DOCTYPE html><html><body>"
    html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBA. <br />"
    html = html & "Here is current recordset data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
        
        'table
        Dim ws, WAsql
        Set ws = CreateObject("ADODB.Recordset")
        WAsql = "Select * from InputWalls WHERE SITE = """ & Me.SITE.Value & """ AND PLOTNO = """ & Me.PLOTNO.Value & """ AND CLIENT = """ & Me.CLIENT.Value & """"
        ws.Open WAsql, CurrentProject.Connection
        ws.MoveFirst
        
        Do While Not ws.EOF
            Plot = Trim(ws!PLOTNO)
            Ref = Trim(ws!Ref)
            SpecCodes = "" 'Here is where I want to pull SpecSheet!Code from SpecSheet table where SpecSheet!Code = InputWalls.Code
            Series = Trim(ws!Series)
            SIZE = "" 'Also from spec Sheet
            BOXQTY = "" 'Also from spec Sheet
            RateBox = "" 'Also from spec Sheet
            Ext = "" 'Also from spec Sheet
            
            html = html & "<tr>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Plot & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Ref & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & SpecCodes & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Series & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & SIZE & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & BOXQTY & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & RateBox & "</td>"
            html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Ext & "</td>"
            html = html & "</tr>"
            ws.MoveNext
        Loop

    html = html & "</table></div></body></html>"
    BuildHtmlBody = html

End Function

CodePudding user response：

Not tested,

Try to change this:

WAsql = "Select * from InputWalls WHERE SITE = """ & Me.SITE.Value & """ AND PLOTNO = """ & Me.PLOTNO.Value & """ AND CLIENT = """ & Me.CLIENT.Value & """"

To this:

WAsql = "Select * from InputWalls LEFT JOIN SpecSheet ON (InputWalls.Client = SpecSheet.Client) AND (InputWalls.Code = SpecSheet.Code) WHERE InputWalls.SITE = """ & Me.SITE.Value & """ AND InputWalls.PLOTNO = """ & Me.PLOTNO.Value & """ AND InputWalls.CLIENT = """ & Me.CLIENT.Value & """"

And use:

ws!SpecSheet.SIZE
ws!SpecSheet.BOXQTY
ws!SpecSheet.RateBox