Solved

Create an If Then statement and insert one of two values into a database field

Posted on 2013-11-06
1
363 Views
Last Modified: 2013-11-06
I have a review page where a user, after making a selection in the previous page, is redirected to the page below to reject a submission. If they select Yes, a drop-down box appears that has a list of standard rejections. However, if none of the standard rejections meet the criteria, the person can select the last option.  The last option displays a text field to allow a person to type a rejection reason. The code is below for your review.

I need to create an If Then statement, beginning in line 35. If someone makes the last selection in the drop-down menu (RejectedExp), then the text field (RejectedText) appears. If text field (RejectedText) is populated, then this information inserts into the RejectedExp field. Otherwise, the drop-down (RejectedExp) inserts a selection into RejectedExp field.

Your assistance and help is greatly appreciated. Thank you.

<%
Dim rs
Dim rs_numRows

pID=Request.Querystring("ID")
pIDX=request.form("IDX")

if request("Submit") = "" Then
Set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\test\database\test.mdb"
rs.Source = "SELECT * FROM IBX WHERE ID=" & pID
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0

if request("Update") = "Update" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\test\database\test.mdb"

sql=""
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Status='" & Request.Form("Status") & "'"
End IF
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Rejected_Date_New='" & Request.Form("Rejected_Date_New") & "'"
End IF
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Rejected='" & Request.Form("Rejected") & "'"
End IF
' Need to create an If RejectedExp ="0", Then RejectedText, Else
If Request.Form("Status") & "" <> "" Then
sql=sql & ",RejectedExp='" & Request.Form("RejectedExp") & "'"
Else
If Request.Form("Status") & "" <> "" Then
sql=sql & ",RejectedExp='" & Request.Form("RejectedText") & "'"
End IF


sql="UPDATE IBX SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
'response.write sql 
rs.Execute sql
       
response.redirect "thankyou2.asp"   

end if
end if
%>

<html>
<head>
<title>Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/universal.css" rel="stylesheet" type="text/css">
<STYLE TYPE='text/css'>
a:link { color:#0000FF; text-decoration:underline}
a:visited { color:#FF0000; text-decoration:underline}
a:hover { color:#009900; text-decoration:underline; cursor:hand}
a:active { color:#FF0000; text-decoration:underline}
</STYLE>
<script language="javascript">
function process( selectedValue )
{
        if( selectedValue == "Rejected" )
        {
         document.getElementById("RejectedExp").style.display="";
        }
	else
        {
         document.getElementById("RejectedExp").style.display="none";
        }
}

function process2( selectedValue )
{
        if( selectedValue == "0" )
        {
         document.getElementById("RejectedText").style.display="";
        }
	else
        {
         document.getElementById("RejectedText").style.display="none";
        }
}
</script>
</head>

<body leftmargin="0" rightmargin="0" topmargin="0">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" class="text-xlarge">
  <tr> 
    <td height="40" bgcolor="#0000AA"><table width="100%" height="40" border="0" cellpadding="0" cellspacing="0" class="text-xlarge">
        <tr> 
          <td><div align="center"><strong><font color="#00FF00"><strong>REVIEW 
              AREA - REJECTION PAGE</strong></font></strong></div></td>
        </tr>
      </table></td>
  </tr>
  <tr> 
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td> <table border="0" cellspacing="0" cellpadding="0" width="950">
        <tr>
          <td><form name="form1" method="post" action="">
              <table width="950" border="0" cellpadding="0" cellspacing="0" class="text-times">
                <tr> 
                  <td width="10">&nbsp;</td>
                  <td width="225"><strong>Submitter's Name: 
                    <input name="IDX" type="hidden" id="IDX" value="<%=(rs.Fields.Item("ID").Value)%>">
                    </strong></td>
                  <td><%=(rs.Fields.Item("YourName").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Email Address:</strong></td>
                  <td><%=(rs.Fields.Item("YourEmail").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Number:</strong></td>
                  <td><%=(rs.Fields.Item("CaseYear1").Value)%>-<%=(rs.Fields.Item("CaseNumber1").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Tracking Number:</strong></td>
                  <td><%=(rs.Fields.Item("Tracking").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Approved On:</strong></td>
                  <td><%=(rs.Fields.Item("ViewDate").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Reviewed By:</strong></td>
                  <td><%=(rs.Fields.Item("Who").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Proposed Submission:</strong></td>
                  <td><a href="<%=(rs.Fields.Item("Link").Value)%>" target="_blank">Order</a> 
                  </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong><font color="#CC0000">Reject Submission?</font></strong></td>
                  <td><select name="Status" id="Status" onchange="process(this.options[this.selectedIndex].value)">
                      <option value="">Select</option>
                      <option value="Rejected">Yes</option>
                    </select> &nbsp; </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><select name="RejectedExp" id="RejectedExp" style="display:none;" onchange="process2(this.options[this.selectedIndex].value)">
                      <option value="Order was rejected">ORDER REJECTED? PLEASE 
                      SELECT AN EXPLANATION BELOW.</option>
                      <option value="Reason One">Reason One</option>
                      <option value="Reason Two">Reason Two</option>
                      <option value="Reason Three">Reason Three</option>
                      <option value="Reason Four">Reason Four</option>
                      <option value="Reason Five">Reason Five</option>
                      <option value="0">Type Rejection Reason</option>
                    </select></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="RejectedText" type="text" id="RejectedText" style="display:none;" size="80" maxlength="100"></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="Rejected_Date_New" type="hidden" id="Rejected_Date_New" value="<%=date()%>"> 
                    <input name="Rejected" type="hidden" id="Rejected" value="SUBMITTED ORDER REJECTED"> 
                  </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>BEFORE UPDATING, PLEASE</strong></td>
                  <td><strong>ENSURE YOU ARE REJECTING THE CORRECT SUBMISSION.</strong></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="Update" type="submit" id="Update" value="Update"> 
                    &nbsp; <input type="reset" name="Reset" value="Cancel"></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
              </table>
            </form></td>
        </tr>
      </table>
      <p>&nbsp;</p></td>
  </tr>
</table>
</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>

Open in new window

0
Comment
Question by:arendt73
1 Comment
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39628570
try this

If Request.Form("Status") & "" <> "" Then
     dim rejected
     if request.Form("RejectedExp") = "0" then
          rejected = Request.Form("RejectedText")
     else
        rejected = request.Form("RejectedExp")
     end if
     sql=sql & ",RejectedExp='" & rejected & "'"
else...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now