Access VBA使用Left Join关联两表循环生成邮件HTML表格问题

核心修改点只有两处：替换原单表SQL为左连接查询、调整循环内的字段读取逻辑，原有HTML拼接、Outlook发信的代码完全不需要改动。

1. 替换原有SQL查询语句 #

删掉原来单表查InputWalls的SQL，换成带LEFT JOIN的查询语句，注意Access的SQL语法要求多表连接时明确指定字段来源，避免两表同名字段（Code、Client两表均存在）冲突：

WAsql = "SELECT " & _
        "  InputWalls.PLOTNO, " & _
        "  InputWalls.Ref, " & _
        "  InputWalls.Series, " & _
        "  SpecSheet.Code AS SpecCodes, " & _
        "  SpecSheet.SIZE, " & _
        "  SpecSheet.BOXQTY, " & _
        "  SpecSheet.RateBox, " & _
        "  SpecSheet.Ext " & _
        "FROM InputWalls " & _
        "LEFT JOIN SpecSheet " & _
        "  ON (InputWalls.Client = SpecSheet.Client) AND (InputWalls.Code = SpecSheet.Code) " & _
        "WHERE " & _
        "  InputWalls.SITE = '" & Replace(Me.SITE.Value, "'", "''") & "' " & _
        "  AND InputWalls.PLOTNO = '" & Replace(Me.PLOTNO.Value, "'", "''") & "' " & _
        "  AND InputWalls.CLIENT = '" & Replace(Me.CLIENT.Value, "'", "''") & "'"

这里做了两个优化：

• 对SpecSheet的Code字段起别名SpecCodes，和InputWalls的Code字段区分开
• 用Replace转义字段值里的单引号，避免值里含单引号时触发SQL语法错误，比原代码直接拼双引号兼容性更好

2. 修改循环内的字段赋值逻辑 #

原来留空的SpecSheet字段直接通过记录集读取即可，加Nz函数处理空值，避免关联不到匹配数据时字段为Null触发运行时错误：

Do While Not ws.EOF
    ' 读取InputWalls表字段
    Plot = Trim(Nz(ws!PLOTNO, ""))
    Ref = Trim(Nz(ws!Ref, ""))
    Series = Trim(Nz(ws!Series, ""))
    ' 读取关联后的SpecSheet表字段
    SpecCodes = Trim(Nz(ws!SpecCodes, ""))
    SIZE = Trim(Nz(ws!SIZE, ""))
    BOXQTY = Trim(Nz(ws!BOXQTY, ""))
    RateBox = Trim(Nz(ws!RateBox, ""))
    Ext = Trim(Nz(ws!Ext, ""))

    ' 原有HTML表格行拼接逻辑保持不变
    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
' 用完记录集记得关闭释放
ws.Close
Set ws = Nothing

注意事项 #

• 因为用的是LEFT JOIN逻辑，如果某条InputWalls记录在SpecSheet中找不到匹配的Client+Code组合，对应的SpecSheet字段会返回Null，Nz函数会自动把Null转为空字符串，表格对应位置留空，完全符合业务预期
• 两表关联查询不要用SELECT *写法，两表存在同名字段时ADODB记录集会出现值覆盖的问题，明确指定需要查询的字段是最稳定的写法
• 原代码中ws.MoveFirst可以删掉，记录集刚打开时默认指针就在第一条，写了属于冗余代码

内容的提问来源于stack exchange，提问作者mccarthy995