SUM records of a subform field

[secretary]

I've created a subform on my mainform by just dragging fields onto it in layout view so it looks like I have a datasheet in the middle of my form.



I opened the subform in Design View and added a textbox called txtTotalOwing to the Form Footer with the control source set to =Sum([txtAmountOwing]) where txtAmountOwing is the field in the subform that I want to total. In the subform's design view, this field appears as a single textbox.

I intend to display this Total on my mainform, but before referencing it, I check to see if it works. When I look at the subform in form view, the txtTotalOwing box says #Error.

I'm not sure what's wrong. I followed the procedure right off of a help forum, and I've discovered other forums with similar suggestions. I don't know where I went wrong.

I've never worked with subforms before, which is why I described what everything looks and how I made it I'm not even sure if I made the form correctly.

[ketbdnetbp]

Is this field [txtAmountOwing] a calculated field in the subform?

If so, see -

http://support.microsoft.com/default.aspx?scid=kb%3Ben%2Dus%3B199355

Otherwise, check spelling and/or check for missing references in the VBE, under tools > references, see if any are marked as MISSING.
Hope this helps.

Jim

[secretary]

Hi Jim!

[txtAmountOwing] is not a calculated field as far as I can tell. It's control source is just a table field [Amount Owing] that does not depend on other fields.

I've checked and rechecked spelling.

I've looked in the VBE and I have the following selected:

- VBA
- Microsoft Access Object Library
- OLE Automation
- Microsoft Office database engine Object Library

Not sure what might be "missing"...

[ketbdnetbp]

In that case, try -

=Sum([AmountOwing])

as the control source.

Jim

[secretary]

If you mean referencing the table [Amount Owing] directly (there is no [AmountOwing] control/field), I can't do that because it contains subform records for every main form record, which means I would sum up all the debt for all the accounts. I just want the total debt for each separate record of the main form.

[ketbdnetbp]

No, I was referring to the field in your subform.

In your first post, you stated:

"I've created a subform on my mainform by just dragging fields onto it in layout view so it looks like I have a datasheet in the middle of my form."

"I opened the subform in Design View and added a textbox called txtTotalOwing to the Form Footer with the control source set to =Sum([txtAmountOwing]) where txtAmountOwing is the field in the subform that I want to total. In the subform's design view, this field appears as a single textbox."

My thought was, if you dragged and dropped the field "AmountOwing" to the subform from the recordsource and didn't change its name, then the control source of the "txtTotalOwing" field should be =Sum([AmountOwing]), instead of =Sum([txtAmountOwing]).

Jim

[secretary]

Oh I see.

Yes the subform fields had at one point the same names as the table fields because of how I built the subform, but I changed them.

[txtAmountOwing] is now the name of the textbox I want to sum up.

:/

[ketbdnetbp]

So, I assume this -

=Sum([txtAmountOwing]), in the control source of the "txtTotalOwing", still shows #Error?

And, the control "txtTotalOwing" is in the form footer of the SUBFORM?
And, the control "txtAmountOwing" is not a calculated field?
And, in the control's properties, the Name is txtAmountOwing and the control source is AmountOwing?

Let me know if all of the above are correct?

Thanks,

Jim

[secretary]

Yup.
That's all true.

[rpeare]

on your main form if you have a text box with the following it should reflect the total of the subform


=forms!MainFormName.SubFormName!txtTotalOwing

Where mainformname and subformname are the names of your parent (main) form and child form (subform) respectively.

[secretary]

=[Forms]![LevyEntry].[subLevies]![txtTotalOwing]

It certainly reflects the total of the subform.
And that total is #Error XD

[rpeare]

you usually see that message when you're trying to perform a mathematical function on a text field or your expression (formula) is invalid. Any chance you could post a sample database with enough fake data to re-create the error.

[secretary]

At the moment, I think my issue with the SUM may have something to do with the fact that I can't properly link the subform to the main form.

The controls on the main form (frmLevies) aren't even picking up data from the MembersQuery properly, and that is nothing but a simple use of the ControlSource property, and I have NO idea how I could have POSSIBLY gotten that wrong.

[rpeare]

Here's your database back.

You had the control sources of your fields as a formula rather than as an actual field from the query you were using as a data source for the form.

You also were not summing the subform amount field correctly.

[secretary]

Thanks. That helped a lot. I see where I went wrong.

However, it's still not summing the entire Amount Owing field; I'm only getting the value of the first record in the SUM control.