Text Field in a Form summing up the Values in a Subform

[Kitana907]

Hi-
I am having issues with adding up fields in a subform. I have a form that has a subform in datasheet mode. It lists titles of books with their correlating units [OH] and dollar amount [OHExtRetail].

The user will be able to delete records from this subform. I am trying to add in text boxes that list the sum of oh, the sum of extretail, and the count of titles.

I have tried it the three ways below with no luck... (I'll give examples with the OH field):

1. By putting in a text box in the footer of the subform. The control source reads =Sum([OH])
The issue I have with this is that I cannot get the footer to show up when the form is activated. The property is set on visible. I think the issue may be that I have the subform in datasheet mode (which I need for it to be in).

2. I then tried putting the text fields into the main form. I changed the Control Source to read = Sum([Forms}![tbletempreturnworkbook subform]![OH])
No luck.

3. I have a query that actually calculates the count of titles, the sum of OH and the sum of ExtRetail. So I tried this for the Control Source =[qryreturnworkbooktotals]![Units]
No luck.

Any ideas?

Thanks in advance.

[PEB]

Return to option 2 and try:
= Sum([tbletempreturnworkbook subform].Form![OH])

[Kitana907]

Thanks, but I still get an error...

When I try to save the change, Access eliminates the brackets around the OH, so it ends up reading:

=Sum([tbletempreturnworkbook subform].Form!OH)

I tried using the expression builder in Access to read off the table where the data is coming from and then I tried the subform, but still nothing.

Is it possible to do what I'm trying to do in Access? I know it can be done on reports as I have it working there, but I've never seen it on a Form before.

Thanks for any help you can give.

[gildomilo]

To get a sum from a subform which is a datasheet have a texbox named say SubformSum in the subform with a control source of

=Sum([Field_Name]) 

in the footer. In your mainform footer have a textbox with a control source

=SubFormName.Form!SubFormSum

. I just tried it out and it works...