Summing a Subform

[TT]

Hi there,

I'm sure this is a very easy question, but for some reason, I'm having a
problem figureing this out!!

On my main form (frmDetailReport), I have an unbound field called
txtUnitCount which is controlled by a combo box. When the user selects an
item from the combo box, txtUnitCount uses an appropriate DCount formula to
gather the unit count. This all works fine.

Secondly, I have a continuous subform (frmVenueDetails) with the following
information on it:
CategoryID (Integer)
Flat Rate (Yes/No)
Cost Per Unit (Double)

I have also created an unbound field called txtAmount on the subform whose
formula reads:
=IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit])

This is basically saying, if the category is a flat fee, then just use the
amount in the CostPerUnit field. If it is not, then take the CostPerUnit
amount and multiply it by the txtUnitCount from the main form. This also
works correctly.

The problem I'm running into is summing up the txtAmount field. I'm
assuming that I can't just put an unbound field at the top of my form header
called =Sum([txtAmount]) as there really is no values for the field to sum
up. So I tried the following:
=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))
Using this formula, I get an #Error.

I'm sure it's something simple in my formula that I'm doing wrong, but if
someone could help me out, that would be great!!

Thanks in advance!!

 

[Steven]

Hi there,

I'm sure this is a very easy question, but for some reason, I'm having a
problem figureing this out!!

On my main form (frmDetailReport), I have an unbound field called
txtUnitCount which is controlled by a combo box. When the user selects an
item from the combo box, txtUnitCount uses an appropriate DCount formula to
gather the unit count. This all works fine.

Secondly, I have a continuous subform (frmVenueDetails) with the following
information on it:
CategoryID (Integer)
Flat Rate (Yes/No)
Cost Per Unit (Double)

I have also created an unbound field called txtAmount on the subform whose
formula reads:
=IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit])

This is basically saying, if the category is a flat fee, then just use the
amount in the CostPerUnit field. If it is not, then take the CostPerUnit
amount and multiply it by the txtUnitCount from the main form. This also
works correctly.

The problem I'm running into is summing up the txtAmount field. I'm
assuming that I can't just put an unbound field at the top of my form header
called =Sum([txtAmount]) as there really is no values for the field to sum
up. So I tried the following:
=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))
Using this formula, I get an #Error.

I'm sure it's something simple in my formula that I'm doing wrong, but if
someone could help me out, that would be great!!

Thanks in advance!!

That sounds like it should work if your fields a calculating ok in the
subform Detail section. On the subform in the footer section put your
textbox with a control source of =Sum([NameOfField]) but if that does not
work maybe try =Sum([NameOfObject]) and then in the main form in you can put
a textbox with a control source of
=[Forms]![MainFormName]![SubFormName].[Form]![NameOfObjectOnSubform]. Hope
this works for you.

 

[TT]

Thanks for your help Steven,

Unfortunately, it just doesn't want to work! I'm so baffled.

I tried to do a sum of the CostPerUnit and it works fine, but as soon as I
try to sum up the calculated field, I wind up with the error.

Is there anything else you can think up? I'm at a total loss here.

Thanks again for everything!!

Todd

Hi there,

I'm sure this is a very easy question, but for some reason, I'm having a
problem figureing this out!!

On my main form (frmDetailReport), I have an unbound field called
txtUnitCount which is controlled by a combo box. When the user selects an
item from the combo box, txtUnitCount uses an appropriate DCount formula to
gather the unit count. This all works fine.

Secondly, I have a continuous subform (frmVenueDetails) with the following
information on it:
CategoryID (Integer)
Flat Rate (Yes/No)
Cost Per Unit (Double)

I have also created an unbound field called txtAmount on the subform whose
formula reads:
=IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit])

This is basically saying, if the category is a flat fee, then just use the
amount in the CostPerUnit field. If it is not, then take the CostPerUnit
amount and multiply it by the txtUnitCount from the main form. This also
works correctly.

The problem I'm running into is summing up the txtAmount field. I'm
assuming that I can't just put an unbound field at the top of my form header
called =Sum([txtAmount]) as there really is no values for the field to sum
up. So I tried the following:
=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))
Using this formula, I get an #Error.

I'm sure it's something simple in my formula that I'm doing wrong, but if
someone could help me out, that would be great!!

Thanks in advance!!

That sounds like it should work if your fields a calculating ok in the
subform Detail section. On the subform in the footer section put your
textbox with a control source of =Sum([NameOfField]) but if that does not
work maybe try =Sum([NameOfObject]) and then in the main form in you can put
a textbox with a control source of
=[Forms]![MainFormName]![SubFormName].[Form]![NameOfObjectOnSubform]. Hope
this works for you.

 

[Steven]

=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))

I tried an example of using =sum(...) in the footer section of the subform
using =Sum(Calculation of fields as needed) and it worked ok. And then I was
able to reference that Sum Textbox to the main form. Can the problem be you
need " " around the yes in your formula?

If this does not work you may need to post the problem again since there has
been so much activity on this post people might bypass it thinking it has
been resolved.
Good Luck.

 

[Steven]

Sorry, I think that last responce I put was not too bright. I forgot you
said the field is a yes/no. I made an example in a db I have and added and
yes/no field and did a simple calculation and did a sum in the footer and
then put a field on the main form and referenced the field from the subform
and it worked ok. The only difference is instead of saying = yes I said =
-1. It sure seems if the individual calculations in the detail of the
subform are calculating ok then the rest should also work ok.

 

[TT]

I don't know what the problem is. I tried your exact solution and it still
gives me an error. The funny thing is, I know I've done something like this
before. Grrrrr.

Anyway, I can re-post the problem again as you suggested. Is there any way
that I can securely ship my DB off to someone to have a look at?

Thanks again for all your help!!

Todd

 

[TT]

That's okay Steven, I knew what you meant. I tried -1 as well and it gave me
the same error. Funny how the same thing works on the detail section of the
subform but not the footer.

I did try one thing as a test also. I changed the sum formula to
=[txtAmount] just to see if I could get the amount for the one record and
that worked fine. I also tried the
=IIf([flatrate]=Yes,[costperunit],[Forms]![frmDetailReport]![txtUnitCount]*[costperunit]) and that worked fine for that one record as well.

As soon as I put the Sum() around either of them, it fails on me.

It shouldn't have anything to do with the DCount number on the main form
should it??

I'm grasping at straws here aren't I?? haha

Thanks again Steven!

Todd

 

[TT]

Thanks Marshall,

At least you solved the question of why it's not working. I didn't realize
the Sum function had that limitation to it.

The only problem about moving the DCount to the record souce of the subform
is that it's not a query that feeds the subform. The main form and sub form
are both tables.

Do you have any suggestions as to how to get the total for my subform
without changing the structure of things?

Thanks so much for you time!

Todd

I'm sure this is a very easy question, but for some reason, I'm having a
problem figureing this out!!

On my main form (frmDetailReport), I have an unbound field called
txtUnitCount which is controlled by a combo box. When the user selects an
item from the combo box, txtUnitCount uses an appropriate DCount formula to
gather the unit count. This all works fine.

Secondly, I have a continuous subform (frmVenueDetails) with the following
information on it:
CategoryID (Integer)
Flat Rate (Yes/No)
Cost Per Unit (Double)

I have also created an unbound field called txtAmount on the subform whose
formula reads:
=IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit])

This is basically saying, if the category is a flat fee, then just use the
amount in the CostPerUnit field. If it is not, then take the CostPerUnit
amount and multiply it by the txtUnitCount from the main form. This also
works correctly.

The problem I'm running into is summing up the txtAmount field. I'm
assuming that I can't just put an unbound field at the top of my form header
called =Sum([txtAmount]) as there really is no values for the field to sum
up. So I tried the following:
=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))
Using this formula, I get an #Error.

The problem here is that Sum only operates on Record source
**fields**, it is totally unaware of controls.

To use Sum you'll have to find a way to get the txtUnitCount
value in the subform's record source query. Maybe you cuold
move the DCount there??

 

[TT]

Thanks Marshall,

I originally had the subform bound to a table itself, but by linking it to a
query, I could solve that problem.

Thank you so much for your input!
Todd

I'm sure this is a very easy question, but for some reason, I'm having a
problem figureing this out!!

On my main form (frmDetailReport), I have an unbound field called
txtUnitCount which is controlled by a combo box. When the user selects an
item from the combo box, txtUnitCount uses an appropriate DCount formula to
gather the unit count. This all works fine.

Secondly, I have a continuous subform (frmVenueDetails) with the following
information on it:
CategoryID (Integer)
Flat Rate (Yes/No)
Cost Per Unit (Double)

I have also created an unbound field called txtAmount on the subform whose
formula reads:
=IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit])

This is basically saying, if the category is a flat fee, then just use the
amount in the CostPerUnit field. If it is not, then take the CostPerUnit
amount and multiply it by the txtUnitCount from the main form. This also
works correctly.

The problem I'm running into is summing up the txtAmount field. I'm
assuming that I can't just put an unbound field at the top of my form header
called =Sum([txtAmount]) as there really is no values for the field to sum
up. So I tried the following:
=Sum(IIf([FlatRate]=Yes,[CostPerUnit],[Forms]![frmDetailReport]![txtUnitCount]*[CostPerUnit]))
Using this formula, I get an #Error.

The problem here is that Sum only operates on Record source
**fields**, it is totally unaware of controls.

To use Sum you'll have to find a way to get the txtUnitCount
value in the subform's record source query. Maybe you cuold
move the DCount there??