VB Total and Percent Errors

N

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
 
C

Clifford Bass

Hi Nikolai,

Could you post a short set of the data that goes into the total and the
results you get from Access 2003 and from 2007?

Clifford Bass

Nikolai said:
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
Click to expand...
[snip]
 
D

David W. Fenton

nTotal = nTotal + rsdetail.Fields("Fee Quantity").Value
Click to expand...

This is not an answer to your question, but why are you so verbose
with this? The following is exactly the same thing:

nTotal = nTotal + rsdetail![Fee Quantity]

And if you didn't have a space in the field name,

nTotal = nTotal + rsdetail!FeeQuantity

I always use "Camel Case" instead of spaces, myself, as it makes it
just as readable, but much easier to use in code and SQL. If you are
worrying about labels and such, you can set the caption property in
your table to be the field name with user-friendly spaces.
 
D

David W. Fenton

Below is the specific code that does this function:
Click to expand...

I didn't read through all the code, but I wonder why you are using
ADO. Is the data stored in a Jet back end (MDB)? If so, ADO is going
to work better, and be nearly identical to the code you've already
written.

Also, why do you choose to loop through the data and add records one
at a time, instead of writing SQL to summarize the data for you and
appending the records in one operation?
 
N

Nikolai

Sorry for the delay, but I don't work on the weekends. Here you go and thank
you again.

Access 2003 output (Correct):

Operator ID Total Volume Quantity Running Total Percent of Total
F61 14,784,942 14,784,942 26.42%
KCAGARDNER 6,896,216 21,681,158 38.74%
D24 3,482,901 25,164,059 44.96%
G68 2,681,790 27,845,849 49.75%
E16 2,127,255 29,973,104 53.55%
XHJTAUBER 1,071,238 31,044,342 55.47%
D5B 1,032,921 32,077,263 57.31%

Access 2007 output (Wrong):
Operator ID Total Volume Quantity Running Total Percent of Total
F61 14,784,942 36,199,979 64.68%
KCAGARDNER 6,896,216 49,765,397 88.91%
D24 3,482,901 12,397,725 22.15%
G68 2,681,790 39,601,794 70.76%
E16 2,127,255 17,443,870 31.17%
XHJTAUBER 1,071,238 54,251,145 96.93%
D5B 1,032,921 13,702,186 24.48%



Clifford Bass said:
Hi Nikolai,

Could you post a short set of the data that goes into the total and the
results you get from Access 2003 and from 2007?

Clifford Bass

Nikolai said:
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
Click to expand...
[snip]
Click to expand...
 
N

Nikolai

ADO is being used in both versions of the database. The difference is the
switch in connection string. All I changed between the two was the
connection string and left the rest of the code the same. The purpose of the
loop is to simply total up the volume for the group of records and store that
single value to come up with the percent of that total. I have pasted my
output below along with the connection strings.

For the 2007 I'm using the following as a connection string:
"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="

Access 2007 output (Wrong):
Operator ID Total Volume Quantity Running Total Percent of Total
F61 14,784,942 36,199,979 64.68%
KCAGARDNER 6,896,216 49,765,397 88.91%
D24 3,482,901 12,397,725 22.15%
G68 2,681,790 39,601,794 70.76%
E16 2,127,255 17,443,870 31.17%
XHJTAUBER 1,071,238 54,251,145 96.93%
D5B 1,032,921 13,702,186 24.48%

In the 2003, I'm suing the following as a connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source="

Operator ID Total Volume Quantity Running Total Percent of Total
F61 14,784,942 14,784,942 26.42%
KCAGARDNER 6,896,216 21,681,158 38.74%
D24 3,482,901 25,164,059 44.96%
G68 2,681,790 27,845,849 49.75%
E16 2,127,255 29,973,104 53.55%
XHJTAUBER 1,071,238 31,044,342 55.47%
D5B 1,032,921 32,077,263 57.31%
 
N

Nikolai

Here are my connection strings in case this is needed:

For the 2007 I'm using the following as a connection string:
"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source="

In the 2003, I'm suing the following as a connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source="

All I did between the two databases is change the connection strings. The
rest of the code was left the same. Thanks again.

Clifford Bass said:
Hi Nikolai,

Could you post a short set of the data that goes into the total and the
results you get from Access 2003 and from 2007?

Clifford Bass

Nikolai said:
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
Click to expand...
[snip]
Click to expand...
 
C

Clifford Bass

Hi Nikolai,

Curious. It works fine for me in Access 2007. Except for the
percents, which I am presuming is due to your not supplying the entire source
record set. I defined the Total Volume and Quantity Running Total columns as
double. How do you have them defined? Also, how is the Fee Quantity column
defined?

By the by, getting a connection to the current database can be
simplified to the single line:

Set conn = CurrentProject.Connection

And you can remove the conn.Close line since you do not need/want to
close the current connection.

Or, you can eliminate conn altogether and just use
CurrentProject.Connection directly.

If may be interesting to see if doing either of those would cure the
problem. Oh, and another thought. Check your references to make sure you
are using Microsoft ActiveX Data Objects 2.8 Library.

Clifford Bass
 
N

Nikolai

Thank you Cliff,

My quantity running total and percent still is not working. I have the
following references in this order for the 2007 DB:
Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library (Was set at 2.6 before. Thank
you.)
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office Web Components Wizards
Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library

For the 2003 DB, I have the following references:
Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.6 Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office XP Web Components
Microsoft Excel 12.0 Object Library
Microsoft Office 12.0 Object Library

I did go ahead and define Total Volume, Quantity Running Total, and Fee
Quantity as Double. Before they were Long Integer. I will go ahead and
change the connection stuff like you said to see if that will make a
difference, but I have a feeling it won't. Like I said, I simply took the
2003 Access Database and moved its objects to a 2007 version to try and make
it work. Another problem is it has been many years since I've done any VB
coding, and this code is a coworker's logic from another database he created.
I simply adjusted his code to work for my database. So, coding really is
not my specialty. I thank you again for your assistance on this. Let me
know if you think of anything else.

Nikolai
 
D

David W. Fenton

I wonder why you are using
ADO. Is the data stored in a Jet back end (MDB)? If so, ADO is
going to work better, and be nearly identical to the code you've
already written.
Click to expand...

that should read:
If so, *DAO* is going
to work better, and be nearly identical to the code you've already
written.
Click to expand...

I wish MS wouldn't create technologie abbreviations that are just
reshufflings of the letters in other abbreviations!
 
D

David W. Fenton

ADO is being used in both versions of the database.
Click to expand...

Why? DAO is the native data interface for Jet data, and for Jet
itself. Jet is quite powerful in working with disparate data sources
and quite useful for that reason. ADO is a non-native interfaced
designed as a generic interface for all sorts of data. When you've
got Jet in the mix already, you really don't need ADO for too much
of anything, unless your situation is quite specific to something
that will benefit from ADO.

In other words:

1. In Access, use DAO by default.

2. Use ADO only when it will work better than DAO for the particular
operation you are performing.

My experience is that #2 very rarely happens. If it weren't for the
ADO UserRoster, I wouldn't have a single line of ADO code in any of
my deployed applications.
 
D

David W. Fenton

For the 2007 I'm using the following as a connection string:
"Provider=Microsoft.ACE.OLEDB.12.0;Persist Security
Info=False;Data Source="
[]

In the 2003, I'm suing the following as a connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data
Source="
Click to expand...

Maybe I missed something important, but what programming environment
are you working in? If you're programming in Access, then I don't
know what in the world you are trying to accomplish by using OLEDB
to access Jet data. It makes no sense whatsoever.
 
C

Clifford Bass

Hi Nikolai,

There must be something really obscure going on there. The only other
ideas I have are: 1) Before the code is run, do existing records get
deleted? That is, could the process not be writing out new records somehow
and you are possibly looking at old data? 2) Place a break point in the code
just before the loop and step through it all line-by-line, inspecting the
computational results and what is getting saved out to the table.

Clifford Bass
 
N

Nikolai

Thank you again Cliff for all of your help. I am running delete queries
prior to the code running to ensure the records get deleted. There must be
something with the code that I'm not picking up on. The fact that it works
in one version of Access and not the other is quite annoying too. If you
would be willing to look at the database, I can e-mail it to you. Just let
me know. My e-mail is (e-mail address removed). Thank you again.

Nikolai
 
C

Clifford Bass

Hi Nikolai,

The following statement needs to be modified because the query is a
summary query that groups, and therefore sorts, by the operator ID. SO

sql = "SELECT * from [qry_Operator_ID_Scope]"

should be

sql = "SELECT * from [qry_Operator_ID_Scope] order by [Fee Quantity] desc,
[Operator ID]"

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 Visual Basic Running Total and Percent of Total.... 0
AutoFilter: Loop Thru Visible Rows? 2
datarow and aggregate functions 1
Range Object being "forgotten" 1
Problem with VBA code trying to query and access database 2
Attn Sprinks - clarification on VB code 6
Attn Sprinks- Not duplicate insert records 1

Top