Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Loop in Access VBA code and make line not appear in email if criteria exists

This is the code I am working with below.    But for example, if rs.Fields("Qty2") is null then this should not appear in the email:

            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>

And if rs.Fields("Qty3") is null then this should not appear in the email:

 strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>

etc. for the other two quantities 4 & 5.

Here is my current code:

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            strBody = strBody & "Size: " & rs.Fields("Size") & "<br>"
            strBody = strBody & "Stock: " & rs.Fields("Description") & "<br>"
            strBody = strBody & "Colors: " & rs.Fields("InkColors") & "<br>"
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>
            strBody = strBody & rs.Fields("Qty1") & " = " & Format(rs.Fields("TotalCostQty1") + rs.Fields("TotalCostQty1") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
<br>"
            
            rs.MoveNext
            
        Loop
    .HTMLBody = strBody & "</html>"
    .send

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Paste this in you DB:

'---------------------------------------------------------------------------------------
' Procedure : IsBlank
' DateTime  : 09/07/2005 16:37
' Author    : WEC
' Purpose   : Tests a variable for Null or Zero-Length String
'---------------------------------------------------------------------------------------
Function IsBlank(ByVal varValue As Variant) As Boolean

          Const Routine = "IsBlank"
          Const Version = "1.0.0"

10        On Error Resume Next
          
20        IsBlank = True
30        IsBlank = (varValue = "" Or IsNull(varValue))

End Function

Open in new window


Then:

If Not IsBlank(rst.Fields("Qty2")) then
strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
End If

Jim.
Avatar of SteveL13

ASKER

Almost perfect but I'm getting the "=" in the email for each line that should be blank..

--Steve
I don't understand how....the entire line is skipped.

 Post what you have.

Jim.
In a module (was that where is was supposed to put it?):

'---------------------------------------------------------------------------------------
' Procedure : IsBlank
' DateTime  : 09/07/2005 16:37
' Author    : WEC
' Purpose   : Tests a variable for Null or Zero-Length String
'---------------------------------------------------------------------------------------
Function IsBlank(ByVal varValue As Variant) As Boolean

          Const Routine = "IsBlank"
          Const Version = "1.0.0"

10        On Error Resume Next
          
20        IsBlank = True
30        IsBlank = (varValue = "" Or IsNull(varValue))

End Function

Open in new window


And in the onclick event of the command button: (this is all of the code in the onclick)

Private Sub cmdEmailEstimate_Click()

   Dim olApp As Object
   Dim objMail As Object
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strBody As String
   Dim strsql As String
   setdb = CurrentDb
   


   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = Me.txtContactEmail
     .Subject = "Estimate # " & [txtEstID] & " - Dated " & [txtEstDate]
     '.HTMLBody = "<htmltags>Regular Hours = " & RegHrs & "<br>Overtime Hours = " & OThrs & "<br>Total Hours = " & totHrs & "<br></htmltags>"
     strBody = "<html>Date: " & [txtEstDate] & "<br>Dealer: " & [txtDealerName] & "<br>Contact: " & [txtContactName] & "<br><br> Thank you for the opportunity to provide the following quotation: <br><br>"


   'Loop through query results starts here...
    Set rs = CurrentDb.OpenRecordset("qryEstimateDetailReport")

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            strBody = strBody & "Size: " & rs.Fields("Size") & "<br>"
            strBody = strBody & "Stock: " & rs.Fields("Description") & "<br>"
            strBody = strBody & "Colors: " & rs.Fields("InkColors") & "<br>"

            strBody = strBody & rs.Fields("Qty1") & " = " & Format(rs.Fields("TotalCostQty1") + rs.Fields("TotalCostQty1") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            
            If Not IsBlank(rst.Fields("Qty2")) Then
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty3")) Then
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty4")) Then
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty5")) Then
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br><br>"
            End If
            
            rs.MoveNext
            
        Loop
    .HTMLBody = strBody & "</html>"
    .send

    MsgBox "This estimate has been emailed to the contact email address."
    End If
    End With

End Sub

Open in new window

Steve,

This:

            If Not IsBlank(rst.Fields("Qty2")) Then
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If

Open in new window


 Looks fine.  I don't see where the equals sign would be coming in.

 What I would suggest is:

1. put a STOP at line 40 of your code.
2. execute.
3. When you hit the stop, call up the debug window (Ctrl/G) and type:

 ? strBody

 and a return.   You'll see the body of the message as it stands.

 Press F8 to continue stepping through the code.   You can stop at any point and printout strBody again and you should see the code skip over the statement for the qty's that are blank.

 Press F5 to get out of step mode and continue execution.

Jim.
I tried that and didn't seem to accomplish anything.  Here is a copy/paste of what I'm getting in the email:

Date: 5/1/2014  (this is not meant to be today's date and is ok)

Dealer: ABC Company
Contact: George Washington

Thank you for the opportunity to provide the following quotation:   (there is only one quantity in the first "group"

Size: #10
Stock: #10 Regular - 24lb. White Wove - Diagonal Seam
Colors: CMYK
250 = $42.80
=
=
=
=

Size: #10
Stock: #10 Regular - 24lb. Classic Laid - Antique Gray
Colors: Black
250 = $49.21
500 = $91.92
1000 = $177.33
2000 = $348.16
3000 = $519.00
Sorry, and I need the equal sings to go away on blank lines.
<<I tried that and didn't seem to accomplish anything. >>

 In what way?   You should have been able to step through the code line by line with F8.   You should have been able to see it skip over putting a line in the e-mail if the qty was blank

 But it seems your qty fields must have something other than a zero length string or a null in them.

 Leave the stop in at 40, and when you hit it, bring up the debug window if not already up (Ctrl/G), and type:

? asc(rs.Fields("qty2"))

 followed by a return

also do:

? Len(rs.Fields("qty2"))

and tell me what you get.


Jim.
Jim,

I have done what you suggested and get two errors.  I have attached two screen shots so you can see exactly what I am getting.
User generated image
User generated image
Note... when I did ? asc(rs.Fields("qty2")) at line 200 I get an invalid use of null error.
And at line 220 I get:

? len(rs.Fields("qty2"))
Null
You got me...any way you can create a small sample DB with just the table(s) and the query your using to pull the data.

The last indicates that the field is NULL, which means the NZ()  (NUll to Zero) function should have worked fine.   I didn't understand why it's not.

Jim.
Attached.  This has me more puzzled than you.
Sample-DB.zip
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial