the payroll form has a subform where the payroll records are entered. When a new record is entered and certain criteria are met an update query runs and takes their total hours from their employee record and then either subtracts or adds the hours from the payroll reocrd and then updates the value back into their employee record. So that their employee record is always accurate. And yes, i am storing the total because i need ti to be accurate as this is a payroll system for a relatively large fire department. At the end of next year they are going to archive the first year payroll records so i have to save the value otherwise when they archive the totals would be wrong. Hope this helps, let me know. I tried to insert a procedure on the beforedeleteconfirm event and the delete event but i can't get either to work right.
Branden
P.S. Here is the code from the subform so you may understand it a little better.
Private Sub Flex_AfterUpdate()
On Error GoTo Err_Flex_AfterUpdate
'If any value is entered in the flex column, updates the flex add checkbox
chkFlexAdd = True
Exit_Flex_AfterUpdate:
Exit Sub
Err_Flex_AfterUpdate:
MsgBox Err.Description
Resume Exit_Flex_AfterUpdate
End Sub
Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert
'Sets variables and performs calculations on the total kelly hours
'in individual employee records
Dim KellyAdd As String
Dim KellySubtract As String
Dim KellyReset As String
KellyAdd = "qryKellyUpdate"
KellySubtract = "qryKellySubtract"
KellyReset = "qryResetKelly"
If Not IsNull(Kelly.Value) Then
DoCmd.OpenQuery KellyAdd, acViewNormal, acAdd
End If
If DescAM.Value = "BANK" Then
DoCmd.OpenQuery KellySubtract, acViewNormal, acAdd
End If
If DescAM.Value = "KELLY" Then
DoCmd.OpenQuery KellySubtract, acViewNormal, acAdd
End If
DoCmd.OpenQuery KellyReset, acViewNormal, acAdd
'Sets variables and performs calculations on the total flex hours
'in individual employee records
Dim FlexAdd As String
Dim FlexSubtract As String
Dim FlexReset As String
FlexAdd = "qryFlexUpdate"
FlexSubtract = "qryFlexSubtract"
FlexReset = "qryResetFlex"
If Not IsNull(Flex.Value) Then
DoCmd.OpenQuery FlexAdd, acViewNormal, acAdd
End If
If DescAM.Value = "FLEX" Then
DoCmd.OpenQuery FlexSubtract, acViewNormal, acAdd
End If
DoCmd.OpenQuery FlexReset, acViewNormal, acAdd
Exit_Form_AfterInsert:
Exit Sub
Err_Form_AfterInsert:
MsgBox Err.Description
Resume Exit_Form_AfterInsert
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert
'Updates the user field with the current user name as the record is created
User.Value = CurrentUser()
Exit_Form_BeforeInsert:
Exit Sub
Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
'Updates the user field with the current user name if the record is altered
Me.User.Value = CurrentUser()
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub
Private Sub Form_Delete(Cancel As Integer)
End Sub
Private Sub Kelly_AfterUpdate()
On Error GoTo Err_Kelly_AfterUpdate
'If any value is entered in the kelly column, updates the kelly add checkbox
chkKellyAdd.Value = True
Exit_Kelly_AfterUpdate:
Exit Sub
Err_Kelly_AfterUpdate:
MsgBox Err.Description
Resume Exit_Kelly_AfterUpdate
End Sub
Private Sub DescAM_AfterUpdate()
On Error GoTo Err_DescAM_AfterUpdate
'Checks the contents of the DescAM field and then updates chk box values accordingly
If DescAM.Value = "BANK" Then
chkKellySubtract.Value = True
ElseIf DescAM.Value = "KELLY" Then
chkKellySubtract.Value = True
End If
If DescAM.Value = "FLEX" Then
chkFlexSubtract.Value = True
End If
Exit_DescAM_AfterUpdate:
Exit Sub
Err_DescAM_AfterUpdate:
MsgBox Err.Description
Resume Exit_DescAM_AfterUpdate
End Sub
|
