UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dao Update No Longer Working, Access 2010    
 
   
guitarsweety
post Sep 17 2017, 04:20 AM
Post#1



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


The below code was working fine. It was pulling each of the referenced fields from the Employee Details Form for Each record and putting them into the temp History table. Now it's only pulling in the EID, EmpID, Employee Name, Active and YearEnd. All of the other fields have a zero.

One thing I did notice is when I changed the calculation in the txtTotalHoursWorkedMain field from this
CODE
=IIf([HoursWorked].[Form]![txtTotalHoursWorked] & ""="",0,[HoursWorked].[Form]![txtTotalHoursWorked])
to this
CODE
=[HoursWorked].[Form]![txtTotalHoursWorked]
and re-ran the code the Hours Worked field in the temp History Table was blank instead of showing a zero. Makes me think it's doing something, just not what I want it to.

Can anyone help? I just can't figure out why it stopped working.
I've started re-creating the entire database piece by piece to see if there is something else that could have caused the problem but have yet to find anything.


CODE
Private Sub btnUpdate_Click()
Dim dbs As DAO.Database
'Dim rst1 As DAO.Recordset
Dim lngLastEID As Long
Set dbs = CurrentDb()
Set rst2 = dbs.OpenRecordset("TEMP_History_Table", dbOpenDynaset)
DoCmd.GoToRecord Record:=acLast
lngLastEID = Me.EID
DoCmd.GoToRecord Record:=acFirst
Do While True
rst2.AddNew
rst2!EID = Me.EID
rst2!EmpID = Me.EmpID
rst2![Employee Name] = Me.[Employee Name]
rst2!Active = Me.Active
rst2![YearEnd] = Me.YearEnd
rst2![Hours Worked] = Me.txtTotalHoursWorkedMain
rst2![Actual Earned] = Me.txtactualhoursearned
rst2![Earned With Cap] = Me.txtcap
rst2![HoursCarriedOver] = Me.txtavailablecap
rst2![Sick Used] = Me.txtUsed
rst2![Available] = Me.txtavailablecap
rst2.Update
If Me.EID = lngLastEID Then
Exit Do
Else
DoCmd.GoToRecord Record:=acNext
End If
Loop
rst2.Close
Set rst2 = Nothing
DoCmd.GoToRecord Record:=acFirst
MsgBox "All employee attendance records have been updated to the historical table for further refernce!", vbCritical + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
End Sub

Attached File(s)
Attached File  Capture.PNG ( 42.18K )Number of downloads: 7
 
Go to the top of the page
 
moke123
post Sep 17 2017, 06:05 AM
Post#2



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Hi GS,
first , code is much easier to read when it is indented. A free add-in that works great can be found here...smart indenter
heres what the result looks like
CODE
Private Sub btnUpdate_Click()
    Dim dbs As DAO.Database
    'Dim rst1 As DAO.Recordset
    Dim lngLastEID As Long
    Set dbs = CurrentDb()
    Set rst2 = dbs.OpenRecordset("TEMP_History_Table", dbOpenDynaset)
    DoCmd.GoToRecord Record:=acLast
    lngLastEID = Me.EID
    DoCmd.GoToRecord Record:=acFirst
    Do While True
        rst2.AddNew
        rst2!EID = Me.EID
        rst2!EmpID = Me.EmpID
        rst2![Employee Name] = Me.[Employee Name]
        rst2!Active = Me.Active
        rst2![YearEnd] = Me.YearEnd
        rst2![Hours Worked] = Me.txtTotalHoursWorkedMain
        rst2![Actual Earned] = Me.txtactualhoursearned
        rst2![Earned With Cap] = Me.txtcap
        rst2![HoursCarriedOver] = Me.txtavailablecap
        rst2![Sick Used] = Me.txtUsed
        rst2![Available] = Me.txtavailablecap
        rst2.Update
        If Me.EID = lngLastEID Then
            Exit Do
        Else
            DoCmd.GoToRecord Record:=acNext
        End If
    Loop
    rst2.Close
    Set rst2 = Nothing
    DoCmd.GoToRecord Record:=acFirst
    MsgBox "All employee attendance records have been updated to the historical table for further refernce!", vbCritical + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
End Sub


that said, i'm confused as your title says DAO Update however your code indicates an AddNew routine.
Also it appears you are iterating thru a form. Why not just open a recordset?
Go to the top of the page
 
guitarsweety
post Sep 17 2017, 06:15 AM
Post#3



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


Thank you moke123, I'm downloading the indenter now. Any clue as to why my code isn't working?
Go to the top of the page
 
moke123
post Sep 17 2017, 07:09 AM
Post#4



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I'm looking at a sample you uploaded in a prior post and cant seem to find the iif code your talking about.
CODE
=IIf([HoursWorked].[Form]![txtTotalHoursWorked] & ""="",0,[HoursWorked].[Form]![txtTotalHoursWorked])

i cant see how the above code worked. if the person worked 4 hours your iif criteria would resolve to "4=".

CODE
=[HoursWorked].[Form]![txtTotalHoursWorked]


if you want a zero rather than a null try
CODE
=nz([HoursWorked].[Form]![txtTotalHoursWorked],0)


that aside, you may want to re-think your tables. for instance your Emp table. this table appears to hold employee information (name, status, etc) as well as the hours worked, carry over, etc. These should be in separate tables.
I would also think about whether you really want a history type table. It appears your using a calendar year so with properly normalized tables a history table may not be necessary. Hopefully someone with more experience with a time and attendance database will chime in on this issue
Go to the top of the page
 
guitarsweety
post Sep 17 2017, 07:16 AM
Post#5



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


Thanks for the help moke123. I need this data in a history table so that when the new year resets I still have the totals from the prior years. Hopefully I'll get someone out there that can help. Thanks again for your time.
For now I'll have to settle for the following until i can figure out how to do a loop on all of the records:

CODE
Private Sub test_Click()
Dim StrSQL As String

StrSQL = "INSERT INTO TEMP_History_Table (EID,EmpID,[Employee Name],Active,YearEnd,[Hours Worked],[Actual Earned],[Earned With Cap],HoursCarriedOver,[Sick Used],[Available]) VALUES ('" & Form!EID & "','" & Form!EmpID & "','" & Form![Employee Name] & "','" & Form!Active & "','" & Form!YearEnd & "','" & Form!txtTotalHoursWorkedMain & "','" & Form!txtactualhoursearned & "','" & Form!txtcap & "','" & Form!txtavailablecap & "','" & Form!txtUsed & "','" & Form!txtavailablecap & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
DoCmd.GoToRecord Record:=acNext
End Sub
Go to the top of the page
 
moke123
post Sep 17 2017, 08:05 AM
Post#6



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
I need this data in a history table so that when the new year resets I still have the totals from the prior years.

Thats what i'm talking about. To get a prior years total for instance you would just adjust the criteria in your query based on dates.
Another for instance may be vacation time. Assuming employees earn a certain amount of time each pay period, they get credited that time each payday and when they use it, it gets debited. If they're permitted to carry over a certain amount to the new year, you have a procedure that calculates that and adjusts the running sum accordingly on 1/1.
Go to the top of the page
 
guitarsweety
post Sep 17 2017, 08:22 AM
Post#7



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I've attached a stripped down copy of the database if you have time to look at it. The employee names were all created from a random generator and are not real. My decision to create a historical table was based on a suggestion from a forum member. If there's a better way to do this I'm all in. And there are also allowed to carry over a certain amount of hours, I've been stuck on that also, but my idea was to pull the available balance based on the year end date from the history table.
Attached File(s)
Attached File  September_17.zip ( 184.69K )Number of downloads: 7
 
Go to the top of the page
 
moke123
post Sep 17 2017, 11:17 AM
Post#8



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



there are always a number of contingencies that could happen. For instance an employee who gets sick during the workday and goes home early. Then you need to be able to split it, 4.5 hrs reg pay and 2.5 sick pay.
I'm sure if you search there are a number of data models you could find.
Go to the top of the page
 
guitarsweety
post Sep 17 2017, 11:22 AM
Post#9



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


That's handled by the time keeper in an entirely separate program. The data I'm working with is from time already entered into that program.
Go to the top of the page
 
guitarsweety
post Sep 17 2017, 12:28 PM
Post#10



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


The code below is only pulling in the first record on the employee details form, but at least I'm making progress.

CODE
Private Sub test1_Click()

Dim StrSQL As String
Dim dbs  As DAO.Database
Set dbs = CurrentDb
Set RS = dbs.OpenRecordset("TEMP_History_Table", dbOpenDynaset)
Set rst1 = [Forms]![Employee Details].RecordsetClone
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveFirst
While (Not rst1.EOF)


StrSQL = "INSERT INTO TEMP_History_Table (EID,EmpID,[Employee Name],Active,YearEnd,[Hours Worked],[Actual Earned],[Earned With Cap],HoursCarriedOver,[Sick Used],[Available]) VALUES ('" & Form!EID & "','" & Form!EmpID & "','" & Form![Employee Name] & "','" & Form!Active & "','" & Form!YearEnd & "','" & Form!txtTotalHoursWorkedMain & "','" & Form!txtactualhoursearned & "','" & Form!txtcap & "','" & Form!txtavailablecap & "','" & Form!txtUsed & "','" & Form!txtavailablecap & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
rst1.MoveNext
DoCmd.GoToRecord Record:=acNext

        Wend
    End If
    rst1.Close
    Set rst1 = Nothing
    RS.Close
    Set RS = Nothing
       End Sub
Go to the top of the page
 
moke123
post Sep 17 2017, 08:46 PM
Post#11



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I fooled around with it a little and just wanted to post what I had so far as its getting late. I'm having some difficulty figuring out your calculations so if you can just list them it may save me some time.

this would go in a stand alone module.

CODE
Public Sub sSetHistoryTable()

    Dim strSql As String
    Dim StrSQL2 As String
    Dim StrHist As String
    Dim lngHist As Long
    Dim db As DAO.Database
    Dim rsEmp As DAO.Recordset
    Dim rsHist As DAO.Recordset
        
    Set db = CurrentDb

    strSql = "SELECT T_Emp.EID, T_Emp.EmpID, DateSerial(Year(Date()),12,31) AS YearEnd, T_Emp.FName, T_Emp.MName, T_Emp.LName, " & _
             "T_Emp.[SDY Accrual], T_Emp.Attachments, T_Emp.Active, T_Emp.[Employee Name], T_Emp.HoursCarriedOver, T_Emp.CYearEnd, t_emp.[SDY Accrual]" & _
             " FROM T_Emp WHERE (((T_Emp.Active) = 'Yes')) ORDER BY T_Emp.LName;"

    StrHist = "select * from TEMP_History_Table where History_ID = 0"

    Set rsHist = db.OpenRecordset(StrHist)
    Set rsEmp = db.OpenRecordset(strSql)

    Do Until rsEmp.EOF

        rsHist.AddNew
        rsHist!EID = rsEmp!EID
        rsHist!EmpID = rsEmp!EmpID
        rsHist![Employee Name] = rsEmp![Employee Name]
        rsHist!Active = rsEmp!Active
        rsHist!YearEnd = rsEmp!YearEnd
        lngHist = rsHist.Fields(0)
        rsHist.Update

        Call sSetWorkHours(lngHist, rsEmp!EmpID, rsEmp![SDY Accrual])  'Call sub for each record in recordset

        rsEmp.MoveNext

    Loop

    rsHist.Close
    Set rsHist = Nothing
    rsEmp.Close
    Set rsEmp = Nothing
End Sub

'_____________________________________________________________

Public Sub sSetWorkHours(varHist As Long, varEmp As Long, varSdy As Double)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim totalHrs As Double
    Dim totalSick As Double
    Dim strSql As String

    strSql = "select * from  tblAttendance where EmpID   = " & varEmp

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        MsgBox "None"
        GoTo MyExit
    End If

    Do Until rs.EOF

        totalHrs = 0
        totalSick = 0

        If rs!KronosCode = "REG" Then
            totalHrs = totalHrs + rs!Amount
        ElseIf rs!KronosCode = "SDY" Then
            totalSick = totalSick + rs!Amount

        End If
        rs.MoveNext
    Loop

    Debug.Print varEmp & "   " & totalHrs
    Dim UdSql As String
    UdSql = "Update TEMP_History_Table set [Hours Worked] = " & totalHrs & " ,[Sick Used] = " & totalSick & " where History_ID = " & varHist

    db.Execute UdSql, dbFailOnError

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub
Go to the top of the page
 
guitarsweety
post Sep 18 2017, 05:06 AM
Post#12



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


Wow thank you very much. The calculations are as followed:

Hours Worked = is the sum of the Amount field in the tblAttendance if the [KronosCode] = "REG"

Actual Earned = is the sum of the Amount field in the tblAttendance if the [KronosCode] = "REG" multiplied by the [SDY Accrual] field in the T_Emp table (which I probably don't need to have store in the table) Accrual amount is 0.033333333 (without the cap)

Earned With Cap = is the capped earnings that I have in a function =CapEarnings([txttotalearned],[YearEnd]

Here is the function:

CODE
Option Compare Database
Option Explicit

Function CapEarnings(txttotalEarned As Double, YearEnd As Date) As Double

    If Format(YearEnd, "yyyy") = 2017 Then
        If txttotalEarned > 20 Then CapEarnings = 20
    End If
    If Format(YearEnd, "yyyy") = 2017 Then
        If txttotalEarned < 20 Then CapEarnings = txttotalEarned
    End If
    If Format(YearEnd, "yyyy") > 2017 Then
        If txttotalEarned > 40 Then CapEarnings = 40
    End If
    If Format(YearEnd, "yyyy") > 2017 Then
        If txttotalEarned < 40 Then CapEarnings = txttotalEarned
    End If
    If Format(YearEnd, "yyyy") = 2017 Then
        If txttotalEarned = 0 Then CapEarnings = 0
    End If

    If Format(YearEnd, "yyyy") > 2017 Then
        If txttotalEarned = 0 Then CapEarnings = 0
    End If
End Function



HoursCarriedOver = this would be any [available] hours from the previous year if applicable.

Sick Used = is the sum of the Amount field in the tblAttendance if the [KronosCode] = "SDY"

Available = is any remaining hours available which would be carried over to the next year. (2017 max is 20 else > 2017 max is 40)
CappedEarnings - [HoursCarriedOver] -[Sick Used]
Go to the top of the page
 
guitarsweety
post Sep 18 2017, 09:32 AM
Post#13



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I'm so excited yayhandclap.gif I have this much of it put together so far

CODE
Public Sub sSetWorkHours(varHist As Long, varEmp As Long, varSdy As Double)
    Dim Sum
    Dim Sum1
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim totalHrs As Double
    Dim totalSick As Double
    Dim earnedHrs As Double
    Dim strSql As String

    strSql = "select * from  tblAttendance where EmpID   = " & varEmp
  
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        MsgBox "None"
        GoTo MyExit
    End If

    Do Until rs.EOF

        'totalHrs = 0
        'totalSick = 0
        'Actual Earned
        '0.033333333
        'Earned With Cap
        'HoursCarriedOver
        'Available
        
        Sum = Sum + rs!Amount
        Sum1 = Sum + rs!Amount
       If rs!KronosCode = "REG" Then
            totalHrs = Sum
       End If
        If rs!KronosCode = "SDY" Then
            totalSick = totalSick + rs!Amount
            End If
        If rs!KronosCode = "REG" Then
            earnedHrs = Sum * 0.033333333
            End If
        rs.MoveNext
    Loop

    Debug.Print varEmp & "   " & totalHrs
    Dim UdSql As String
    UdSql = "Update TEMP_History_Table set [Hours Worked] = " & totalHrs & " ,[Actual Earned] = " & earnedHrs & " ,[Sick Used] = " & totalSick & " where History_ID = " & varHist

    db.Execute UdSql, dbFailOnError

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub
Go to the top of the page
 
moke123
post Sep 18 2017, 09:55 AM
Post#14



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



i'm still a little confused. I think i have most of them except available.

Hours worked = sum of Amount in tblAttendance w/ REG

Sick Used = sum of Amount in tblAttendance w/ SDY

Actual earned = (sum of Amount in tblAttendance w/ REG) * 0.033333333

earned with Cap = function CapEarnings2 ( i re-wrote you function)

Hours carried over = Hours carried over in T_Emp

Available =?

heres what I have so far

CODE
Public Sub sSetHistoryTable()

    Dim strSql As String
    Dim StrSQL2 As String
    Dim StrHist As String
    Dim lngHist As Long
    Dim db As DAO.Database
    Dim rsEmp As DAO.Recordset
    Dim rsHist As DAO.Recordset
        
    Set db = CurrentDb

     strSql = "select T_Emp.*,DateSerial(Year(Date()),12,31) AS YearEnd  from T_Emp where Active = 'Yes'"
            

    StrHist = "select * from TEMP_History_Table where History_ID = 0"

    Set rsHist = db.OpenRecordset(StrHist)
    Set rsEmp = db.OpenRecordset(strSql)

    Do Until rsEmp.EOF

        rsHist.AddNew
        rsHist!EID = rsEmp!EID
        rsHist!EmpID = rsEmp!EmpID
        rsHist![Employee Name] = rsEmp![Employee Name]
        rsHist!Active = rsEmp!Active
        rsHist!YearEnd = rsEmp!YearEnd
        rsHist!HoursCarriedOver = rsEmp!HoursCarriedOver
        
        lngHist = rsHist.Fields(0)
        rsHist.Update

        Call sSetWorkHours(lngHist, rsEmp!EmpID)  'Call sub for each record in recordset

        rsEmp.MoveNext

    Loop

    rsHist.Close
    Set rsHist = Nothing
    rsEmp.Close
    Set rsEmp = Nothing
End Sub

Public Sub sSetWorkHours(varHist As Long, varEmp As Long)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim totalHrs As Double
    Dim totalSick As Double
    Dim strSql As String
    Dim UdSql As String
    Dim AE As Double
    Dim EWC As Double
    
    strSql = "select * from  tblAttendance where EmpID   = " & varEmp

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        MsgBox "None"
        GoTo MyExit
    End If
    
        totalHrs = 0
        totalSick = 0
        
    Do Until rs.EOF

        If rs!KronosCode = "REG" Then
            totalHrs = totalHrs + rs!Amount
        ElseIf rs!KronosCode = "SDY" Then
            totalSick = totalSick + rs!Amount
        End If
        
        rs.MoveNext
    Loop
    
    rs.MoveFirst
    
    AE = totalHrs * 0.033333333
    
    EWC = CapEarnings2(AE, CLng(rs!YearEnd))
    
    UdSql = "Update TEMP_History_Table set [Hours Worked] = " & totalHrs & ", [Actual Earned] = " & AE & " ,[Sick Used] = " & totalSick & " ,[Earned With Cap] = " & EWC & " where History_ID = " & varHist

    db.Execute UdSql, dbFailOnError

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Public Function CapEarnings2(TE As Double, YearEnd As Long) As Double

    Select Case YearEnd

    Case Is = 2017
        If TE = 0 Then
            CapEarnings2 = 0
        ElseIf TE > 20 Then
            CapEarnings2 = 20
        ElseIf TE < 20 Then
            CapEarnings2 = TE
        End If

    Case Is > 2017

        If TE = 0 Then
            CapEarnings2 = 0
        ElseIf TE > 40 Then
            CapEarnings2 = 40
        ElseIf TE < 40 Then
            CapEarnings2 = TE
        End If

    End Select

End Function
Go to the top of the page
 
guitarsweety
post Sep 18 2017, 10:23 AM
Post#15



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


What you gave me was a good starting point but I needing to sum the amounts, etc. I can't thank you enough. I just finished it up and though I would share it with you. I wouldn't have been able to do it without your help.

CODE
Option Compare Database
Public Sub sSetHistoryTable()

    Dim strSql As String
    Dim StrSQL2 As String
    Dim StrHist As String
    Dim lngHist As Long
    Dim db As DAO.Database
    Dim rsEmp As DAO.Recordset
    Dim rsHist As DAO.Recordset
        
    Set db = CurrentDb

    strSql = "SELECT T_Emp.EID, T_Emp.EmpID, DateSerial(Year(Date()),12,31) AS YearEnd, T_Emp.FName, T_Emp.MName, T_Emp.LName, " & _
             "T_Emp.[SDY Accrual], T_Emp.Attachments, T_Emp.Active, T_Emp.[Employee Name], T_Emp.CYearEnd, t_emp.[SDY Accrual]" & _
             " FROM T_Emp WHERE (((T_Emp.Active) = 'Yes')) ORDER BY T_Emp.LName;"

    StrHist = "select * from TEMP_History_Table where History_ID = 0"

    Set rsHist = db.OpenRecordset(StrHist)
    Set rsEmp = db.OpenRecordset(strSql)

    Do Until rsEmp.EOF

        rsHist.AddNew
        rsHist!EID = rsEmp!EID
        rsHist!EmpID = rsEmp!EmpID
        rsHist![Employee Name] = rsEmp![Employee Name]
        rsHist!Active = rsEmp!Active
        rsHist!YearEnd = rsEmp!YearEnd
        lngHist = rsHist.Fields(0)
        rsHist.Update

        Call sSetWorkHours(lngHist, rsEmp!EmpID, rsEmp![SDY Accrual])  'Call sub for each record in recordset

        rsEmp.MoveNext

    Loop

    rsHist.Close
    Set rsHist = Nothing
    rsEmp.Close
    Set rsEmp = Nothing
End Sub

'_____________________________________________________________

Public Sub sSetWorkHours(varHist As Long, varEmp As Long, varSdy As Double)
    Dim YearEnd As Date
    Dim Sum
    Dim Sum1
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim totalHrs As Double
    Dim totalSick As Double
    Dim earnedHrs As Double
    Dim strSql As String

    strSql = "select * from  tblAttendance where EmpID   = " & varEmp
  
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        MsgBox "None"
        GoTo MyExit
    End If

    Do Until rs.EOF

        'totalHrs = 0
        'totalSick = 0
        'Actual Earned
        '0.033333333
        'Earned With Cap
        'HoursCarriedOver
        'Available
        
        Sum = Sum + rs!Amount
        Sum1 = Sum + rs!Amount
       If rs!KronosCode = "REG" Then
            totalHrs = Sum
       End If
        If rs!KronosCode = "SDY" Then
            totalSick = totalSick + rs!Amount
            End If
        If rs!KronosCode = "REG" Then
            earnedHrs = Sum * 0.033333333
            End If
        
        YearEnd = DateSerial(Year(Date), 12, 31)
            cappedHrs = CapEarnings(earnedHrs, YearEnd)
             availableHrs = Nz(cappedHrs, 0) - Nz(totalSick, 0)
             carryoverhrs = Nz(cappedHrs, 0) - Nz(totalSick, 0)
        rs.MoveNext
    Loop

    Debug.Print varEmp & "   " & totalHrs
    Dim UdSql As String
    UdSql = "Update TEMP_History_Table set [Hours Worked] = " & totalHrs & " ,[Actual Earned] = " & earnedHrs & " ,[Sick Used] = " & totalSick & " ,[Earned With Cap] = " & cappedHrs & " ,[Available] = " & availableHrs & ",[HoursCarriedOver] = " & carryoverhrs & " where History_ID = " & varHist

    db.Execute UdSql, dbFailOnError

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub
Go to the top of the page
 
moke123
post Sep 18 2017, 06:00 PM
Post#16



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Glad to hear you got it working.
Now you can put it aside and go enjoy the Mayberry Days Events! Starts Today I believe.
good luck.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 02:40 PM