Sum function?

[Joan]

Could anyone tell me whether or not the SUM() function is available to use
in VBA? As part if an IIF statement , I would like one of the arguments to
sum the values in a field on a subform. Below is the line of code that I am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How do I
sum ExtendedPrice in my code?

Joan

 

[Kelvin]

The SUM() function only works on the table/query of the form, not the text
boxes. If you have a field called intQnty and a text box called txtQnty,
SUM(intQnty) will give the total of the intQnty field in the table/query.
SUM(txtQnty) will generate an error. If you want to calculate the sum of a
calculated field, you need to sum the original fields.

Kelvin

 

[Marshall Barton]

Could anyone tell me whether or not the SUM() function is available to use
in VBA? As part if an IIF statement , I would like one of the arguments to
sum the values in a field on a subform. Below is the line of code that I am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How do I
sum ExtendedPrice in my code?

The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Joan]

Marshall,
I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Joan

Could anyone tell me whether or not the SUM() function is available to use
in VBA? As part if an IIF statement , I would like one of the arguments to
sum the values in a field on a subform. Below is the line of code that I am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How do I
sum ExtendedPrice in my code?

The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Marshall Barton]

Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubform].[Form].[ExtendedPriceTotal])
--
Marsh
MVP [MS Access]

I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Could anyone tell me whether or not the SUM() function is available to use
in VBA? As part if an IIF statement , I would like one of the arguments to
sum the values in a field on a subform. Below is the line of code that I am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How do I
sum ExtendedPrice in my code?

The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Joan]

Marshall,

I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum and the
InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:
=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePric
e) +
IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsSub
Edit.Form!ExtendedPriceSum).

Now a problem happens when the user attempts to add a new record to the
subform by putting a value in the control, cboChargeCode. When this is
attempted, the user gets the message: "Field cannot be updated", even
though the subform does allow the record to be added. How can I keep this
message from appearing?? This has only started happening since I put the
line:
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being used by
two processes simultaneously. What do you think?

Joan

Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubfor
m].[Form].[ExtendedPriceTotal])
--
Marsh
MVP [MS Access]

I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is available to use
in VBA? As part if an IIF statement , I would like one of the

arguments
to

sum the values in a field on a subform. Below is the line of code

that I
am

trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How

do
I

sum ExtendedPrice in my code?

The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Marshall Barton]

Sorry, Joan, I've never tried to do it that way and have
never seen that issue before, so I have no ideas about this
latest situation.

Why do you want to display the total of the subform in both
the main form and sub form? I've always wanted it just in
the main form and make the subform's footer invisible so I
don't care if it doesn't display 0 when there's no data to
sum.
--
Marsh
MVP [MS Access]

I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum and the
InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:
=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePric
e) +
IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsSub
Edit.Form!ExtendedPriceSum).

Now a problem happens when the user attempts to add a new record to the
subform by putting a value in the control, cboChargeCode. When this is
attempted, the user gets the message: "Field cannot be updated", even
though the subform does allow the record to be added. How can I keep this
message from appearing?? This has only started happening since I put the
line:
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being used by
two processes simultaneously. What do you think?

Joan

Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubfor
m].[Form].[ExtendedPriceTotal])

I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is available to
use
in VBA? As part if an IIF statement , I would like one of the arguments
to
sum the values in a field on a subform. Below is the line of code that I
am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug. How do
I
sum ExtendedPrice in my code?


:
The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Joan]

Marshall,
I want to display the total of the subform for the user's benefit so
that they know what the miscellaneous other charges amount to. Am not
understanding why this would be a problem.

Where did you intend for the following line to be used?

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubfo r

m].[Form].[ExtendedPriceTotal])

Joan

Sorry, Joan, I've never tried to do it that way and have
never seen that issue before, so I have no ideas about this
latest situation.

Why do you want to display the total of the subform in both
the main form and sub form? I've always wanted it just in
the main form and make the subform's footer invisible so I
don't care if it doesn't display 0 when there's no data to
sum.
--
Marsh
MVP [MS Access]

I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Reco

r

dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum and the
InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:
=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePri

c

e) +
IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsSu

b

Edit.Form!ExtendedPriceSum).

Now a problem happens when the user attempts to add a new record to the
subform by putting a value in the control, cboChargeCode. When this is
attempted, the user gets the message: "Field cannot be updated", even
though the subform does allow the record to be added. How can I keep this
message from appearing?? This has only started happening since I put the
line:
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Reco

r

dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being used by
two processes simultaneously. What do you think?

Joan

Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubfo r

m].[Form].[ExtendedPriceTotal])


Joan wrote:
I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is available to
use
in VBA? As part if an IIF statement , I would like one of the arguments
to
sum the values in a field on a subform. Below is the line of code that I
am
trying to use in the OnCurrent event of the subform.

ExtendedPriceSum
is

the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function not
defined." and Sum in the above line is highlighted when I debug.

How
do

I
sum ExtendedPrice in my code?


:
The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Marshall Barton]

Marshall,

I want to display the total of the subform for the user's benefit so
that they know what the miscellaneous other charges amount to. Am not
understanding why this would be a problem.

Well, I can't explain why this worked for me, but here's a
different idea I tried out of pure desperation. Instead of
all that IIf - recordsetclone stuff, just use your
=Sum([ExtendedPrice]) expression in the subform and the
expression =EditDog.Form!txtTotalSalePrice
in the main form. Then, it seems that you can display the
zeros when the subform has no records to display, set both
text box's Format property to the custom format 0;;;\0

Where did you intend for the following line to be used?

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,
[nameofsubform].[Form].[ExtendedPriceTotal])

That's the kind of expression I use in the main form because
I alway thought that a total over no records was a
nonexistent value (not even Null). That's certainly the way
the analogous situation in reports work, except reports
provide the HasData property to help clarify things.
--
Marsh
MVP [MS Access]

Sorry, Joan, I've never tried to do it that way and have
never seen that issue before, so I have no ideas about this
latest situation.

Why do you want to display the total of the subform in both
the main form and sub form? I've always wanted it just in
the main form and make the subform's footer invisible so I
don't care if it doesn't display 0 when there's no data to
sum.

I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Reco

r

dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum and the
InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:
=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePri

c

e) +
IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsSu

b

Edit.Form!ExtendedPriceSum).

Now a problem happens when the user attempts to add a new record to the
subform by putting a value in the control, cboChargeCode. When this is
attempted, the user gets the message: "Field cannot be updated", even
though the subform does allow the record to be added. How can I keep this
message from appearing?? This has only started happening since I put the
line:
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Reco

r

dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being used by
two processes simultaneously. What do you think?


:
Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)


=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubfo

r

m].[Form].[ExtendedPriceTotal])


Joan wrote:
I had already tried what you suggested, but if there are no records in
the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is available to
use
in VBA? As part if an IIF statement , I would like one of the
arguments
to
sum the values in a field on a subform. Below is the line of code
that I
am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum
is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function
not
defined." and Sum in the above line is highlighted when I debug. How
do
I
sum ExtendedPrice in my code?


:
The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Joan]

Marshall,
Thanks so much for all of your help. It works!! I put 0;;;\0
for both text box's Format property like you suggested. When I changed the
control source for ExtendedPriceSum back to = Sum([ExtendedPrice]) , I still
got the message: "Field cannot be updated." when attempting to add a new
record by putting a value in the control, cboChargeCode. So then I surmised
there was not a problem with
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice])).



So I went back and looked at the query the subform is built on. Decided to
take out a calculated field in the query, ExtendedPrice: [Quantity] *
[UnitPrice] and use (= [Quantity] * [UnitPrice]) as the control source of
txtExtendedPrice instead of [ExtendedPrice]. I also change the above IIF
statement to:

=IIf([Forms]![EditDeleteInvoice]![InvoiceDetailsSubEdit].[Form].[RecordsetCl
one].[RecordCount]=0,0,Sum(([Quantity]*[UnitPrice])))





I had an AfterUpdate event procedure on cboChargeCode which went like this:



Private Sub cboChargeCode_AfterUpdate()
Select Case cboChargeCode
Case "Crat1"
Me!txtUnitPrice = "23"
Case "Crat2"
Me!txtUnitPrice = "25"
Case "Crat3"
Me!txtUnitPrice = "33"

........

End Select

End Sub



I am guessing that since I was trying to set UnitPrice in the AfterUpdate
event and UnitPrice was also being used in my underlying query to calculate
ExtendedPrice that Access displayed the "Field cannot be updated" message
because two processes were trying to access it at the same time. At any
rate everything works like I had intended and there are no error messages.

Thanks again for helping me solve this problem.

Joan

Marshall,

I want to display the total of the subform for the user's benefit so
that they know what the miscellaneous other charges amount to. Am not
understanding why this would be a problem.

Well, I can't explain why this worked for me, but here's a
different idea I tried out of pure desperation. Instead of
all that IIf - recordsetclone stuff, just use your
=Sum([ExtendedPrice]) expression in the subform and the
expression =EditDog.Form!txtTotalSalePrice
in the main form. Then, it seems that you can display the
zeros when the subform has no records to display, set both
text box's Format property to the custom format 0;;;\0

Where did you intend for the following line to be used?

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,
[nameofsubform].[Form].[ExtendedPriceTotal])

That's the kind of expression I use in the main form because
I alway thought that a total over no records was a
nonexistent value (not even Null). That's certainly the way
the analogous situation in reports work, except reports
provide the HasData property to help clarify things.
--
Marsh
MVP [MS Access]

Sorry, Joan, I've never tried to do it that way and have
never seen that issue before, so I have no ideas about this
latest situation.

Why do you want to display the total of the subform in both
the main form and sub form? I've always wanted it just in
the main form and make the subform's footer invisible so I
don't care if it doesn't display 0 when there's no data to
sum.

Joan wrote:
I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..

=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Rec o
r

dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum

and
the

InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:

=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePr i
c

IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsS u
b

=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Rec o
r

dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being

used
by

two processes simultaneously. What do you think?


:
Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubf o
r

m].[Form].[ExtendedPriceTotal])


Joan wrote:
I had already tried what you suggested, but if there are no

records
in

the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is

available
to

use
in VBA? As part if an IIF statement , I would like one of the
arguments
to
sum the values in a field on a subform. Below is the line of code
that I
am
trying to use in the OnCurrent event of the subform. ExtendedPriceSum
is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function
not
defined." and Sum in the above line is highlighted when I

debug.
How

do
I
sum ExtendedPrice in my code?


:
The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)

 

[Marshall Barton]

Whew. That's a relief. It's nice to know I wasn't losing
my mind about how the messy IIf worked, but I learned
something new about totals in an empty subform so it was a
profitable effort.

It sounds like you sorted out the error message caused by a
calculated field quite well. Good work on a tough problem.
--
Marsh
MVP [MS Access]

Marshall,
Thanks so much for all of your help. It works!! I put 0;;;\0
for both text box's Format property like you suggested. When I changed the
control source for ExtendedPriceSum back to = Sum([ExtendedPrice]) , I still
got the message: "Field cannot be updated." when attempting to add a new
record by putting a value in the control, cboChargeCode. So then I surmised
there was not a problem with
=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Recor
dCount=0,0,Sum([ExtendedPrice])).



So I went back and looked at the query the subform is built on. Decided to
take out a calculated field in the query, ExtendedPrice: [Quantity] *
[UnitPrice] and use (= [Quantity] * [UnitPrice]) as the control source of
txtExtendedPrice instead of [ExtendedPrice]. I also change the above IIF
statement to:

=IIf([Forms]![EditDeleteInvoice]![InvoiceDetailsSubEdit].[Form].[RecordsetCl
one].[RecordCount]=0,0,Sum(([Quantity]*[UnitPrice])))





I had an AfterUpdate event procedure on cboChargeCode which went like this:



Private Sub cboChargeCode_AfterUpdate()
Select Case cboChargeCode
Case "Crat1"
Me!txtUnitPrice = "23"
Case "Crat2"
Me!txtUnitPrice = "25"
Case "Crat3"
Me!txtUnitPrice = "33"

.......

End Select

End Sub



I am guessing that since I was trying to set UnitPrice in the AfterUpdate
event and UnitPrice was also being used in my underlying query to calculate
ExtendedPrice that Access displayed the "Field cannot be updated" message
because two processes were trying to access it at the same time. At any
rate everything works like I had intended and there are no error messages.

Thanks again for helping me solve this problem.

Joan

Marshall,

I want to display the total of the subform for the user's benefit so
that they know what the miscellaneous other charges amount to. Am not
understanding why this would be a problem.

Well, I can't explain why this worked for me, but here's a
different idea I tried out of pure desperation. Instead of
all that IIf - recordsetclone stuff, just use your
=Sum([ExtendedPrice]) expression in the subform and the
expression =EditDog.Form!txtTotalSalePrice
in the main form. Then, it seems that you can display the
zeros when the subform has no records to display, set both
text box's Format property to the custom format 0;;;\0

Where did you intend for the following line to be used?
=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,
[nameofsubform].[Form].[ExtendedPriceTotal])

That's the kind of expression I use in the main form because
I alway thought that a total over no records was a
nonexistent value (not even Null). That's certainly the way
the analogous situation in reports work, except reports
provide the HasData property to help clarify things.
--
Marsh
MVP [MS Access]

Sorry, Joan, I've never tried to do it that way and have
never seen that issue before, so I have no ideas about this
latest situation.

Why do you want to display the total of the subform in both
the main form and sub form? I've always wanted it just in
the main form and make the subform's footer invisible so I
don't care if it doesn't display 0 when there's no data to
sum.

Joan wrote:
I tried a statement that is very close to the one you sent as the control
source of ExtendedPriceSum in the subform footer. This statement below is
similar to yours except that if the RecordCount is not = 0, then it sums
[ExtendedPrice] because it is not yet summed..

=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Rec o
r
dCount=0,0,Sum([ExtendedPrice]))

This works. The subform shows the correct amount in ExtendedPriceSum and
the
InvoiceTotal on the main form shows the correct amount. I used the
following statement as the control source of InvoiceTotal:

=IIf(IsError(EditDog.Form!txtTotalSalePrice),0,EditDog.Form!txtTotalSalePr i
c
e) +

IIf(IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),0,InvoiceDetailsS u
b
Edit.Form!ExtendedPriceSum).

Now a problem happens when the user attempts to add a new record to the
subform by putting a value in the control, cboChargeCode. When this is
attempted, the user gets the message: "Field cannot be updated", even
though the subform does allow the record to be added. How can I keep this
message from appearing?? This has only started happening since I put the
line:

=IIf(Forms!EditDeleteInvoice!InvoiceDetailsSubEdit.Form.RecordsetClone.Rec o
r
dCount=0,0,Sum([ExtendedPrice])) as the control source for
ExtendedPriceSum.

When I had: = Sum([ExtendedPrice]) as the control source the message
didn't
appear. I wonder if the reason it appears now is that I am referring to
the underlying recordset simultaneously in the control source of the
total
control on the subform??? Was hoping that the property, RecordsetClone,
would help in making Access think that the recordset was not being used
by
two processes simultaneously. What do you think?


:
Sorry, Joan, I was off in my thinking. Here's an expression
that I actually tested ;-)

=IIf([nameofsubform].[Form].[RecordsetClone].[RecordCount]=0,0,[nameofsubf o
r
m].[Form].[ExtendedPriceTotal])


Joan wrote:
I had already tried what you suggested, but if there are no records
in
the subform, no total shows in either the subform or on the main form.

Joan wrote:

Could anyone tell me whether or not the SUM() function is available
to
use
in VBA? As part if an IIF statement , I would like one of the
arguments
to
sum the values in a field on a subform. Below is the line of code
that I
am
trying to use in the OnCurrent event of the subform.
ExtendedPriceSum
is
the control with the total in the form footer.

Private Sub Form_Current()
Me.ExtendedPriceSum = IIf(IsError([ExtendedPriceSum]), 0,
Sum([ExtendedPrice]))
End Sub

When I run my form, I get the following message: "Sub or Function
not
defined." and Sum in the above line is highlighted when I debug.
How
do
I
sum ExtendedPrice in my code?


:
The Sum function is not available in the VBA environment, It
only exists in the SQL and control source expression
environments.

Put a text box named ExtendedPriceTotal in the subform's
footer section. Set its expression to:
=Sum(ExtendedPrice)

Then, your ExtendedPriceSum text box on the main form can
display the sum using a text box by using the expression:
=IIf(IsError(ExtendedPriceTotal, 0, ExtendedPriceTotal)