display totals on subform

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
 
S

strive4peace

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 :)
*
 
M

Microsoft

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...
 
S

strive4peace

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...
 
M

Microsoft

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...
 
S

strive4peace

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...
 
M

Microsoft

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...
 
M

Microsoft

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...
 
S

strive4peace

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...
 
S

strive4peace

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...
 

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

subform totals to popform 1
subform total to mainform 2
Access Daily Entry with multiple entries per field 0
How to position records in a scrolling subform 5
Totals from subform problem 7
Subform 1
subform record source tabbed page 2
Continuous forms coding suggestions needed. 1

Top