Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 15th, 2003, 02:36 PM
Authorized User
 
Join Date: Nov 2003
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to bjackman Send a message via MSN to bjackman
Default Access Calculations

Hey Folks,

I'm building a payroll program that allows the employees to bank hours worked and then take them at a later date. It updates the user information form with a running total of how many hours they have.

What i need to know if it is possible when one of these records are edited or deleted i need it to update the employee record with the changes. For example if record 1 added 14 hours to the total hours and then was edited so that it only had 10 hours, i need it to subtract the 14 hours from the records then add the 10 bck in. If this is confusing, let me know and i'll try to provide more info.

Thanks in advance,

Branden

 
Old December 15th, 2003, 07:01 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can u be more specific? I need to know the way you are updating the total hours and what fields you are using.
Regards Edward
 
Old December 15th, 2003, 08:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are you storing the total? If so, don't, if not - do you want it recorded that 14 hours was removed, and 10 put in, or will it do that the 14 just gets changed?
I think more info is needed here.

Steven

I am a loud man with a very large hat. This means I am in charge
 
Old December 15th, 2003, 09:44 PM
Authorized User
 
Join Date: Nov 2003
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to bjackman Send a message via MSN to bjackman
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 4 calculations zenixcomp BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 November 3rd, 2008 05:18 PM
Date Calculations scourge SQL Server 2000 9 May 11th, 2006 05:08 AM
time calculations lizhaskin Access 1 November 10th, 2005 08:48 AM
DateTime calculations sudarshan73 SQL Language 8 April 7th, 2005 09:07 AM
DateTime calculations sudarshan73 SQL Server 2000 1 March 15th, 2005 03:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.