Dsum adds the same record twice

[Guest]

I am trying to show a total for a group of records on a form. The form is
for data entry into my BIN table. Each BIN record in the table has a load
assigned. Each bin record has a total weight field stored in the BIN table.

What I want to know is the total load weight (the sum of the total weight
field for each record with that load number) each time I assign a load
number to a bin record. I have the following code segment in VB.

Private Sub Combo47_Click()
Refresh
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
End Sub


The above works, but Dsum is returning the wrong value. In my test senario
I have two bin records assigned to load "99" (combo47 will equal "99"). The
first record has a total weight of 500 and the second 450.

Dcount is correct and tells me 2 records via text84. Dsum tells me 1000 or
900 depending on which record is current. The curren record being added
twice.
This will not do! So what have I messed up?

 

[Graham Mandeno]

Hi Dancy

I'm surprised it returns anything at all, as Jet will have no idea what
[Combo47] is when it's processing the SQL.

You should include the *value* of the combobox, not its *name*. Try this:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])

If [LoadNumber] is a text field in your table, then you must enclose the
value in quotes:
... "[LoadNumber] = '" & [Combo47] & "'")

 

[Guest]

Graham, Thank you for responding;
your suggestions did not help because I forgot to mention that LoadNumber
is a long integer type variable.

these three lines produce identical results:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = 99")

DSum is being constrained some what as there are over 3000 bin records but
only two with load number 99. One with a total weight of 500 and the other
450. Dsum still reports a grand total of 1000 or 900 depending on which
record is current.

thank you!
Dan

Hi Dancy

I'm surprised it returns anything at all, as Jet will have no idea what
[Combo47] is when it's processing the SQL.

You should include the *value* of the combobox, not its *name*. Try this:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])

If [LoadNumber] is a text field in your table, then you must enclose the
value in quotes:
... "[LoadNumber] = '" & [Combo47] & "'")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to show a total for a group of records on a form. The form is
for data entry into my BIN table. Each BIN record in the table has a load
assigned. Each bin record has a total weight field stored in the BIN
table.

What I want to know is the total load weight (the sum of the total weight
field for each record with that load number) each time I assign a load
number to a bin record. I have the following code segment in VB.

Private Sub Combo47_Click()
Refresh
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
End Sub


The above works, but Dsum is returning the wrong value. In my test
senario
I have two bin records assigned to load "99" (combo47 will equal "99").
The
first record has a total weight of 500 and the second 450.

Dcount is correct and tells me 2 records via text84. Dsum tells me 1000
or
900 depending on which record is current. The curren record being added
twice.
This will not do! So what have I messed up?

 

[Graham Mandeno]

Hi again Dan,

I'm sorry - I've tried to reproduce this behaviour using a couple of
different tables, but I cannot.

Is it possible that your project has a function named DSum which is being
called in preference to the built-in one? Try calling Access.DSum instead,
just to check.

You could also try writing your own function, just to see if you get a
different result:

Public Function SumTotalWeight(lngLoadNumber as Long) as Long
' or "As Double" as required
Dim rs as DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset( _
"Select Sum([Total Weight]) from bin where [LoadNumber]=" _
& lngLoadNumber", dbOpenSnapshot)
If rs.RecordCount > 0 Then SumTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

Then, change your line of code to:

Text82 = SumTotalWeight( Combo47 )

Please report back - I'm interested to know the result.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thank you for responding;
your suggestions did not help because I forgot to mention that LoadNumber
is a long integer type variable.

these three lines produce identical results:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = 99")

DSum is being constrained some what as there are over 3000 bin records but
only two with load number 99. One with a total weight of 500 and the
other
450. Dsum still reports a grand total of 1000 or 900 depending on which
record is current.

thank you!
Dan

Hi Dancy

I'm surprised it returns anything at all, as Jet will have no idea what
[Combo47] is when it's processing the SQL.

You should include the *value* of the combobox, not its *name*. Try
this:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " &
[Combo47])

If [LoadNumber] is a text field in your table, then you must enclose the
value in quotes:
... "[LoadNumber] = '" & [Combo47] & "'")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am trying to show a total for a group of records on a form. The form
is
for data entry into my BIN table. Each BIN record in the table has a
load
assigned. Each bin record has a total weight field stored in the BIN
table.

What I want to know is the total load weight (the sum of the total
weight
field for each record with that load number) each time I assign a load
number to a bin record. I have the following code segment in VB.

Private Sub Combo47_Click()
Refresh
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
End Sub


The above works, but Dsum is returning the wrong value. In my test
senario
I have two bin records assigned to load "99" (combo47 will equal "99").
The
first record has a total weight of 500 and the second 450.

Dcount is correct and tells me 2 records via text84. Dsum tells me
1000
or
900 depending on which record is current. The curren record being
added
twice.
This will not do! So what have I messed up?

 

[Guest]

Graham, It's been awhile since I have had time to mess with this problem,
sorry for the lapse.
In response to your question, I have not created another Dsum function that
I know of. This is not a larger project and I am not much of a Vbasic
programmer! I did try changing the call to Access.DSum instead but same
results. I would like to try your function but I am getting a syntax error.
Access does not explane what is wrong. The debugger puts a yellow arrow at
the beginning of the words Public Function and the Set rs line is in red.

This is the ENTIRE block of code for the Bin form:

Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long
' or "As Double" as required
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset( _
"Select Sum([Total Weight]) from bin where [LoadNumber]=" _
& lngLoadNumber", dbOpenSnapshot)
If rs.RecordCount > 0 Then SumTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

Private Sub Combo43_Change()
If Combo43 = "brine" Then [Empty weight] = 1100
If Combo43 = "field" Then [Empty weight] = 59
End Sub

Private Sub Combo47_Click()
Refresh
'Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
'Text82 = Access.DSum("[Total weight]", "bin", "[LoadNumber] = 99")
Text82 = SumMyTotalWeight(Combo47)
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = 99")
'Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
End Sub

Private Sub Command55_Click()
Text20 = Date
End Sub

Private Sub Form_Current()
[Bin Number].SetFocus
End Sub

Hi again Dan,

I'm sorry - I've tried to reproduce this behaviour using a couple of
different tables, but I cannot.

Is it possible that your project has a function named DSum which is being
called in preference to the built-in one? Try calling Access.DSum instead,
just to check.

You could also try writing your own function, just to see if you get a
different result:

Public Function SumTotalWeight(lngLoadNumber as Long) as Long
' or "As Double" as required
Dim rs as DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset( _
"Select Sum([Total Weight]) from bin where [LoadNumber]=" _
& lngLoadNumber", dbOpenSnapshot)
If rs.RecordCount > 0 Then SumTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

Then, change your line of code to:

Text82 = SumTotalWeight( Combo47 )

Please report back - I'm interested to know the result.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thank you for responding;
your suggestions did not help because I forgot to mention that LoadNumber
is a long integer type variable.

these three lines produce identical results:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = 99")

DSum is being constrained some what as there are over 3000 bin records but
only two with load number 99. One with a total weight of 500 and the
other
450. Dsum still reports a grand total of 1000 or 900 depending on which
record is current.

thank you!
Dan

Hi Dancy

I'm surprised it returns anything at all, as Jet will have no idea what
[Combo47] is when it's processing the SQL.

You should include the *value* of the combobox, not its *name*. Try
this:

Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " &
[Combo47])

If [LoadNumber] is a text field in your table, then you must enclose the
value in quotes:
... "[LoadNumber] = '" & [Combo47] & "'")
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am trying to show a total for a group of records on a form. The form
is
for data entry into my BIN table. Each BIN record in the table has a
load
assigned. Each bin record has a total weight field stored in the BIN
table.

What I want to know is the total load weight (the sum of the total
weight
field for each record with that load number) each time I assign a load
number to a bin record. I have the following code segment in VB.

Private Sub Combo47_Click()
Refresh
Text82 = DSum("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = [Combo47]")
End Sub


The above works, but Dsum is returning the wrong value. In my test
senario
I have two bin records assigned to load "99" (combo47 will equal "99").
The
first record has a total weight of 500 and the second 450.

Dcount is correct and tells me 2 records via text84. Dsum tells me
1000
or
900 depending on which record is current. The curren record being
added
twice.
This will not do! So what have I messed up?

 

[Dirk Goldgar]

Graham, It's been awhile since I have had time to mess with this
problem, sorry for the lapse.
In response to your question, I have not created another Dsum
function that I know of. This is not a larger project and I am not
much of a Vbasic programmer! I did try changing the call to
Access.DSum instead but same results. I would like to try your
function but I am getting a syntax error. Access does not explane
what is wrong. The debugger puts a yellow arrow at the beginning of
the words Public Function and the Set rs line is in red.

This is the ENTIRE block of code for the Bin form:

Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long
' or "As Double" as required
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset( _
"Select Sum([Total Weight]) from bin where [LoadNumber]=" _
& lngLoadNumber", dbOpenSnapshot)
If rs.RecordCount > 0 Then SumTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

[snip]

PMFJI. I don't know how long it's been since you and Graham last
exchanged messages, so in case it's been long enough that he may not
notice this message, I'll point out the problem the VBA compiler is
complaining about. In this line of code:

& lngLoadNumber", dbOpenSnapshot)

there's an unwanted quote character. It should be:

& lngLoadNumber, dbOpenSnapshot)

 

[Guest]

Thank you Dirk;
I took out the extra quote and now I am getting a diffrent error:
Run-time error "3061":
too few parameters: expected 1

Graham, It's been awhile since I have had time to mess with this
problem, sorry for the lapse.
In response to your question, I have not created another Dsum
function that I know of. This is not a larger project and I am not
much of a Vbasic programmer! I did try changing the call to
Access.DSum instead but same results. I would like to try your
function but I am getting a syntax error. Access does not explane
what is wrong. The debugger puts a yellow arrow at the beginning of
the words Public Function and the Set rs line is in red.

This is the ENTIRE block of code for the Bin form:

Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long
' or "As Double" as required
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset( _
"Select Sum([Total Weight]) from bin where [LoadNumber]=" _
& lngLoadNumber", dbOpenSnapshot)
If rs.RecordCount > 0 Then SumTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

[snip]

PMFJI. I don't know how long it's been since you and Graham last
exchanged messages, so in case it's been long enough that he may not
notice this message, I'll point out the problem the VBA compiler is
complaining about. In this line of code:

& lngLoadNumber", dbOpenSnapshot)

there's an unwanted quote character. It should be:

& lngLoadNumber, dbOpenSnapshot)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you Dirk;
I took out the extra quote and now I am getting a diffrent error:
Run-time error "3061":
too few parameters: expected 1

You'll get that error when the query contains a field name or word that
isn't in the table -- the database engine concludes that the name must
be a parameter that you failed to supply a value for. Check to see that
the table "bin" actually contains fields named (and spelled exactly
like) "Total Weight" and "LoadNumber", which are used in your SQL
statement. If there's a misspelling -- quite likely either a missing
space or an extra space -- fix the SQL statement to correctly name the
field.

 

[Guest]

you were right Dirk, I had Total Weight instead of Totalweight. Total Weight
is the form field name not the table field name. So fixed that and the pesky
compiler just moved on to the next error "run time error 13. Type mismatch."
I have read several questions about this error and the common response is to
make sure that the DAO prefix is used in the dim statement:
Dim rs As DAO.Recordset
I have that done. the error persists. here is the current set statement:
Set rs = DBEngine(0)(0).OpenRecordset("Select Sum([Totalweight]) from bin
where [LoadNumber]=" & lngLoadNumber, dbOpenSnapshot)

 

[Dirk Goldgar]

you were right Dirk, I had Total Weight instead of Totalweight. Total
Weight is the form field name not the table field name. So fixed that
and the pesky compiler just moved on to the next error "run time
error 13. Type mismatch." I have read several questions about this
error and the common response is to make sure that the DAO prefix is
used in the dim statement:
Dim rs As DAO.Recordset
I have that done. the error persists. here is the current set
statement: Set rs = DBEngine(0)(0).OpenRecordset("Select
Sum([Totalweight]) from bin where [LoadNumber]=" & lngLoadNumber,
dbOpenSnapshot)

Are you sure the error is being raised by the "Set rs = " statement?
Was that the line that was highlighted in the debugger when the error
was raised? I don't see anything there that I'd expect to give that
error. The only thing that might be wrong is if the field LoadNumber is
not a numeric field, so you should check that -- but I don't think that
would cause the exact error you report.

I now see another probably error in your code. You posted this for the
function header:

Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long

and this line inside it:

If rs.RecordCount > 0 Then SumTotalWeight = rs(0)

I believe that should be:

If rs.RecordCount > 0 Then SumMyTotalWeight = rs(0)

However, though you should fix that if you haven't already, I still
don't think that would give the error you report.

Could the error be at the point where you call the function? The
function expects to be passed a Long value, or a value that can be
converted to a Long. What is being passed to it? I see this line from
your posted code:

Text82 = SumMyTotalWeight(Combo47)

Could it be that Combo47 has a text value, or some other non-numeric
value?

 

[Guest]

Yes Dirk, the set rs = DBengine(0)(0)...... statement is in yellow and has a
small arrow on the left. If I hover over lngLoadNumber the debugger tells me
it is 99 (correct).
Totalweight is defined in the table as an integer and LoadNumber is a long
integer.

I tried to simplify the statement to help identify where the error is
comming from by changing it to the following (I hope the syntax is correct)
Set rs = DBEngine(0)(0).OpenRecordset("Select count(*) from bin",
dbOpenSnapshot)

this line gives the same run-time error 13, Type mismatch.

PS I had previously changed Text82 = SumMyTotalWeight(Combo47).

 

[Guest]

Dirk, I have made it work. I found that if I changed the references it would
work. I changed from "Microsoft DAO 2.5 Object library" to "Microsoft DAO 3.6
Object library" the error goes away and I get the correct total weight
returned. So the new function "SumMyTotalWeight" demonstrates the problem I
am having with Dsum. So now we can go back to working on the original problem
of Dsum returning the incorrect amount.
text82 = 950 (correct)
text84 = 2 (correct)
text86 = 1000 (incorrect!) (has added the current recort twice)

Here is the current complete block of code. what do you think?



Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long

Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("Select Sum([Totalweight]) from bin
where [LoadNumber]=" & lngLoadNumber, dbOpenSnapshot)
If rs.RecordCount > 0 Then SumMyTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

Private Sub Combo43_Change()
If Combo43 = "brine" Then [Empty weight] = 1100
If Combo43 = "field" Then [Empty weight] = 59
End Sub

Private Sub Combo47_Click()
Refresh
Text82 = SumMyTotalWeight(Combo47)
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
Text86 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])
End Sub

Private Sub Command55_Click()
Text20 = Date
End Sub

Private Sub Form_Current()
[Bin Number].SetFocus
End Sub

 

[Dirk Goldgar]

Dirk, I have made it work. I found that if I changed the references
it would work. I changed from "Microsoft DAO 2.5 Object library" to
"Microsoft DAO 3.6 Object library" the error goes away and I get the
correct total weight returned.

Well no wonder! I'm glad you found that, because it would never have
occurred to me that you might be referencing DAO 2.5.

So the new function
"SumMyTotalWeight" demonstrates the problem I am having with Dsum. So
now we can go back to working on the original problem of Dsum
returning the incorrect amount.
text82 = 950 (correct)
text84 = 2 (correct)
text86 = 1000 (incorrect!) (has added the current recort twice)

Here is the current complete block of code. what do you think?

[snipped]

I think I'm mystified, that's what I think. If you'd like to send me a
cut-down copy of your database, containing *only* the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message, or you can get my e-mail address
from my website.

 

[Guest]

Forwarded stripped down version of DB to '(e-mail address removed)' atten Dirk

 

[Dirk Goldgar]

Forwarded stripped down version of DB to [snipped] atten
Dirk

Dan - In future, please respect my desire *NOT* to have my e-mail
address posted in the newsgroups, where spammers and viruses can harvest
it and target me. That's why I use the NO SPAM tactic, and don't ever
post using my real e-mail address.

 

[Guest]

Sorry Dirk, I did not know that could happen.

Forwarded stripped down version of DB to [snipped] atten
Dirk

Dan - In future, please respect my desire *NOT* to have my e-mail
address posted in the newsgroups, where spammers and viruses can harvest
it and target me. That's why I use the NO SPAM tactic, and don't ever
post using my real e-mail address.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, I have made it work. I found that if I changed the references
it would work. I changed from "Microsoft DAO 2.5 Object library" to
"Microsoft DAO 3.6 Object library" the error goes away and I get the
correct total weight returned. So the new function
"SumMyTotalWeight" demonstrates the problem I am having with Dsum. So
now we can go back to working on the original problem of Dsum
returning the incorrect amount.
text82 = 950 (correct)
text84 = 2 (correct)
text86 = 1000 (incorrect!) (has added the current recort twice)

Here is the current complete block of code. what do you think?



Public Function SumMyTotalWeight(lngLoadNumber As Long) As Long

Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("Select Sum([Totalweight]) from
bin where [LoadNumber]=" & lngLoadNumber, dbOpenSnapshot)
If rs.RecordCount > 0 Then SumMyTotalWeight = rs(0)
rs.Close
Set rs = Nothing
End Function

Private Sub Combo43_Change()
If Combo43 = "brine" Then [Empty weight] = 1100
If Combo43 = "field" Then [Empty weight] = 59
End Sub

Private Sub Combo47_Click()
Refresh
Text82 = SumMyTotalWeight(Combo47)
Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " &
[Combo47]) Text86 = DSum("[Total weight]", "bin", "[LoadNumber] =
" & [Combo47])
End Sub

Private Sub Command55_Click()
Text20 = Date
End Sub

Private Sub Form_Current()
[Bin Number].SetFocus
End Sub

I've looked at your database, and here is the answer. Your DSum
expression isn't working because there is no field in your table named
"Total weight". The actual field is named "Totalweight". The DSum is
actually failing, and I'm not sure why no error message is displayed.
However, if you change this line:

Text86 = DSum("[Total weight]", "bin", "[LoadNumber] = " & [Combo47])

to this:

Text86 = DSum("Totalweight", "bin", "LoadNumber = " & [Combo47])

it should work. It works for me. You should also change your line:

Text84 = DCount("[Total weight]", "bin", "[LoadNumber] = " &

[Combo47])

to:

Text84 = DCount("Totalweight", "bin", "LoadNumber = " & [Combo47])