INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

How to connect a web page to MS ACCESS Database by AccessGuruCarl
Posted: 4 Jul 04

You will need to create 3 .asp web pages(for a login)
host them on IIS server with Active Server Page and have MS Access installed on the server!

This page is called login.asp
    It uses a FORM to pass the values to the web page called "login-validate.asp"


<< BEGIN CODE for 1st page>>


<%@ Language=VBScript %>
<html>
<head>
<meta NAME="GENERATOR" Content="Microsoft FrontPage 5.0">
<title>Login Page</title>
<script language="javascript">
function noRightClick() {
if (event.button==2) {
alert('Sorry, while viewing our website your Right Click action has been disabled.')
}
}
document.onmousedown=noRightClick
</script>
</head>
<body bgcolor="#ffffff">
<script LANGUAGE="vbscript" RUNAT='Server'>
Response.Write"<form METHOD='post' ACTION='login-validate.asp' id='form1' name='form1'>"

' If you change the web page names... you must change the ACTION= in the above line...
'  Check out using FORMS in HTML, if your not familiar...

Response.Write"<Table CellPadding=2 Width=100%>"    
Response.Write"<tr>"
Response.Write"<td width='185' rowspan='2'><img src='images/logo.gif'></td>"
Response.Write"<td width='7' valign='top' rowspan='2'>&nbsp;<p>&nbsp;</td>"
Response.Write"<td width='530' valign='bottom' align='center'><h2><br>"
Response.Write"<br>"
Response.Write"    Welcome to the ===== Login Page</h2>"
Response.Write"</td>"
Response.Write"</tr>"
Response.Write"<tr>"
Response.Write"<td width='530' valign='top'><font color=0000FF>Enter the login name and password you"
Response.Write"    where supplied, and press LOGIN to continue.</font> <br>"
Response.Write" <p>   "
Response.Write"    "
Response.Write"</td>"
Response.Write"</tr>"
Response.Write"</Table>"    
Response.Write"<BR>"
Response.Write"<Table CellPadding=2 Width=100%>"
Response.Write"<TR>"
Response.Write"<TD Align='Right' Width=50%><FONT color=000000>Login Name</FONT>&nbsp;"
Response.Write"</TD>"
Response.Write"<TD Align='Left' Width=40%><input id='Name' name='Name' SIZE='15'>"
Response.Write"</TD>"
Response.Write"</TR>"
Response.Write"<TR>"
Response.Write"<TD Align='Right'><FONT color=000000>Password</FONT>&nbsp;"
Response.Write"</TD>"
Response.Write"<TD Align='Left'><input TYPE='password' id='Password' name='Password' SIZE='15'>&nbsp;&nbsp;<FONT color=

crimson>(Will appear as ****)</FONT>"
Response.Write"</TD>"
Response.Write"</TR>"
Response.Write"<TR>"
Response.Write"<TD>"
Response.Write"</TD>"
Response.Write"<TD><P>&nbsp;</P>"
Response.Write"</TD>"
Response.Write"<TD>"
Response.Write"</TD>"
Response.Write"</TR>"
Response.Write"<TR>"
Response.Write"<TD>"
Response.Write"</TD>"
Response.Write"<TD><input id='submit1' name='submit1' type='submit' value='LOGIN'>"
Response.Write"</TD>"
Response.Write"<TD>"
Response.Write"</TD>"
Response.Write"</TR>"
Response.Write"</Table>"    
Response.Write"<BR>"
Response.Write"<BR>"
Response.Write"</form>"
Response.Write"</FONT>"
</SCRIPT></body></html>


<< END CODE for page 1 >>


==================================================


2nd web page....

call this one.. login-validate.asp

<< BEGIN CODE for page 2 >>



<%Response.Buffer = True%>
<HTML>
<HEAD>
<%
Dim strRemoteIP
strRemoteIP =  LCase(Request.ServerVariables("REMOTE_ADDR"))    ' Client's IP address
' If you want to track IP login's...
%>
<TITLE>Login Validation</TITLE>

</HEAD>
<BODY>


<%
' Open a DNS-Less connection to the database

set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Data Source=" & Server.Mappath("TheXYZ.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

set rs = Server.CreateObject("ADODB.Recordset")

' Setup the JOINS, and the relationship within the tables

sql="SELECT tblUsers.UserID, tblUsers.Login, tblUsers.Password "
sql=sql & "FROM tblUsers "
sql=sql & "WHERE tblUsers.Login=" & Chr(34) & Request.Form("Name") & Chr(34)
sql=sql & " AND tblUsers.Password=" & Chr(34) & Request.Form("Password") & Chr(34)

rs.Open sql, conn

On ERROR RESUME NEXT
' Track login attempts - requires cookies...
' Or if users don't wish to allow cookies, update it to a table...
' This option is the easiest - Instruct users to change thier cookies setting to allow your site
If rs.recordcount <> 1 Then
'Set a log file - give it a 1 hour life
If Request.Cookies("status") = "" Then    '1st login attempt
  Response.Cookies("status")= 1
  Response.Cookies("status").Expires=DateAdd("h",1,Now)
Else
  Response.Cookies("status")= Request.Cookies("status") + 1                    
  Response.Cookies("status").Expires=DateAdd("h",1,Now)
End If
End If

'Determine if we ban the IP address
If Request.Cookies("status") > 4 Then
'Ban the IP address for 1 hr - Denies User Login - Adds a record to the database
    Set connupdate = Server.CreateObject( "ADODB.Connection" )
    Set rec = server.CreateObject("ADODB.Recordset")
'Create connection to the table, and use OLEDB.4.0 driver
    connupdate.Open "Data Source=" & Server.Mappath("TheXYZ.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"
    dim objRS
    set objRS = server.CreateObject("ADODB.Recordset")
   ' Set the cursor location to client side (3) -- required
    objRS.CursorLocation = 3
    objRs.open "Select * from tblBannedIP where RecordID = 0", connupdate, 3, 3
    objRS.AddNew
    objRS.Fields("IP") = strRemoteIP
    objRS.Update
    objrs.Close
set objrs = Nothing
connupdate.Close
set connupdate = nothing
MyRedirect = "http://yourcompany.com/XYZ-FOLDER/banned.asp"
Else
MyRedirect = "http://yourcompany.com/XYZ-FOLDER/welcome.asp"
End If

If rs("UserID") <> 0 Then
  MyID = rs("UserID")    ' Valid Login
'Set the Cookie to the User ID  - give it a 1 day life.
    Response.Cookies("Scout-No")=MyID
    Response.Cookies("Scout-No").Expires=DateAdd("d",1,Now)
End If

rs.Close
set rs = Nothing
 conn.Close
set conn = nothing
%>
</BODY></HTML>
<html>
<head>
<title>Re-Directing to Welcome Page</title>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
redirTime = "3000";
redirURL = "<%=MyRedirect%>";
function redirTimer() { self.setTimeout("self.location.href = redirURL;",redirTime); }
//  End -->
    </script>
</head>
<body onLoad="redirTimer()">
<div align="center"><h2>
    Please Wait</h2><br>
    <br>Processing your Login<br>
    <br>You will be Re-Directed in a few seconds.
    <br>If you are not re-directed in a 10 seconds, try clicking below.
    <br>
    You may also get there by clicking
    <a href="http://yourcompany.com/XYZ-FOLDER/welcome.asp"
        ONMOUSEOVER="window.status='Welcome Page'; return true;"
        ONMOUSEOUT="window.status=''; return true;">here</a><br>
    </div>
    <br><br>
</body>
</html>


<< END CODE for page 2 >>


==================================================


last web page....

call this one.. welcome.asp

<< BEGIN CODE for page 3 >>


<html>
<head>
<title>Welcome Page</title>
<script language="javascript">
function noRightClick() {
if (event.button==2) {
alert('Sorry, while viewing our website your Right Click action has been disabled.')
}
}
document.onmousedown=noRightClick

</script>

<SCRIPT event=onclick for=Command0 language=vbscript>
<!--

MsgBox ("Feature Not Installed Yet!")

-->
</SCRIPT>

</head>
<body>
<%
' Open a DNS-Less connection to the database

set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Data Source=" & Server.Mappath("TheXYZ.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

set rs = Server.CreateObject("ADODB.Recordset")

 'Set the Joins, and the relationships within the tables.
'  & request.Form("Scout-No")
'response.buffer=false

'on error resume next

If Request.Cookies("Scout-No")<>"" Then
sql="SELECT tblScouts.lngS_ID, tblDenLeaders.txtDL_DenNumber, tblDenLeaders.txtDL_DenType, "
sql=sql & "tblScouts.txtS_FirstName, tblScouts.txtS_LastName, tblScouts.txtS_Address, "
sql=sql & "tblScouts.txtS_PhoneNum, tblScouts.booMultiFamily "
sql=sql & "FROM tblDenLeaders INNER JOIN tblScouts ON tblDenLeaders.txtDL_ID = tblScouts.txtDL_ID "
sql=sql & "WHERE tblScouts.lngS_ID = " & Request.Cookies("Scout-No")

rs.CursorLocation = 3  ' adUseClient
rs.Open sql, conn
rs.PageSize = 1
intPageCount = rs.PageCount
MyDen = rs("txtDL_DenNumber")
MyScoutID= rs("lngS_ID")

' Used later in code for navigation buttons - can be removed - BE CAREFUL - BackUP,Edit,Reload...
            ' Watch for the If - Then Statement when editing....
Select Case Request("Action")
    case "<<"
        intpage = 1
    case "<"
        intpage = Request("intpage")-1
        if intpage < 1 then intpage = 1
    case ">"
        intpage = Request("intpage")+1
        if intpage > intPageCount then intpage = IntPageCount
    Case ">>"
        intpage = intPageCount
    case else
        intpage = 1
end select
%>

<%
rs.AbsolutePage = intPage
For intRecord = 1 To rs.PageSize
Response.Write"<table border='0' cellpadding='0' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111'

width='100%' id='AutoNumber1'>"
  Response.Write"<tr>"
    Response.Write"<td colspan='2' align='center'>"
    Response.Write"<table border='0' cellpadding='0' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111'

width='100%' id='AutoNumber2'>"
      Response.Write"<tr>"
        Response.Write"<td width='22%'>"
        Response.Write"<img border='0' src='images/cubscout.jpg' width='157' height='148'></td>"
        Response.Write"<td width='78%'>&nbsp;&nbsp;&nbsp; Den :&nbsp; "& rs.Fields("txtDL_DenNumber") & "

&nbsp;&nbsp;&nbsp;&nbsp;Den Level:&nbsp; "& rs.Fields("txtDL_DenType") & "&nbsp;</td>"
      Response.Write"</tr>"
      Response.Write"<tr>"
        Response.Write"<td width='22%'>&nbsp;</td>"
        Response.Write"<td width='78%'>&nbsp;</td>"
      Response.Write"</tr>"
    Response.Write"</table>"
    Response.Write"</td>"
  Response.Write"</tr>"
  Response.Write"<tr>"
    Response.Write"<td width='100%' colspan='2'>Scout ID:&nbsp; "& rs.Fields("lngS_ID") & " </td>"
  Response.Write"</tr>"
  Response.Write"<tr>"
    Response.Write"<td width='100%' colspan='2'>&nbsp;</td>"
  Response.Write"</tr>"
  Response.Write"<tr>"
    Response.Write"<td width='35%'>Scout Name:&nbsp; "& rs.Fields("txtS_FirstName") & "  &nbsp; "& rs.Fields("txtS_LastName")

& " </td>"
    Response.Write"<td width='65%'>Phone #:&nbsp; "& rs.Fields("txtS_PhoneNum") & " </td>"
  Response.Write"</tr>"
  Response.Write"<tr>"
    Response.Write"<td width='100%' colspan='2'>Address:&nbsp; "& rs.Fields("txtS_Address") & " </td>"
  Response.Write"</tr>"
  Response.Write"<tr>"
    Response.Write"<td width='100%' colspan='2'>Multiple Family Members:&nbsp; "& rs.Fields("booMultiFamily") & " </td>"
  Response.Write"</tr>"
Response.Write"</table>"

MyScoutID= rs("lngS_ID")

    rs.MoveNext
If rs.EOF Then Exit For

Next
rs.Close

set rs = Nothing
conn.Close
set conn = nothing
%>
</table>
<p><p>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="

Page_Links" >
  <tr>
    <td width="25%"><p align="right"><form name="Awards" action="awards.asp" method="post">
<input type="hidden" name="Scout-No" value="<%=MyScoutID%>" size="3">
<input type="submit" value="View Awards">
</form></td>
    <td width="25%"><p align="center"><form name="Popcorn" action="popcorn.asp" method="post">
<input type="hidden" name="Scout-No" value="<%=MyScoutID%>" size="3">
<input type="submit" value="Popcorn Sales">
</form></td>
    <td width="25%"><p align="center"><form name="Dues" action="dues.asp" method="post">
<input type="hidden" name="Scout-No" value="<%=MyScoutID%>" size="3">
<input type="submit" value="Pack Dues">
</form></td>
    <td width="25%"><form name="DenLeader" action="den-leader.asp" method="post">
<input type="hidden" name="Den-No" value="<%=MyDen%>">
<input type="submit" value="Den Leader Info">
</form></td>
  </tr>
  <tr>
    <td colspan =4><center><form method="POST" action="/62/edit-login/edit.asp">
  <input type="hidden" name="Scout-No" value="<%=MyScoutID%>" size="3">
  <a title="Click here to edit your login information." href="/edit-login/edit.asp">
  <input type="Submit" value="Edit Login Information" name="B1"></a>
</form>

    </td>
  </tr>
</table>
<%
Else
Response.Write"<p><p>"
Response.Write"<CENTER><FONT Color='Red' Size=6><B><U>INCORRECT LOGIN!</U><BR><BR>&nbsp;&nbsp;&nbsp; TRY AGAIN!</B></FONT>"
Response.Write"<P>&nbsp;</P>"
Response.Write"<FONT Color='Red' Size=6><A href='//yourcompany.com/XYZ-FOLDER/'>Click Here</A>&nbsp;to try again!</CENTER>"

End If
%>
</body>
</html>


<< END CODE for page 3 >>


=====================================================================


HERE IS SOME OTHER USEFUL CODE...
    EDIT THEN UPDATE THE DATA...

Here is a page getting popcorn orders placed by a scout, that he edits his sales...

<< BEGIN CODE >>

<html>
<head><title>Edit your Popcorn Order</title>
<script language="javascript">
function noRightClick() {
if (event.button==2) {
alert('Sorry, while viewing our website your Right Click action has been disabled.')
}
}
document.onmousedown=noRightClick
</script>
</head>
<body>
<%
' Open the database
Set connupdate = Server.CreateObject( "ADODB.Connection" )
connupdate.Open "Data Source=" & Server.Mappath("TheXYZ.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"
Set MyConn=Server.CreateObject("ADODB.Connection")

'QueryString to select specific scout order
' & request.Form("Scout-No")
sql="SELECT tblScouts.lngS_ID, tblPopcornOrders.[5WayTinCount], "
sql=sql & "tblPopcornOrders.[5WayTinCost], tblPopcornOrders.[3WayTinCount], "
sql=sql & "tblPopcornOrders.[3WayTinCost], tblPopcornOrders.[30PackCount], "
sql=sql & "tblPopcornOrders.[30PackCost], tblPopcornOrders.ChocCarmelCount, "
sql=sql & "tblPopcornOrders.ChocCarmelCost, tblPopcornOrders.[28OzCarmelCount], "
sql=sql & "tblPopcornOrders.[28OzCarmelCost], tblPopcornOrders.[15PackCount], "
sql=sql & "tblPopcornOrders.[15PackCost], tblPopcornOrders.[15PackLightCount], "
sql=sql & "tblPopcornOrders.[15PackLightCost], tblPopcornOrders.[12OzCarmelCount], "
sql=sql & "tblPopcornOrders.[12OzCarmelCost], tblPopcornOrders.TotalDollarsSold "
sql=sql & "FROM tblScouts INNER JOIN tblPopcornOrders ON tblScouts.lngS_ID = tblPopcornOrders.ScoutID "
sql=sql & "WHERE tblScouts.lngS_ID= " & request.Form("Scout-No")
On Error Resume Next
set rs=Connupdate.execute (sql)
MyScoutID= request.Form("Scout-No") 'rs("lngS_ID")

'Load the form with the values based on the Scout-No selected
%>

<h2>Popcorn Sales Drive <font color="blue">Update Form</h2>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="80%" id="

EmptyTable">
  <tr>
    <td width="100%"> </td>
  </tr>
  <tr>
    <td width="100%">
    <p align="center">&nbsp;Enter or replace the current value with your new count.<br>
    You must Submit your changes for the update to occur.</font></td>
  </tr>
  <tr>
    <td width="100%">&nbsp;</td>
  </tr>
  <tr>
    <td width="100%">
    <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%"

id="RecordHeader">
      <tr>
        <td width="12%" align="center" bgcolor="#000000"><font color="#FFFFFF">5-Way Tin</font></td>
        <td width="12%" align="center" bgcolor="#FF9900"><font color="#FFFFFF">3-Way Tin</font></td>
        <td width="12%" align="center" bgcolor="#0033CC"><font color="#FFFFFF">30<br>Pack</font></td>
        <td width="12%" align="center" bgcolor="#800080"><font color="#FFFFFF">Choc Caramel Crunch</font></td>
        <td width="13%" align="center" bgcolor="#CC3300"><font color="#FFFFFF">28Oz Caramel Corn</font></td>
        <td width="13%" align="center" bgcolor="#008080"><font color="#FFFFFF">15-Pack<br>
          <font size="1">Unbelievable</font> Butter</font></td>
        <td width="13%" align="center" bgcolor="#3399FF"><font color="#FFFFFF">15-Pack Butter Light</font></td>
        <td width="13%" align="center" bgcolor="#996600"><font color="#FFFFFF">12 Oz Caramel Corn</font></td>
      </tr>
      <tr>
    <form name="SelectPopcorn" action="popcorn-update.asp" method="post">
        <td width="12%" align="center"><input type="text" name="5WayTinCount" Value="<%= rs("5WayTinCount") %>" size="5"

align="center"></td>
        <td width="12%" align="center"><input type="text" name="3WayTinCount" Value="<%= rs("3WayTinCount") %>" size="5"

align="center"></td>
        <td width="12%" align="center"><input type="text" name="30PackCount" Value="<%= rs("30PackCount") %>" size="5" align

="center"></td>
        <td width="12%" align="center"><input type="text" name="ChocCarmelCount" Value="<%= rs("ChocCarmelCount") %>" size="

5" align="center"></td>
        <td width="13%" align="center"><input type="text" name="28OzCarmelCount" Value="<%= rs("28OzCarmelCount") %>" size="

5" align="center"></td>
        <td width="13%" align="center"><input type="text" name="15PackCount" Value="<%= rs("15PackCount") %>" size="5" align

="center"></td>
        <td width="13%" align="center"><input type="text" name="15PackLightCount" Value="<%= rs("15PackLightCount") %>" size

="5" align="center"></td>
        <td width="13%" align="center"><input type="text" name="12OzCarmelCount" align="center" Value="<%= rs("12

OzCarmelCount") %>" size="5"></td>
      </tr>
      <tr>
        <td width="106%" align="center" colspan="8"><br><input type="Submit" value="Submit Changes"><br>Click only once! Wait

for update verification.
</td><input type="hidden" name="Scout-No" value="<%=MyScoutID%>">
      </form></tr>

      </table></td>
  </tr>
  <tr>
    <td width="100%"><br>
    <p align="center">Your total sales are calculated for you,<br>
    please <font color="#FF0000">verify your records</font> after updating your
    order.<br>
    <font color="#0033CC">Click your <u>Refresh Button</u> if you don't see any
    changes!</font></td>
  </tr>
</table>
</body>
</html>


<< END CODE >>


==========================================
Here is the page that Updates the data....
==========================================


<< BEGIN CODE >>


<html>
<head><title>Pack 62 - Edit your Popcorn Order</title>
<script language="javascript">
function noRightClick() {
if (event.button==2) {
alert('Sorry, while viewing our website your Right Click action has been disabled.')
}
}
document.onmousedown=noRightClick
</script>
</head>
<body>

<%

' Open the database
Set connupdate = Server.CreateObject( "ADODB.Connection" )
connupdate.Open "Data Source=" & Server.Mappath("scouts.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

Set MyConn=Server.CreateObject("ADODB.Connection")

'QueryString to update popcorn orders by scout-no

sql="UPDATE tblPopcornOrders SET "
sql=sql & "5WayTinCount = '" & Replace(Request.Form("5WayTinCount"),"'","''") & "', "
sql=sql & "3WayTinCount = '" & Replace(Request.Form("3WayTinCount"),"'","''") & "', "
sql=sql & "30PackCount = '" &  Replace(Request.Form("30PackCount"),"'","''") & "', "
sql=sql & "ChocCarmelCount = '" &  Replace(Request.Form("ChocCarmelCount"),"'","''") & "', "
sql=sql & "28OzCarmelCount = '" &  Replace(Request.Form("28OzCarmelCount"),"'","''") & "', "
sql=sql & "15PackCount = '" &  Replace(Request.Form("15PackCount"),"'","''") & "', "
sql=sql & "15PackLightCount = '" &  Replace(Request.Form("15PackLightCount"),"'","''") & "', "
sql=sql & "12OzCarmelCount = '" &  Replace(Request.Form("12OzCarmelCount"),"'","''") & "' "
sql=sql & "WHERE tblPopcornOrders.ScoutID = " & request.Form("Scout-No")

connupdate.execute sql

sql=""

'QueryString to select specific order number to calculate total sales.

sql="SELECT tblScouts.lngS_ID, tblPopcornOrders.[5WayTinCount], "
sql=sql & "tblPopcornOrders.[5WayTinCost], tblPopcornOrders.[3WayTinCount], "
sql=sql & "tblPopcornOrders.[3WayTinCost], tblPopcornOrders.[30PackCount], "
sql=sql & "tblPopcornOrders.[30PackCost], tblPopcornOrders.ChocCarmelCount, "
sql=sql & "tblPopcornOrders.ChocCarmelCost, tblPopcornOrders.[28OzCarmelCount], "
sql=sql & "tblPopcornOrders.[28OzCarmelCost], tblPopcornOrders.[15PackCount], "
sql=sql & "tblPopcornOrders.[15PackCost], tblPopcornOrders.[15PackLightCount], "
sql=sql & "tblPopcornOrders.[15PackLightCost], tblPopcornOrders.[12OzCarmelCount], "
sql=sql & "tblPopcornOrders.[12OzCarmelCost], tblPopcornOrders.TotalDollarsSold "
sql=sql & "FROM tblScouts INNER JOIN tblPopcornOrders ON tblScouts.lngS_ID = tblPopcornOrders.ScoutID "
sql=sql & "WHERE tblScouts.lngS_ID= " & request.Form("Scout-No")

set rs=Connupdate.execute (sql)

MyScoutID= rs("lngS_ID")
' MyScoutID= request.Form("Scout-No")
My5waySales= rs("5WayTinCount") * rs("5WayTinCost")
My3waySales= rs("3WayTinCount") * rs("3WayTinCost")
My30PackSales= rs("30PackCount") * rs("30PackCost")
MyChocSales= rs("ChocCarmelCount") * rs("ChocCarmelCost")
My28OzSales= rs("28OzCarmelCount") * rs("28OzCarmelCost")
My15PackSales= rs("15PackCount") * rs("15PackCost")
My15LightSales= rs("15PackLightCount") * rs("15PackLightCost")
My12OzSales= rs("12OzCarmelCount") * rs("12OzCarmelCost")
MyTotalSales= My5waySales + My3waySales + My30PackSales + MyChocSales + My28OzSales
MyTotalSales= MyTotalSales + My15PackSales + My15LightSales + My12OzSales

If MyTotalSales < 50 Then
        MyTotalPrizeSales = 0
        MyPrizeLevel = "Not Eligable"
End IF

If MyTotalSales > 49 AND MyTotalSales < 100 Then
        MyTotalPrizeSales = 50
        MyPrizeLevel = "ONE"
End IF

If MyTotalSales > 99 AND MyTotalSales < 200 Then
        MyTotalPrizeSales = 100
        MyPrizeLevel = "TWO"
End If

If MyTotalSales > 199 AND MyTotalSales < 300 Then
        MyTotalPrizeSales = 200
        MyPrizeLevel = "THREE"
End If

If MyTotalSales > 299 AND MyTotalSales < 425 Then
        MyTotalPrizeSales = 300
        MyPrizeLevel = "FOUR"
End If

If MyTotalSales > 424 AND MyTotalSales < 550 Then
        MyTotalPrizeSales = 425
        MyPrizeLevel = "FIVE"
End If

If MyTotalSales > 449 AND MyTotalSales < 750 Then
        MyTotalPrizeSales = 550
        MyPrizeLevel = "SIX"
End If

If MyTotalSales > 749 AND MyTotalSales < 1000 Then
        MyTotalPrizeSales = 750
        MyPrizeLevel = "SEVEN"
End If

If MyTotalSales > 999 AND MyTotalSales < 1250 Then
        MyTotalPrizeSales = 1000
        MyPrizeLevel = "EIGHT"
End If

If MyTotalSales > 1249 AND MyTotalSales < 1500 Then
        MyTotalPrizeSales = 1250
        MyPrizeLevel = "NINE"
End If

If MyTotalSales > 1499 AND MyTotalSales < 2000 Then
        MyTotalPrizeSales = 1500
        MyPrizeLevel = "TEN"
End If

If MyTotalSales > 1999 AND MyTotalSales < 5000 Then
        MyTotalPrizeSales = 2000
        MyPrizeLevel = "ELEVEN"
End If

sql=""
' Reset all Prizes
MyPrize = " "
'QueryString to update popcorn orders sales total by scout-no

sql="UPDATE tblPopcornOrders SET "
sql=sql & "TotalDollarsSold = '" & MyTotalSales & "', "
sql=sql & "TotalPrizeSales = '" & MyTotalPrizeSales & "', "
sql=sql & "PrizeSale = '" & MyTotalPrizeSales & "', "
sql=sql & "PrizeLevel = '" & MyPrizeLevel & "', "
sql=sql & "ScoutPrize = '" & MyPrize & "', "
sql=sql & "ScoutPrize2 = '" & MyPrize & "', "
sql=sql & "ScoutPrize3 = '" & MyPrize & "', "
sql=sql & "ScoutPrize4 = '" & MyPrize & "', "
sql=sql & "ScoutPrize5 = '" & MyPrize & "', "
sql=sql & "ScoutPrize6 = '" & MyPrize & "' "
sql=sql & "WHERE tblPopcornOrders.ScoutID = " & request.Form("Scout-No")

connupdate.execute sql

sql=""

connupdate.close

' after committing the changes, send the user back
' to their scout page

%>

</b>
</body>
</html>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Re-Direct to Scout Pages</title>
<SCRIPT LANGUAGE="JavaScript">
 
<!-- Begin
redirTime = "4000";
redirURL = "http://yourcompany.com/XYZ-FOLDER/scouts.asp?Scout-No=<%=MyScoutID%>";
function redirTimer() { self.setTimeout("self.location.href = redirURL;",redirTime); }
//  End -->
    </script>
</head>

<body onLoad="redirTimer()">
<div align="center"><img height="148" width="696" src="images/Title_bar.gif"></div>
<p>    <div align="center"><h2>Popcorn<br>
    Update Complete</h2><br><br>
    Please update your prizes.
    <br>
    Your will be Re-Directed back to your Scout page in a few seconds.<br>
    <br>
    You may also get there by clicking
    <a href="http://yourcompany.com/XYZ-FOLDER/scouts.asp?Scout-No=<%=MyScoutID%>"
        ONMOUSEOVER="window.status='Return to your scout page'; return true;"
        ONMOUSEOUT="window.status=''; return true;">here</a><br>
  <br>
  DO NOT USE YOUR BROWSERS BACK BUTTON<br>
  THIS WILL RE-SUBMIT YOUR ORDER AGAIN!<br>
    </div>
    <br><br>

</body>
</html>


<< END CODE >>

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close