Solved Help with handling of Null (1 Viewer)

[June7]

Okay, maybe put query back to the original and handle the Null in VBA

.HTMLBody = Replace(.HTMLBody, "%Call1%", Nz(clientRST![Phone_Call_#1], "")

No, the VBA SQL has FROM Client.

 

[NauticalGent]

No, the VBA SQL has FROM Client.

Ah, I see that now. Thanks...

 

[allen675]

FROM Client" _

?

 

[allen675]

Okay, maybe put query back to the original and handle the Null in VBA

.HTMLBody = Replace(.HTMLBody, "%Call1%", Nz(clientRST![Phone_Call_#1], "")

No, the VBA SQL has FROM Client.

Whooohoooo thats working for the email body from 'Client' table THANK YOU SO MUCH ...................but still have null issue with notes i.e. if no notes are entered get an error at this point here:

salesRST.MoveFirst

Error saying no current record, any ideas?

 

[June7]

If Not salesRST.EOF and Not salesRST.EOF Then salesRST.MoveFirst

Review http://allenbrowne.com/ser-29.html

 

[allen675]

@June7 so I assume I need this in my code somewhere:

Do while Not rst.EOF
        If rst![MyField] <> Something Then  'The real loop exit condition.
            Exit Do
        End If
        ' Rest of your code here.
        rst.MoveNext
    Loop

Where and how please?

 

[June7]

Why don't you use the code suggested in post 51? What is not clear about the suggestion?

 

[allen675]

Why don't you use the code suggested in post 51? What is not clear about the suggestion?

Doh! My bag apologies completely didn't see that

@June7 thank you for all your help and persisting with me. Because of you I now don't need to go back to the drawing board, I'm forever in your debt

For whoever else looks at this forum here is the full working code:

Private Sub ClientStatus_Change()
    Dim sStatus As String
sStatus = Me!ClientStatus & ""
If sStatus <> "NPW - No Contact" And sStatus <> "NPW - Gone Elsewhere" And sStatus <> "NPW - Unable to Place" Then
    Exit Sub
End If
If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbNo Then
    Exit Sub
End If
    
Me.Refresh

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant
Dim strPaths() As String


strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3] FROM Client" _
            & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.path & "\RefundRequest.oft")
    
    strSQL = "SELECT NoteDate, Note" _
    & " FROM NoteHistory" _
    & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)
    
    
    
    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 0 To salesRST.Fields.Count - 1
        strTable = strTable & "<td>" & "</td>"
    Next i
    strTable = strTable & "</th>"
    
    ' TABLE ROWS
    If Not salesRST.EOF And Not salesRST.EOF Then salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close
    
    strSQL = "SELECT [FileName] FROM ContactProofT" _
    & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
    strPaths = Split(SimpleCSV(strSQL), ",")
    
    
    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"
        Dim x As Long
        For x = 0 To UBound(strPaths)
            .Attachments.Add CurrentProject.path & "\ContactProofs\" & strPaths(x)
        Next
        
            
        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", Nz(clientRST![Phone_Call_#1], ""))
        .HTMLBody = Replace(.HTMLBody, "%Call2%", Nz(clientRST![Phone_Call_#2], ""))
        .HTMLBody = Replace(.HTMLBody, "%Call3%", Nz(clientRST![Phone_Call_#3], ""))
        .HTMLBody = Replace(.HTMLBody, "%unsuccessful%", Nz(clientRST!Email_Sent, ""))
        .HTMLBody = Replace(.HTMLBody, "%message%", Nz(clientRST![SMS/WhatsApp_Sent], ""))
        .HTMLBody = Replace(.HTMLBody, "%Broker%", Nz(clientRST![Broker], ""))
        
        
        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)
        
        .Display
    
    End With
    
    
    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop
clientRST.Close
Set clientRST = Nothing
DoCmd.Close acForm, "CopyExistingLeadF"

End Sub