Update Form then use RS to generate CDO Email

[devine]

Hi All,
I am trying to send an automatic email when an update has been made.
My update statement will updates 6 fields, and dependant on one of the fields, I would like to send an email using CDO.
Once the update is made, I am trying to re-query the database to retrieve all the fields that need to be included in the email, but it's just not working for me!!

This my code to update

Code:
<%
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/change control.mdb"




strSQL = "UPDATE tblform SET" & _
" PCM = " & "'" & Replace(Request.Form("PCM"),"'","''") & "'" & _
", PCMAction = " & "'" & Request.Form("PCMAction") & "'" & _
", PCMReason = " & "'" & Replace(Request.Form("PCMReason"),"'","''") & "'" & _
", Approve1 = " & "'" & Request.Form("Approve1") & "'" & _
", 1Action = " & "'" & Request.Form("1Action") & "'" & _
", 1Reason = " & "'" & Replace(Request.Form("1Reason"),"'","''") & "'" & _
", Approve2 = " & "'" & Request.Form("Approve2") & "'" & _
", 2Action = " & "'" & Request.Form("2Action") & "'" & _
", 2Reason = " & "'" & Replace(Request.Form("2Reason"),"'","''") & "'"

IF Request.Form("2Action") <> "" THEN
strSQL=strSQL & ", Status = '"& Request.Form("2Action") &"' "
End If
strSQL = strSQL & " WHERE ccf = " & Request.Form("ccf") & ";"

Conn.execute(strSQL)

idOfUpdatedRecord = Request.Form("CCF")
%>

<CENTER><H2>
<font face="InfoText Bd" color="#0388BB">Thank you, the status of Change <% = idOfUpdatedRecord %> has been successfully updated.</font></H2>
<%Conn.Close%>


Code:
This is my code to the query the database to retrieve all the details from the database and then use this to email:


Code:
<% IF Request.Form("2Action")="Approved" or Request.Form("2Action")="Rejected" THEN
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/change control.mdb"
set rs=Server.CreateObject("ADODB.recordset")
' Set SQL statement
SQL = "SELECT * FROM tblform WHERE ccf = " & Request.Form("ccf") & " "

' Open Recordset Object
rs.Open strSQL,conn





DIM strEmail, strOriginator, strOriginEmail, StrRep, strStartDate, strCCF, strBody, strStatus
strEmail=RS("Email")
strOriginator=Request.Form("Originator")strOriginEmail=Request.Form("Originator_Email")
strRep=Request.Form("Rep")
strSummary=Request.Form("Summary")
strImplementer=Request.Form("Implementer")
strStartDate=Request.Form("StartDate")
strStatus=Request.Form("2Action")
strStart=Request.Form("Start")
strRisk=Request.Form("Risks")
strCCF= Request.Form("CCF")
strBody="<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "CCF " & strCCF & " has been " & strStatus & "<br>" & "<br>"
strBody=strBody & "<font size=2 face=Helvetica Neue color=#260063>" & "<strong>"& "Start Date: " & strStartDate & "<br>"
strBody=strBody & "Start Time: " & strStart & "<br>"
strBody=strBody & "Change Summary: " & strSummary & "<br>"
strBody=strBody & "Implementer: " & strImplementer & "<br>" & "<br>" & "<br>"
strBody=strBody & "<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "Please contact Change@xxxx.com if you require further information" & "<br>"
strBody=strBody & "or Click here to view full details " & "<a href=http://127.0.0.1/view.asp?qryid="& StrCCF & ">"& strCCF & "</a>"


Set Mail=Server.CreateObject("CDO.Message")
'Next lines of code are for XP PWS only:
Set Config = CreateObject("CDO.Configuration")

'Configuration:
Config.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
Config.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") _
= "c:\inetpub\mailroot\pickup"


'Update configuration
Config.Fields.Update
Set Mail.Configuration = Config

Mail.From="Change@xxxxx.com"
Mail.To="changex@xxxxx.com;"& strOriginEmail & ";" & strEmail
Mail.CC=strRep
Mail.Subject="CCF " & strCCF & " has been " & strStatus
Mail.HTMLBody=strBody
Mail.Send
Set Mail=Nothing
End If
Conn.Close
%>

If I query the database and try to use RS, I get the ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/approval.asp, line 46 error
I do not get an error if I use Request.Form, but then there is no data populated for the email with the exception of the status and the ID number because they were request.form update fields!

I have checked for spelling errors when using RS, but still receive the
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/approval.asp, line 46 error
Thanks in advance for any help! :(