Categories
MySQL

Access VBA > MySQL Update TimeSheetRates

Private Sub UpdateTimeSheetCalculations()
On Error GoTo Err_UpdateTimeSheetCalculations
‘this simple subroutine looks at the MySQL View “time_sheet_calc_dynamic” and copies the calculated values therein to the time_sheet table
‘The “time_sheet_calc_dynamic” does not include records where the “ts_calculation_completed” column is set to 1. The DB Manager can switch this flag
‘on and off via the Time Sheet Management form or when running a report for the timesheet. This means calculations made are “frozen in time”
‘with the rates as they were so subsequent changes to mileage or on-call rates are not re-calculated.

Dim strSQLUpdate As String

strSQLUpdate = “UPDATE time_sheet AS x INNER JOIN time_sheet_calc_dynamic AS y ON y.ts_id = x.ts_id SET ”

strSQLUpdate = strSQLUpdate & “x.ts_MonMileage = y.MonMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_TueMileage = y.TueMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileage = y.wedMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileage = y.thuMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileage = y.friMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileage = y.satMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileage = y.sunMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileage = y.mamMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_MonStandbyRate = y.MonStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_tueStandbyRate = y.tueStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_wedStandbyRate = y.wedStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_thuStandbyRate = y.thuStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_friStandbyRate = y.friStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_MonTotalHours = y.MonTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_tueTotalHours = y.tueTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_wedTotalHours = y.wedTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_thuTotalHours = y.thuTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_friTotalHours = y.friTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_satTotalHours = y.satTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_sunTotalHours = y.sunTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_mamTotalHours = y.mamTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_MonMileageCosts = y.MonMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_tueMileageCosts = y.tueMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileageCosts = y.wedMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileageCosts = y.thuMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileageCosts = y.friMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileageCosts = y.satMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileageCosts = y.sunMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileageCosts = y.mamMileageCosts;”

CurrentDb.Execute strSQLUpdate, dbFailOnError

Exit_UpdateTimeSheetCalculations:
Exit Sub
Err_UpdateTimeSheetCalculations:
MsgBox Err.description
Resume Exit_UpdateTimeSheetCalculations
End Sub