Subform calculated field #Error

  • Thread starter Thread starter Eric Frohmann
  • Start date Start date
E

Eric Frohmann

I'm attempting to emulate a spreadsheet in Access XP. The subform's
datasource is linked to a table, but I've added a few calculated fields.

In my current incarnation, the form displays correctly most of the time, but
often enough to call it a problem, it will kaff and display #Error in all the
calculated fields. My best guess is that it's a timing issue - the values
required for a particular calc haven't been determined yet....

Here's what I believe is the culprit:
Field is in the Detail:
=IIf(([TotTrees]=0),0,([Pole_AGS]+[Pole_UGS_C]+[Pole_UGS_D]+[Small_AGS]+[Smal
l_UGS_C]+[Small_UGS_D]+[Medium_AGS]+[Medium_UGS_C]+[Medium_UGS_D]+[Large_AGS]
+[Large_UGS_C]+[Large_UGS_D])/[TotTrees]*100)

Field is in the footer:
[TotTrees]=[TotTreesAGS]+[TotTreesUGS_C]+[TotTreesUGS_D]

Field is in the footer:
[TotTreesAGS]=[fldSumPoleAGS]+[fldSumSmallAGS]+[fldSumMediumAGS]+[fldSumLarge
AGS]

Field is in the footer:
[fldSumPoleAGS]=Sum([Pole_AGS])

[Pole_AGS] is in the detail.

Is there any way to force the order in which field's on a form are
calculated??

Failing that, how do I manually assign a value to a field in the detail
portion of a form?

TIA
 
OK - How about this...

What is the best way to fill a calculated field in a form's detail section
for each record displayed from within code?

Should the code go in Form_Current? How do I get a unique value into each
record in the detail?

--
--EricF


Eric Frohmann said:
I'm attempting to emulate a spreadsheet in Access XP. The subform's
datasource is linked to a table, but I've added a few calculated fields.

In my current incarnation, the form displays correctly most of the time, but
often enough to call it a problem, it will kaff and display #Error in all the
calculated fields. My best guess is that it's a timing issue - the values
required for a particular calc haven't been determined yet....

Here's what I believe is the culprit:
Field is in the Detail:
=IIf(([TotTrees]=0),0,([Pole_AGS]+[Pole_UGS_C]+[Pole_UGS_D]+[Small_AGS]+[Small_UGS_C]+[Small_UGS_D]+[Medium_AGS]+[Medium_UGS_C]+[Medium_UGS_D]+[Large_AGS]
+[Large_UGS_C]+[Large_UGS_D])/[TotTrees]*100)

Field is in the footer:
[TotTrees]=[TotTreesAGS]+[TotTreesUGS_C]+[TotTreesUGS_D]

Field is in the footer:
[TotTreesAGS]=[fldSumPoleAGS]+[fldSumSmallAGS]+[fldSumMediumAGS]+[fldSumLarge
AGS]

Field is in the footer:
[fldSumPoleAGS]=Sum([Pole_AGS])

[Pole_AGS] is in the detail.

Is there any way to force the order in which field's on a form are
calculated??

Failing that, how do I manually assign a value to a field in the detail
portion of a form?

TIA
Click to expand...
 
Eric Frohmann said:
OK - How about this...

What is the best way to fill a calculated field in a form's detail section
for each record displayed from within code?

Should the code go in Form_Current? How do I get a unique value into each
record in the detail?
Click to expand...

In a continuous or datasheet form a control that is populated by code
running in the form cannot have a different value per-row. You would need
to use an expression in the ControlSource of the TextBox itself if you want
a per-row value displayed. This expression could include a custom function
that is doing the same calculation that you would otherwise be doing in a
form event.
 
Back
Top