display totals on subform

  • Thread starter Thread starter Microsoft
  • Start date Start date
M

Microsoft

Access 2007

I have a MainForm that includes a SubForm. I can enter values into the
SubForm.
On the SubForm (continuous form) there is a button that opens up another
form (ItemForm - not a subform) to enter details of records that relate to
the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed on the
SubForm records?

If so how?

Thanks

A
 
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is
not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
strive4peace said:
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is not
found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Access 2007

I have a MainForm that includes a SubForm. I can enter values into the
SubForm.
On the SubForm (continuous form) there is a button that opens up another
form (ItemForm - not a subform) to enter details of records that relate
to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed on
the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
 
you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
strive4peace said:
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is not
found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Access 2007

I have a MainForm that includes a SubForm. I can enter values into the
SubForm.
On the SubForm (continuous form) there is a button that opens up another
form (ItemForm - not a subform) to enter details of records that relate
to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed on
the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
yes
the control names on the form are txtEstimateNo and txtItemNo and they are
both Text

Thanks for your help


strive4peace said:
you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
strive4peace said:
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is
not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into the
SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed on
the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Hi (what is your name?)

if you are in the code behind the subform:

"EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



yes
the control names on the form are txtEstimateNo and txtItemNo and they are
both Text

Thanks for your help


strive4peace said:
you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*


Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is
not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into the
SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed on
the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
I am putting this in the Control source of an unbound text box on the
subform

=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[me].[txtEstimateNo] & "' AND ItemNo= '" & [me].[txtItemNo] & "'"),0)

the textbox now just displays Name?

What am I doing wrong

Thanks
Alison
strive4peace said:
Hi (what is your name?)

if you are in the code behind the subform:

"EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



yes
the control names on the form are txtEstimateNo and txtItemNo and they
are both Text

Thanks for your help


strive4peace said:
you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of
each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]",
"condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is
not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into
the SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed
on the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help - I've got it to work!

I removed the me and used the field name not the control name (ie.
removed the txt...

Thanks again
Microsoft said:
I am putting this in the Control source of an unbound text box on the
subform

=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[me].[txtEstimateNo] & "' AND ItemNo= '" & [me].[txtItemNo] & "'"),0)

the textbox now just displays Name?

What am I doing wrong

Thanks
Alison
strive4peace said:
Hi (what is your name?)

if you are in the code behind the subform:

"EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



yes
the control names on the form are txtEstimateNo and txtItemNo and they
are both Text

Thanks for your help


you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of
each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]",
"condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition
is not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into
the SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed
on the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Hi Alison,

since you have the equation in a ControlSource, remove 'Me.' in front of
the controlnames ...

"EstimateNo ='" & [txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



I am putting this in the Control source of an unbound text box on the
subform

=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[me].[txtEstimateNo] & "' AND ItemNo= '" & [me].[txtItemNo] & "'"),0)

the textbox now just displays Name?

What am I doing wrong

Thanks
Alison
strive4peace said:
Hi (what is your name?)

if you are in the code behind the subform:

"EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



yes
the control names on the form are txtEstimateNo and txtItemNo and they
are both Text

Thanks for your help


you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of
each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]",
"condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition is
not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into
the SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed
on the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
glad you got it, Alison ;) happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your help - I've got it to work!

I removed the me and used the field name not the control name (ie.
removed the txt...

Thanks again
Microsoft said:
I am putting this in the Control source of an unbound text box on the
subform

=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[me].[txtEstimateNo] & "' AND ItemNo= '" & [me].[txtItemNo] & "'"),0)

the textbox now just displays Name?

What am I doing wrong

Thanks
Alison
strive4peace said:
Hi (what is your name?)

if you are in the code behind the subform:

"EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Microsoft wrote:
yes
the control names on the form are txtEstimateNo and txtItemNo and they
are both Text

Thanks for your help


you're welcome

are EstimateNo and ItemNo your fieldnames? what is the data type of
each?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Thanks - I'm not sure on the condition part of this. Please clarify

I want it to sum where:
EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo

How would I write this?

Thanks again

A
one way would be to use a dSum equation in a textbox (this way, your
ItemForm does not have to be open)

make a textbox control
Name --> SumItems
ControlSource --> =nz(dSum("[fieldname]", "[Tablename]",
"condition"),0)

WHERE

fieldname is the name of the field (or the expression) to add

Tablename is the name of the table containing the field

condition is the string with the condition for which to add the
field/expression
ie: "ItemID=" & [ItemID]
where ItemID is the fieldname in Tablename
[ItemID] is the controlname on the current form to match the value of

nz is Null-to-Zero, so zero is returned in the event that condition
is not found

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Microsoft wrote:
Access 2007

I have a MainForm that includes a SubForm. I can enter values into
the SubForm.
On the SubForm (continuous form) there is a button that opens up
another form (ItemForm - not a subform) to enter details of records
that relate to the items on the subform.

Is it possible that the sum(values) of the ItemForm can be displayed
on the SubForm records?

If so how?

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Back
Top