Access 2007 Visual Basic Running Total and Percent of Total....

[Nikolai]

Hello all,

I am running an Access 2007 database where I have some behind the scenes
code that calculates a running total and a percent of total for a group of
records. This database was initially created in Access 2003, and I am just
trying to get it to work properly in 2007. The output of the running total
and percent of total are not correct when they are populated in the table;
however, they are correct in the 2003 version. Please see my below code and
advise of any change I should make. Thank you all in advance for any help
you can provide. Below is the specific code that does this function:

'Loop through records to get total quantity
rsdetail.MoveFirst
nTotal = 0
Do Until rsdetail.EOF
nTotal = nTotal + rsdetail.Fields("Fee Quantity").Value
rsdetail.MoveNext
Loop

'Loop through records to calculate running total and pct of total and
add to Account Scope Table
nRTotal = 0
nPct = 0
rsdetail.MoveFirst
Do Until rsdetail.EOF
nRTotal = nRTotal + rsdetail.Fields("Fee Quantity").Value
nPct = nRTotal / nTotal
rsScope.AddNew
rsScope.Fields("Operator ID").Value = rsdetail.Fields("Operator
ID").Value
rsScope.Fields("Total Volume").Value = rsdetail.Fields("Fee
Quantity").Value
rsScope.Fields("Quantity Running Total").Value = nRTotal
rsScope.Fields("Percent of Total").Value = nPct
rsScope.Update
rsdetail.MoveNext
Loop

The below is all of my code:

Option Compare Database
Option Explicit

Function CurrentDBConnectionString()
Dim db As Database

Set db = CurrentDb

CurrentDBConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist
Security Info=False;Data Source=" + db.Name

db.Close

End Function

Function AddRecordsToTblOperatorIDScopeSelection()
Dim conn As ADODB.Connection
Dim rsdetail As New ADODB.Recordset
Dim rsScope As New ADODB.Recordset
Dim sql As String
Dim nRTotal As Double
Dim nTotal As Double
Dim nPct As Double

'Set Mouse Pointer
Screen.MousePointer = 11 'Hourglass

'Open connection to DB
sql = CurrentDBConnectionString
Set conn = New ADODB.Connection
conn.Open sql

'Open list of records and place to put them
sql = "Select * from [tbl_Operator_ID_Scope_Selection]"
rsScope.Open sql, conn, adOpenKeyset, adLockOptimistic


sql = "SELECT * from [qry_Operator_ID_Scope]"
rsdetail.Open sql, conn, adOpenKeyset, adLockReadOnly



'Loop through records to get total quantity
rsdetail.MoveFirst
nTotal = 0
Do Until rsdetail.EOF
nTotal = nTotal + rsdetail.Fields("Fee Quantity").Value
rsdetail.MoveNext
Loop

'Loop through records to calculate running total and pct of total and
add to Account Scope Table
nRTotal = 0
nPct = 0
rsdetail.MoveFirst
Do Until rsdetail.EOF
nRTotal = nRTotal + rsdetail.Fields("Fee Quantity").Value
nPct = nRTotal / nTotal
rsScope.AddNew
rsScope.Fields("Operator ID").Value = rsdetail.Fields("Operator
ID").Value
rsScope.Fields("Total Volume").Value = rsdetail.Fields("Fee
Quantity").Value
rsScope.Fields("Quantity Running Total").Value = nRTotal
rsScope.Fields("Percent of Total").Value = nPct
rsScope.Update
rsdetail.MoveNext
Loop


'Cleanup
rsdetail.Close
rsScope.Close
conn.Close
Set rsdetail = Nothing
Set rsScope = Nothing
Set conn = Nothing

Screen.MousePointer = 0 'Default
MsgBox "Trade volume data added to the operator ID scope table."

End Function