Summarized field in form footers 1

[si013]

Can anyone help. I currently have a Subform (datasheet view) and i have three field summarizing the data,
=count("*&quot
=avg([turnaround])
=sum([turnaround])

Once the form has been refreshed, i reference the three fields within VBA and i assign the values to three variables. But whenever the event is started, the variables are always set to 0. If i put a break point in the code and F8 through it, it will assign the values correctly.

Any ideas?

 

[VBslammer]

Let's see your code.

VBSlammer

Unemployed in Houston, Texas

 

[si013]

dim TotalDone as long
dim TurnAverage as long
dim TotalAverage as long

TotalDone = [frmgrid].form![txtTotalDone]
TotalAverage = [frmgrid].form![txtTotalAverage]
TurnAverage = [frmgrid].form![txtTurnAverage]

 

[VBslammer]

Are the variables dimensioned at module level?

Option Compare Database
Option Explicit

'Module-level declarations

Dim TotalDone as Long
Dim TurnAverage as Long
Dim TotalAverage as Long 


Private Sub Form_Current()
 
  TotalDone = [frmgrid].form![txtTotalDone]
  TotalAverage = [frmgrid].form![txtTotalAverage]
  TurnAverage = [frmgrid].form![txtTurnAverage]

End Sub

VBSlammer

Unemployed in Houston, Texas

 

[si013]

Yes

 

[VBslammer]

I would put textboxes on the main form and set their ControlSource properties to the textboxes on the subform.

If you don't want to see them just set the Visible property to false. That way they will always be current even when the user makes changes to the subform data.

You can get at the totals by referring to the Forms![MainForm]![txtTotalDone] from just about anywhere.

In the main form you can use the textboxes just like variables:

 Msgbox "There are " & [txtTotalDone] & " jobs done."

VBSlammer

Unemployed in Houston, Texas