Form Field Calculations not calculating the first time through

T

TheRoundPen

I am working on an employee appraisal form that has for different sections
for evaluating and recording scores. Each section averages these scores and
records the average at the bottom of the section and also on a summary page.

On the summary page I have another field that calcuates a "Grand Average"
for a total appraisal score. I have saved this document as a template. When
I open the document and populate it with scores the Grand Average total is
not correct; however, when I simply tab through the entire document - without
making ANY changes - by the time I get to the summary page the Grand average
has now calculated correctly.

Please tell me what in the world I am doing wrong!
Thank you!
 
D

Doug Robbins - Word MVP

To you have the Calculate on Exit box checked for ALL of the formfields that
contribute to the calculation?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
T

TheRoundPen

Yes...however these four fields are populated from code and there is never
the chance to actually exit these fields - would that cause a problem?
 
D

Doug Robbins - Word MVP

What is the code and what is triggering it?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
T

TheRoundPen

This is kind of long, but here's the code. The score field for each
question in each qroup is set to run the corresponding code on exit.
(FYI...DepartmentKnowledge is a single score and does not need an average -
it just gets transferred to the summary page using Summary_Transfer)


Sub Summary_Transfer()

With ActiveDocument
If .FormFields("DepartmentKnowledge").Result = "1" Then
.FormFields("DepartmentSummary").Result = "1"
ElseIf .FormFields("DepartmentKnowledge").Result = "2" Then
.FormFields("DepartmentSummary").Result = "2"
ElseIf .FormFields("DepartmentKnowledge").Result = "3" Then
.FormFields("DepartmentSummary").Result = "3"
ElseIf .FormFields("DepartmentKnowledge").Result = "4" Then
.FormFields("DepartmentSummary").Result = "4"
ElseIf .FormFields("DepartmentKnowledge").Result = "5" Then
.FormFields("DepartmentSummary").Result = "5"
Else: .FormFields("DepartmentSummary").Result = "0"

End If


End With

End Sub

Sub Perform_CoreKnowledgeAverage()

Dim i As Long, j As Long
Dim Total As Double
Dim ffname As String
Total = 0
i = 0
With ActiveDocument
For j = 1 To 7
ffname = "CoreKnowledge0" & j

If Val(.FormFields(ffname).Result) <> 0 Then
Total = Total + .FormFields(ffname).Result
i = i + 1
End If

Next j
If i > 0 Then
.FormFields("TotalCoreKnowledge").Result = Total / i
.FormFields("CoreKnowledgeSummary").Result = Total / i
Else
.FormFields("TotalCoreKnowledge").Result = 0
.FormFields("CoreKnowledgeSummary").Result = 0
End If
End With

End Sub

Sub Perform_Compentancy_Average()

Dim k As Long, l As Long
Dim Total As Double
Dim ffname As String
Total = 0
k = 0
With ActiveDocument
For l = 1 To 12
ffname = "Comp0" & l
If Val(.FormFields(ffname).Result) <> 0 Then
Total = Total + .FormFields(ffname).Result
k = k + 1
End If
Next l
If k > 0 Then
.FormFields("TotalComp").Result = Total / k
.FormFields("CompSummary").Result = Total / k
Else
.FormFields("TotalComp").Result = 0
.FormFields("CompSummary").Result = 0
End If
End With

End Sub
Sub Perform_Values_Average()

Dim o As Long, p As Long
Dim Total As Double
Dim ffname As String
Total = 0
o = 0
With ActiveDocument
For p = 1 To 8
ffname = "Values0" & p
If Val(.FormFields(ffname).Result) <> 0 Then
Total = Total + .FormFields(ffname).Result
o = o + 1
End If
Next p
If o > 0 Then
.FormFields("TotalValues").Result = Total / o
.FormFields("ValuesSummary").Result = Total / o
Else
.FormFields("TotalValues").Result = 0
.FormFields("ValuesSummary").Result = 0
End If
End With

End Sub

Once these calculations are done, I have a form field (GrandAverage) that is
set as a calculation type with this calculation:
=(DepartmentSummary + CoreKnowledgeSummary + CompSummary + ValuesSummary) / 4

Boy, I sure hope all this makes sense to you! It is kind of hard to explain
without showing you the document. Once again, I really appreciate your help!
 
D

Doug Robbins - Word MVP

The following should overcome the issue:

Sub Summary_Transfer()
With ActiveDocument
.FormFields("DepartmentSummary").result =
Val(.FormFields("DepartmentKnowledge").result)
.FormFields("GrandAverage").result =
(.FormFields("DepartmentSummary").result _
+ .FormFields("CoreKnowledgeSummary").result +
..FormFields("CompSummary").result _
+ .FormFields("ValuesSummary").result) / 4
End With
End Sub
Sub Perform_CoreKnowledgeAverage()
Dim i As Long, j As Long
Dim Total As Double
Dim ffname As String
Total = 0
i = 0
With ActiveDocument
For j = 1 To 7
ffname = "CoreKnowledge0" & j
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
i = i + 1
End If
Next j
If i > 0 Then
.FormFields("TotalCoreKnowledge").result = Total / i
.FormFields("CoreKnowledgeSummary").result = Total / i
Else
.FormFields("TotalCoreKnowledge").result = 0
.FormFields("CoreKnowledgeSummary").result = 0
End If
.FormFields("GrandAverage").result =
(.FormFields("DepartmentSummary").result _
+ .FormFields("CoreKnowledgeSummary").result +
..FormFields("CompSummary").result _
+ .FormFields("ValuesSummary").result) / 4
End With
End Sub
Sub Perform_Compentancy_Average()
Dim k As Long, l As Long
Dim Total As Double
Dim ffname As String
Total = 0
k = 0
With ActiveDocument
For l = 1 To 12
ffname = "Comp0" & l
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
k = k + 1
End If
Next l
If k > 0 Then
.FormFields("TotalComp").result = Total / k
.FormFields("CompSummary").result = Total / k
Else
.FormFields("TotalComp").result = 0
.FormFields("CompSummary").result = 0
End If
.FormFields("GrandAverage").result =
(.FormFields("DepartmentSummary").result _
+ .FormFields("CoreKnowledgeSummary").result +
..FormFields("CompSummary").result _
+ .FormFields("ValuesSummary").result) / 4
End With
End Sub
Sub Perform_Values_Average()
Dim o As Long, p As Long
Dim Total As Double
Dim ffname As String
Total = 0
o = 0
With ActiveDocument
For p = 1 To 8
ffname = "Values0" & p
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
o = o + 1
End If
Next p
If o > 0 Then
.FormFields("TotalValues").result = Total / o
.FormFields("ValuesSummary").result = Total / o
Else
.FormFields("TotalValues").result = 0
.FormFields("ValuesSummary").result = 0
End If
.FormFields("GrandAverage").result =
(.FormFields("DepartmentSummary").result _
+ .FormFields("CoreKnowledgeSummary").result +
..FormFields("CompSummary").result _
+ .FormFields("ValuesSummary").result) / 4
End With
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
M

macropod

Hi TheRoundPen,

Do note that having a formfield calculation that references another formfield calculation is liable to generate erroneous results.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to run automatically when a field is updated 1
Average calculation when a field is left blank 7
Form data entry 0
Form data entry 3
Form Field/Function problem 2
Database structure 13
AVG and IIF expressions 0
How do I add a value to a check box? 1

Top