SteveL13
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("TotalCos tQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp") , "##.00") & "<br>"
strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCos tQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp") , "##.00") & "<br>"
strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCos tQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp") , "##.00") & "<br>"
strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCos tQty5") + 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("TotalCos tQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp") , "##.00") & "<br>"
strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCos tQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp") , "##.00") & "<br>"
strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCos tQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp") , "##.00") & "<br>
etc. for the other two quantities 4 & 5.
Here is my current code:
strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCos
strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCos
strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCos
strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCos
And if rs.Fields("Qty3") is null then this should not appear in the email:
strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCos
strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCos
strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCos
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
ASKER
Almost perfect but I'm getting the "=" in the email for each line that should be blank..
--Steve
--Steve
I don't understand how....the entire line is skipped.
Post what you have.
Jim.
Post what you have.
Jim.
ASKER
In a module (was that where is was supposed to put it?):
And in the onclick event of the command button: (this is all of the code in the onclick)
'---------------------------------------------------------------------------------------
' 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
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
Steve,
This:
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.
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
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.
ASKER
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
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
ASKER
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.
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.
ASKER
ASKER
Note... when I did ? asc(rs.Fields("qty2")) at line 200 I get an invalid use of null error.
ASKER
And at line 220 I get:
? len(rs.Fields("qty2"))
Null
? 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.
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.
ASKER
Attached. This has me more puzzled than you.
Sample-DB.zip
Sample-DB.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Then:
If Not IsBlank(rst.Fields("Qty2")
strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCos
End If
Jim.