Display Sum of field values in Subform

[Derek]

Hi All

I have a continuous subform. It works fine and displays the records from the query. But now I want to display the total of "Score" field value in that recordset and display that in a textbox (may be on the footer).

How can I do the sum of "Score" field value in all the records in the subform?

Any help will be much appreciated.

Thanks

 

[Isskint]

You say you are using a query as the source of your continuous form records, so =Dsum("[Score]","[SOURCEQUERY]") {where [SOURCEQUERY] is the name of your query} in a textbbox should do.

 

[CJ_London]

if your textbox is in the subform footer then you would use

=sum([score])

 

[Derek]

Thanks. As there will always be 3 records in the subform. I also want to check
1. if score of any record is 1 then store in another textbox "Doesn't meet expectations".
2. If score of any two records is 3 then store in the textbox "Exceeds"
3. ANy other combination store "Meets"

 

[CJ_London]

for that you will need to use the domain functions

1. if score of any record is 1 then store in another textbox "Doesn't meet expectations".
2. If score of any two records is 3 then store in the textbox "Exceeds"

=iif(dmax("score","sourcequery")=1,"Doesn't meet expectations",iif(dcount("*","sourcequery","Score=3")=2,"Exceeds","Meets"))

 

[Derek]

CJ_London, Can I check individual record using do while statement? How can I use any loop within subform recordset? As I have some more calculations I need to do.

 

[CJ_London]

yes you can

something like

 me.recordsetclone.movefirst
 while not me.recordsetclone.eof
     'do something here
     me.recordsetclone.movenext
 wend

 

[Derek]

CJ_London, I am writing the following code but it doesn't seem to be working. I will always have 3 records in the subform so I have written the following code in the click event of the checkbox. Sometimes the value of a,b or c is right and sometimes its wrong (sometimes it gives old recordset values for a,b,c)

 Private Sub Check23_Click()
If Check23.Value = True Then
 txtOverallScore.Visible = True
 Dim rs As Recordset
Dim a, b, c As Integer
Set rs = Me.RecordsetClone
 rs.MoveFirst
 a = rs.Fields("Salesservicescore").Value
 MsgBox "First record=" & a
 rs.MoveNext
 b = rs.Fields("Salesservicescore").Value
 MsgBox "Second record=" & b
 rs.MoveNext
 c = rs.Fields("Salesservicescore").Value
 MsgBox "Third record=" & c
 
If a = 1 Or b = 1 Or c = 1 Then
Me.txtOverallScore = "Doesn't meet expectations"
 ElseIf a + b + c > 7 Then
Me.txtOverallScore = "Exceeds"
 Else
Me.txtOverallScore = "Meets Expectations"
End If
 End If
End Sub

 

[CJ_London]

Sometimes the value of a,b or c is right and sometimes its wrong (sometimes it gives old recordset values for a,b,c)

implies you are loading the data, then changing it. If so, put

me.refresh

before your line

Set rs = Me.RecordsetClone


some other comments
to declare as integer you need

Dim a As Integer, b As Integer, c As Integer (at the moment a and b will be variant types

You don't need .value - that is the default

 

[Derek]

It worked fine. Thanks