Loop statement help

Hello,

I am having trouble creating a loop statement for an email I am trying to generate after the database is updated. I would like the email to list each item for a particular form. Right now it is only listing the first item of each form when the email is sent. Any ideas? Here is the email code:

if (debug = True) then
body = vbNewLine & "NOTE: THIS SYSTEM IS CURRENTLY IN DEBUG MODE AND ANY REPORTS YOU RECEIVE MAY BE FALSE" & vbNewLine & vbNewLine
end if

body = body & "---------------------" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "------" & vbNewLine & vbNewLine
body = body & "EACR Form Submission:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "Items:" & vbNewLine & vbNewLine
body = body & "---------------------" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "------" & vbNewLine & vbNewLine & vbNewLine

esql = "SELECT * FROM Eacr WHERE ID = " & Request.Form("eacrID")
rs6.open esql, conn, adOpenstatic, adLockReadOnly, adCmdText

asql = "SELECT * FROM User WHERE ID = " & Rs6("UserID")
rs.open asql, conn, adOpenstatic, adLockReadOnly, adCmdText

body = body & "Submitted By: " & Rs("FirstName") & " " & Rs("LastName") & vbTab & vbTab & vbTab & vbTab & "Lot #: " & eacrID & vbNewLine
body = body & "Date Submitted: " & today & vbTab & vbTab & vbTab & vbTab & vbTab & "Item " & i & ":" & vbNewLine & vbNewLine

asql = "SELECT * FROM ItemDetails WHERE EACRID = " & Request.Form("eacrID")
rs.open asql, conn, adOpenstatic, adLockReadOnly, adCmdText

bsql = "SELECT Description FROM Disposition WHERE ID = " & Rs6("DispositionID")
rs2.open bsql, conn, adOpenstatic, adLockReadOnly, adCmdText

body = body & "Lot #: " & eacrID & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & " Serial: " & Rs("SerialNumber") & vbNewLine
body = body & "Disposition Type: " & Rs2(0) & vbTab & vbTab & vbTab & " Decal #: " & Rs("Decal") & vbNewLine

bsql = "SELECT * FROM Department WHERE ID = " & Rs6("DepartmentID")
rs2.open bsql, conn, adOpenDynamic, adLockReadOnly, adCmdText

csql = "SELECT * FROM Reason"
rs3.open csql, conn, adOpenstatic, adLockReadOnly, adCmdText

dsql = "SELECT * FROM Condition"
rs4.open dsql, conn, adOpenstatic, adLockReadOnly, adCmdText

body = body & "Requisition Number: " & Rs6("ReqNumber") & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & " Description: " & Rs("Description") & vbNewLine
body = body & "Recipient: " & Rs6("Recipient") & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & " Manufacturer: " & Rs("Manufacturer") & vbNewLine
body = body & "Erase Hard Drives: " & Rs6("HDDCheck") & vbTab & vbTab & vbTab & vbTab & vbTab & " Model: " & Rs("Model") & vbNewLine
body = body & "Department Name: " & Rs2("Name") & vbTab & vbTab & vbTab & vbTab & " Building: " & Rs("Building") & vbNewLine
body = body & "Department Number: " & Rs2("Number") & vbTab & vbTab & vbTab & vbTab & vbTab & " Room: " & Rs("Room") & vbNewLine

body = body & "Department Phone: " & Rs2("Telephone") & vbTab & vbTab & vbTab & vbTab & vbTab & " Reason Code: " & Rs3("Description") & vbNewLine
body = body & " " & vbTab & vbTab & vbTab & vbTab & vbTab & " Condition Code: " & Rs4("Description") & vbNewLine
body = body & " " & vbTab & vbTab & vbTab & vbTab & vbTab & " Funding: " & Rs("Funding") & vbNewLine
body = body & " " & vbTab & vbTab & vbTab & vbTab & vbTab & " Value: " & Rs("Value") & vbNewLine & vbNewLine

Rs4.close
Rs3.close
Rs2.close
Rs.close
Rs6.close

I have experienced similar problems with email in the past, the only positive solution I have come up with is to set your variables from the database before writing the values directly to your email code.

So an example would be.


Allthough saying that I have also had issues like this because I kept overwriting my body with the new version, so to get around that I created a new body variable which would build the entire body.

Example below is not directly relevant to you because the email body resides in Html within a database then it gets called and it checks for predefined areas of the email which contain a specific string to point out to the code that a correspionding item needs to be replaced there.



I hope this helps you in some way.