Sum of Current Record and all prior for Form with Textbox

[sailinxtc]

I am trying to have a sum of a field in a form with a textbox, and would inlcude a total for the current record and all prior for that field. In the following Log ID is the Primary Key, Hours trained is the Field to sum, Totals Hrs is the field for the Total, TTLOG is the table, and the Form is also TTLOG. I would like to call this on lost focus Event. I have included what I have based on the example referenced and others work. I am not sure how to call the total to the Text box, or if I need to create a module, and wasn't sure what idValue was either ? any help would be appreciated...

Private Function frmRunSum(curForm As Form, Log_ID As String, idValue, sumField As String)

If Not IsNull(Me!Log_ID) Then
UniqueNamePerForm = frmRunSum(Me, "Log_ID", Me!Log_ID, "Hours_Trained")
[Total Hrs] = ([Hours Trained])
End If

End Function

 

[dhookom]

I'm not sure where you found this function or how it is used. Can you define "prior"? This sounds like there should be some field or fields that identify an order such as a date field.

I think you could use DSum("Hours_Trained","TTLOG","....")

I assume the TTLOG table has records for multiple people etc. The "...." in the DSum() needs to filter to a specific subset of records in the table.

Duane
Hook'D on Access
MS Access MVP

 

[sailinxtc]

Any Records prior to the current one being used or viewed, i.e. Log ID 1 "Hours Trained" = 2 "Total Hrs" = 2
Log ID 2 "Hours Trained" = 6 "Total Hrs" = 8
Log ID 3 "Hours Trained" = 1.5 "Total Hrs" = 9.5

Basically for each Record in the Form or Report I would like to see the Total hours for each Log ID or Record I am viewing. The Hours Trained and Total Hrs are a Text Box on the Form. The user enters the Hours Trained on each record, and then Total hrs is displayed. I thought this would be simple but am strugling or missing something obbious. Also wasn't sure if it would be better to try and do this with a querry but since its real time didn't think I should use the querry. I was trying to utilize the function cause it was the closest example I could find. thanks..

 

[dhookom]

You can use a running sum on a report.

Again, you haven't described any definition of what you think prior means. Records are like marbles in a bag. There is no marble that is prior to any other marble.

Duane
Hook'D on Access
MS Access MVP

 

[sailinxtc]

Prior Log ID's, so if I am viewing Log ID 3, it should total Log Id's 1,2 and 3. If I am viewing Log Id 2, then it would total Log ID 1 and 2 and show the correct sum in the textbox Total Hrs. The total would be of Hours Trained. Similar to a running sum but only to the current record or Log Id viewed and all prior.

 

[sailinxtc]

This is where I am at but now need to find the method to set the current Log ID for the criteria and sum the current and prior Log ID for the field Hours Trained. I was reading way to much into it before thanks... It currently just multiplies the last Log ID # by the Current Hours Trained.

If [Log ID] > 1 Then
[Total Hrs] = DSum("Hours_Trained", "TTLOG", Me![Log ID])

Else
[Total Hrs] = ([Hours Trained])
End If
DoCmd.RunCommand acCmdSaveRecord

 

[dhookom]

So the value of the Log ID field determines the order and can be used for evaluating "Prior".

Are the field names "Hours_Trained" or "Hours Trained" ; "Log ID" or "Log ID"?

[Total Hrs] = DSum("Hours_Trained", "TTLOG", "[Log ID],=" & Me![Log ID])
DoCmd.RunCommand acCmdSaveRecord

Duane
Hook'D on Access
MS Access MVP

 

[sailinxtc]

It is Hours Trained and Log ID with a space between them. Is there a way to Sum the current record plus the previous records or prior, where if the current record is number 3, it would include record number 2 and 1 in the sum or DSum. I am not sure if I can do this with a querry or should use and array

[Total Hrs] = DSum("Hours Trained", "TTLOG", "[Log ID] = " & Me![Log ID])

 

[PHV]

DSum("[Hours Trained]", "TTLOG", "[Log ID]<=" & Me![Log ID])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sailinxtc]

Can't believe I Didn't see that, I can tell you it works without even trying it. thanks...

 

[dhookom]

Having to type []s is the penalty you pay for creating field and table names that contain spaces or other non-standard characters. You might want to find and use a good, consistent naming convention that doesn't allow spaces, symbols, and punctuation marks.

Duane
Hook'D on Access
MS Access MVP