|
|
I hope someone can help me here.
I have a form where someone can select several zones from a combo box. These zones are added to the tblZoneApproval table (ASstatus = Pending), while the rest of the data is added to the tblRequestData table with a status of Pending. The authorized signer is responsible to log into an approval screen, that lists all the zones, and approve or reject each zone. Upon submit, the status is updated in the tblZoneApproval table.
What I need to do is upon submit I need to check the tblZoneApproval table to see:
if any status = Pending then do nothing
if all status = Approved then update status field in tblRequestData to Approved and send email to Card Access
if all status = Rejected then update status field in tblRequestData to Rejected and send email to requesting Manager
if status’ = Approved OR Rejected then update status field in tblRequestData to Approved and send an email to Card Access and requesting Manager (for zone that was rejected)
The tblZoneApproval table’s unique id: ZoneID
The tblRequestData tables’s unique id: RequestID
The two tables are linked together with the RequestID fields
Here’s my code
<%
dim myRS2, mySQL2, i, strZoneID, ZoneIDArray, thisStatus, thisRejReason, thisApprover, thisDate
'get ZoneID, then attach it to rdoStatus.
'split commas out
strZoneID = Request.Form("ZoneID")
ZoneIDArray = Split(strZoneID, ", ")
'i = the ZoneID. rdoStatus&i = the the status object name
for each i in ZoneIDArray
thisStatus = Request.Form("rdoStatus"&i)
thisRejReason = Request.Form("txtRejReason"&i)
thisApprover = Request.Cookies("UserName")
thisDate = (Date)
If thisStatus = "Approved" OR thisStatus = "Rejected" Then
'Update the tblZoneApproval table
mySQL2 = "UPDATE tblZoneApproval SET ASStatus = '"& thisStatus &"', ASRejReason = '"& thisRejReason &"', ApprovedBy = '"& thisApprover &"', AppDateTime = '"& thisDate &"' WHERE ZoneID = "& i &""
Set myRS2 = Server.CreateObject("ADODB.Recordset")
myRS2.Open mySQL2, myCONN, adOpenKeyset, adLockOptimistic, adCmdText
‘Here’s where I was trying to figure out how to create my if stmts
'If Request.Form("rdoStatus" &i) = "Approved" Then
' Response.Write "This Record is Approved"
'End If
'If Request.Form("rdoStatus" &i) = "Rejected" Then
' Response.Write "This Record is Rejected"
'End If
End If
next
%>
<%
Dim myRS3, mySQL3, thisReqID3
thisReqID3 = Request.Cookies("cRequestID")
mySQL3 = "SELECT ASStatus FROM tblZoneApproval WHERE RequestID='" & thisReqID3 & "'"
Set myRS3 = Server.CreateObject("ADODB.RecordSet")
myRS3.Open mySQL3, myCONN, adOpenKeyset, adLockOptimistic, adCmdText
‘Trying again to figure out how to pull all status’ and send them to the other table
'If myRS3("ASStatus") <> "Pending" Then
Do While myRS3("ASStatus") <> "Pending" 'NOT myRS3.EOF
Response.Write myRS3("ASStatus")
myRS3.MoveNext
Loop
'Else
'End If
%>
<%
‘This is where I would update the tblRequestData table
Dim myRS, mySQL, thisReqID, eReqType
thisReqID = Request.Cookies("cRequestID")
mySQL = "SELECT * FROM tblRequestData WHERE Rid='" & thisReqID & "'"
Set myRS = Server.CreateObject("ADODB.RecordSet")
myRS.Open mySQL, myCONN, adOpenKeyset, adLockOptimistic, adCmdText
''myRS("Status")= myRS3("ASStatus")
'myRS("LastModified") = (Now)
'myRS.Update
%>
‘Then I would send my emails here.
|
|
|
|
|
|
|
|
DAT