#Error In Sub-Form

[Jeff G]

All -

I have an interesting one...

I'm running Access 2003 (SP2). I have a form that has a sub-form on it.
Normally, everything works fine. Where I run into and issue and when I go
into Form Design and make changes, either on the Form itself or through VBA.
When I go back to Form View, the Total fields on the sub-form all say #Error
and I get a "Calculating..." on the Status Bar. If I Minimize Access and
Restore it, everything in the main Form's footer is missing. The only way
to get everything back to the way it is supposed to be is to close out of
the app and go back in.

Any clues on why this is happening?

Thanks in advance.

Jeff

 

[vbasean]

I'm not saying my way is the best but I like to use the DSum function for
subtotals.

the reason for this is two fold:
1. I'm less likely to get an error based off of the form's info
2. I can put it anywhere on the form (subform, main form) and get the same
results. (as long as I can retreave the seed/id number to filter my DSum
function)

i.e. DSum("[field to sum]", "[source table/query of items to sum]", "[id
field to filter on] = " & me.[field in my form that contains the id])

if the field is text and not a number then you will need to enclose the
field in quotes:
DSum("[field to sum]", "[source table/query of items to sum]", "[id field to
filter on] = " & chr(34) & me.[field in my form that contains the id] &
chr(34))


if I enclose my DSum in an nz function I don't get the error
nz(DSum("[field to sum]", "[source table/query of items to sum]", "[id field
to filter on] = " & me.[field in my form that contains the id]), 0)