Filter and Header Updates

[EdwardSalas]

Afternoon all,

I've got an interesting one here...I've got a form that is tied to query (very simple). On the header portion of the form I have a calculated field that sums one of the fields.

Here is the problem

I want to apply a filter and determine a % share (Sum of Calc Field filtered / Sum of Total unfiltered) on a click of a command button. The command button applies a filter and writes the 'Sum of Total Unfiltered' into a temp field. However, the code runs so quick that there is no value in the 'Sum of Calc Field' because Access is trying to perform the calculation. I've tried Requerying and that also doesn't work. I'm tempted to use a loop but I don't care to be locked into a timed event (because some categories are larger than others, it may need a longer processing time).

any help would be appreciated!

Eddy

 

[TheAceMan1]

How are ya EdwardSalas . . .

[ol][li]Set a Private variable in the decalarations section of the forms code module:

[blue]Private SOTU As Single [green]'SumOfTotalUnfiltered[/green][/blue]

[/li]
[li]In the OnLoad event og the form, copy/paste the following:

[blue]   SOTU = Me![[purple][b]TextboxName[/b][/purple] for SumofCalcFieldFiltered][/blue]

Now your total is saved and SOTU remains as long as the form is open.[/li]
[li]Setup your [blue]TempField[/blue] to display the results by adjusting the following properties:
[ol a][li]Remove any text in the ControlSource[/li]
[li]Set the Format to Percent.[/li][/ol][/li]
[li]Finally, change your button code to:

[blue]   Me.Filter = [green]'Your Filter[/green]
   Me.FilterOn = True
   Me!TempFieldName = Me![[purple][b]TextboxName[/b][/purple] for SumofCalcFieldFiltered]/SOTU[/blue]

[/li][/ol]
Check it out!

See Ya! . . . . . .