Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Total in Repeating Region

Status
Not open for further replies.
skw8966

skw8966

Programmer
Apr 12, 2001
59
US
I have an asp connected to an MS Access database. The purpose is displaying quote information. The top section from Recordset1 contains the main quote data (Quote #, Name, Address, etc). The bottom section from Recordset2 contains the quote details (Item #, Description, Qty, Price). The bottom section is a repeating region to display all items relating to a particular quote.

I would like to get a grand total for all the items relating to this particular quote.

Is there an easy way?

This results page is generated from a search page. Everything currently works great except for the price total.

Below is the code for the page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Quote.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("QuoteNum") <> "") Then
Recordset1__MMColParam = Request.QueryString("QuoteNum")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Quote_STRING
Recordset1.Source = "SELECT * FROM tblMain WHERE QuoteNo = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2__MMColParam
Recordset2__MMColParam = "1"
If (Request.QueryString("QuoteNum") <> "") Then
Recordset2__MMColParam = Request.QueryString("QuoteNum")
End If
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Quote_STRING
Recordset2.Source = "SELECT * FROM tblsubMain WHERE fkQuote = " + Replace(Recordset2__MMColParam, "'", "''") + " ORDER BY pkauto ASC"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim Recordset2_total
Dim Recordset2_first
Dim Recordset2_last

' set the record count
Recordset2_total = Recordset2.RecordCount

' set the number of rows displayed on this page
If (Recordset2_numRows < 0) Then
Recordset2_numRows = Recordset2_total
Elseif (Recordset2_numRows = 0) Then
Recordset2_numRows = 1
End If

' set the first and last displayed record
Recordset2_first = 1
Recordset2_last = Recordset2_first + Recordset2_numRows - 1

' if we have the correct record count, check the other stats
If (Recordset2_total <> -1) Then
If (Recordset2_first > Recordset2_total) Then
Recordset2_first = Recordset2_total
End If
If (Recordset2_last > Recordset2_total) Then
Recordset2_last = Recordset2_total
End If
If (Recordset2_numRows > Recordset2_total) Then
Recordset2_numRows = Recordset2_total
End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (Recordset2_total = -1) Then

' count the total records by iterating through the recordset
Recordset2_total=0
While (Not Recordset2.EOF)
Recordset2_total = Recordset2_total + 1
Recordset2.MoveNext
Wend

' reset the cursor to the beginning
If (Recordset2.CursorType > 0) Then
Recordset2.MoveFirst
Else
Recordset2.Requery
End If

' set the number of rows displayed on this page
If (Recordset2_numRows < 0 Or Recordset2_numRows > Recordset2_total) Then
Recordset2_numRows = Recordset2_total
End If

' set the first and last displayed record
Recordset2_first = 1
Recordset2_last = Recordset2_first + Recordset2_numRows - 1

If (Recordset2_first > Recordset2_total) Then
Recordset2_first = Recordset2_total
End If
If (Recordset2_last > Recordset2_total) Then
Recordset2_last = Recordset2_total
End If

End If
%>

<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID

strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If

On Error Resume Next

If (nLCID > -1) Then
Session.LCID = nLCID
End If

If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If

If (nLCID > -1) Then
Session.LCID = oldLCID
End If

DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Quote Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
<table width="650" border="3" cellspacing="0" cellpadding="0">
<tr bgcolor="#FFFF99">
<td colspan="6">
<div align="center"><strong><font size="4">McPhersons Quote</font></strong></div></td>
</tr>
<tr>
<td width="73" bgcolor="#FFFFCC">Quote #</td>
<td width="245" colspan="3"><%=(Recordset1.Fields.Item("QuoteNo").Value)%></td>
<td width="72" bgcolor="#FFFFCC">Date</td>
<td width="246"><%= DoDateTime((Recordset1.Fields.Item("dtDate").Value), 1, 4105) %></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Account #</td>
<td colspan="5"><%=(Recordset1.Fields.Item("AccountNo").Value)%></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Name</td>
<td colspan="5"><%=(Recordset1.Fields.Item("Name").Value)%></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">Address</td>
<td colspan="5"><%=(Recordset1.Fields.Item("Address").Value)%></td>
</tr>
<tr>
<td bgcolor="#FFFFCC">City</td>
<td><%=(Recordset1.Fields.Item("City").Value)%></td>
<td bgcolor="#FFFFCC">State</td>
<td><%=(Recordset1.Fields.Item("State").Value)%></td>
<td bgcolor="#FFFFCC">Zip</td>
<td><%=(Recordset1.Fields.Item("Zipcode").Value)%></td>
</tr>
<tr>
<td bgcolor="#FFFFCC"><font color="#000000">Telephone</font></td>
<td colspan="3"><%=(Recordset1.Fields.Item("Phone").Value)%></td>
<td bgcolor="#FFFFCC">Fax</td>
<td><%=(Recordset1.Fields.Item("Fax").Value)%></td>
</tr>
<tr>
<td bgcolor="#FFFFCC"><font color="#000000">Quoted to</font></td>
<td colspan="3"><%=(Recordset1.Fields.Item("QuotedTo").Value)%></td>
<td bgcolor="#FFFFCC">Quoted by</td>
<td><%=(Recordset1.Fields.Item("QuotedBy").Value)%></td>
</tr>
</table>
<p>&nbsp;</p>

<table width="718" height="84" border="3" cellpadding="0" cellspacing="0">
<tr bgcolor="#FFFF99">
<td colspan="5"> <div align="center">Details</div></td>
</tr>
<tr bgcolor="#FFFFCC">
<td width="57"> <div align="center">ID #</div></td>
<td width="115"> <div align="center">Item #</div></td>
<td width="322"> <div align="center">Description</div></td>
<td width="62"> <div align="center">Quantity</div></td>
<td width="78"> <div align="center">Price</div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset2.EOF))
%>
<tr>
<td height="27"><%=(Recordset2.Fields.Item("fkQuote").Value)%></td>
<td><%=(Recordset2.Fields.Item("ItemNo").Value)%></td>
<td><%=(Recordset2.Fields.Item("Product").Value)%></td>
<td><div align="center"><%=(Recordset2.Fields.Item("Quantity").Value)%></div></td>
<td><div align="right">
<div align="center">
<input name="Price" type="text" id="Price" value="<%=(FormatCurrency((Recordset2.Fields.Item("Price").Value), 2, -2, -2, -2))%>">
</div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset2.MoveNext()
Wend
%>
<tr>
<td height="27">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><div align="center">
<input name="sTotalValue" type="text" id="sTotalValue" value="$0.00">
</div></td>
</tr>
</table>
<p>&nbsp;</p>
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>


 
Sort by date Sort by votes
Try using this
Code: 
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Quote.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("QuoteNum") <> "") Then 
  Recordset1__MMColParam = Request.QueryString("QuoteNum")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Quote_STRING
Recordset1.Source = "SELECT * FROM tblMain WHERE QuoteNo = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2__MMColParam
Recordset2__MMColParam = "1"
If (Request.QueryString("QuoteNum") <> "") Then 
  Recordset2__MMColParam = Request.QueryString("QuoteNum")
End If
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Quote_STRING
Recordset2.Source = "SELECT * FROM tblsubMain WHERE fkQuote = " + Replace(Recordset2__MMColParam, "'", "''") + " ORDER BY pkauto ASC"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat1__numRows
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim Recordset2_total
Dim Recordset2_first
Dim Recordset2_last

' set the record count
Recordset2_total = Recordset2.RecordCount

' set the number of rows displayed on this page
If (Recordset2_numRows < 0) Then
  Recordset2_numRows = Recordset2_total
Elseif (Recordset2_numRows = 0) Then
  Recordset2_numRows = 1
End If

' set the first and last displayed record
Recordset2_first = 1
Recordset2_last  = Recordset2_first + Recordset2_numRows - 1

' if we have the correct record count, check the other stats
If (Recordset2_total <> -1) Then
  If (Recordset2_first > Recordset2_total) Then
    Recordset2_first = Recordset2_total
  End If
  If (Recordset2_last > Recordset2_total) Then
    Recordset2_last = Recordset2_total
  End If
  If (Recordset2_numRows > Recordset2_total) Then
    Recordset2_numRows = Recordset2_total
  End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (Recordset2_total = -1) Then

  ' count the total records by iterating through the recordset
  Recordset2_total=0
  While (Not Recordset2.EOF)
    Recordset2_total = Recordset2_total + 1
    Recordset2.MoveNext
  Wend

  ' reset the cursor to the beginning
  If (Recordset2.CursorType > 0) Then
    Recordset2.MoveFirst
  Else
    Recordset2.Requery
  End If

  ' set the number of rows displayed on this page
  If (Recordset2_numRows < 0 Or Recordset2_numRows > Recordset2_total) Then
    Recordset2_numRows = Recordset2_total
  End If

  ' set the first and last displayed record
  Recordset2_first = 1
  Recordset2_last = Recordset2_first + Recordset2_numRows - 1
  
  If (Recordset2_first > Recordset2_total) Then
    Recordset2_first = Recordset2_total
  End If
  If (Recordset2_last > Recordset2_total) Then
    Recordset2_last = Recordset2_total
  End If

End If
%>

<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>                    
function DoDateTime(str, nNamedFormat, nLCID)                
    dim strRet                                
    dim nOldLCID                                
                                        
    strRet = str                                
    If (nLCID > -1) Then                            
        oldLCID = Session.LCID                        
    End If                                    
                                        
    On Error Resume Next                            
                                        
    If (nLCID > -1) Then                            
        Session.LCID = nLCID                        
    End If                                    
                                        
    If ((nLCID < 0) Or (Session.LCID = nLCID)) Then                
        strRet = FormatDateTime(str, nNamedFormat)            
    End If                                    
                                        
    If (nLCID > -1) Then                            
        Session.LCID = oldLCID                        
    End If                                    
                                        
    DoDateTime = strRet                            
End Function                                    
</SCRIPT>                                    
<html>
<head>
<title>Quote Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
  <table width="650" border="3" cellspacing="0" cellpadding="0">
    <tr bgcolor="#FFFF99"> 
      <td colspan="6"> 
        <div align="center"><strong><font size="4">McPhersons Quote</font></strong></div></td>
    </tr>
    <tr> 
      <td width="73" bgcolor="#FFFFCC">Quote #</td>
      <td width="245" colspan="3"><%=(Recordset1.Fields.Item("QuoteNo").Value)%></td>
      <td width="72" bgcolor="#FFFFCC">Date</td>
      <td width="246"><%= DoDateTime((Recordset1.Fields.Item("dtDate").Value), 1, 4105) %></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC">Account #</td>
      <td colspan="5"><%=(Recordset1.Fields.Item("AccountNo").Value)%></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC">Name</td>
      <td colspan="5"><%=(Recordset1.Fields.Item("Name").Value)%></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC">Address</td>
      <td colspan="5"><%=(Recordset1.Fields.Item("Address").Value)%></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC">City</td>
      <td><%=(Recordset1.Fields.Item("City").Value)%></td>
      <td bgcolor="#FFFFCC">State</td>
      <td><%=(Recordset1.Fields.Item("State").Value)%></td>
      <td bgcolor="#FFFFCC">Zip</td>
      <td><%=(Recordset1.Fields.Item("Zipcode").Value)%></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC"><font color="#000000">Telephone</font></td>
      <td colspan="3"><%=(Recordset1.Fields.Item("Phone").Value)%></td>
      <td bgcolor="#FFFFCC">Fax</td>
      <td><%=(Recordset1.Fields.Item("Fax").Value)%></td>
    </tr>
    <tr> 
      <td bgcolor="#FFFFCC"><font color="#000000">Quoted to</font></td>
      <td colspan="3"><%=(Recordset1.Fields.Item("QuotedTo").Value)%></td>
      <td bgcolor="#FFFFCC">Quoted by</td>
      <td><%=(Recordset1.Fields.Item("QuotedBy").Value)%></td>
    </tr>
  </table>
  <p>&nbsp;</p>
  
  <table width="718" height="84" border="3" cellpadding="0" cellspacing="0">
    <tr bgcolor="#FFFF99"> 
      <td colspan="5"> <div align="center">Details</div></td>
    </tr>
    <tr bgcolor="#FFFFCC"> 
      <td width="57"> <div align="center">ID #</div></td>
      <td width="115"> <div align="center">Item #</div></td>
      <td width="322"> <div align="center">Description</div></td>
      <td width="62"> <div align="center">Quantity</div></td>
      <td width="78"> <div align="center">Price</div></td>
    </tr>

<%
dim mtTotal
myTotal = 0 
%>
<% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset2.EOF)) 
%>
<% 
dim myPrice
myPrice = (FormatCurrency((Recordset2.Fields.Item("Price").Value), 2, -2, -2, -2)) 
%>
    <tr> 
      <td height="27"><%=(Recordset2.Fields.Item("fkQuote").Value)%></td>
      <td><%=(Recordset2.Fields.Item("ItemNo").Value)%></td>
      <td><%=(Recordset2.Fields.Item("Product").Value)%></td>
      <td><div align="center"><%=(Recordset2.Fields.Item("Quantity").Value)%></div></td>
      <td><div align="right"> 
        <div align="center">
          <input name="Price" type="text" id="Price" value="<%=myPrice%>">
        </div></td>
    </tr>
<% myTotal = myTotal + myPrice 
    <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset2.MoveNext()
Wend
%>
    <tr> 
      <td height="27">&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><div align="center">
          <input name="sTotalValue" type="text" id="sTotalValue" value="$<%=myPrice%>">
</div></td>
    </tr>
  </table>
  <p>&nbsp;</p>
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>

You will see that I have added variables called myPrice and myTotal which are then just used to add up and display back the total. Have a play you will see how easy it works together.

Cheech

[Peace][Pipe]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
543
mdvp
mdvp
ccorry
  • Locked
  • Question
FrameMaker 2015 hyperlinks in book files don't work
Replies
0
Views
212
ccorry
ccorry
Mike Lewis
  • Locked
  • Question
Using Dreamweaver to maintain a site not created in Dreamweaver
Replies
2
Views
741
Mike Lewis
Mike Lewis
KDFischer
  • Locked
  • Question
Getting past Invalid cell in spreadsheet
Replies
0
Views
220
KDFischer
KDFischer
ElizaShteto
  • Locked
  • Question
Split One Page in two separate Pages in Framemaker 11
Replies
0
Views
194
ElizaShteto
ElizaShteto

Part and Inventory Search

Sponsor

Back
Top