Hi All
Below is one days transactions input for one day of my timesheet. There are 14 days in my timesheet (see image attached) so I would have to repeat this code 14 times, not only hard to manage, but hard to maintain.
Here is the code, below, and further down my attempt at a loop. There are three groups of data, a) Timesheet b) Project allocation c) HR allocation. So theoretically we could have 3 loops, or maybe one depending on clever you guys are.
DoCmd.SetWarnings False
' Input Monday Week 1 TimeSheet dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate, Start, Lunch, Other, Finish," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1, txtStartD1, txtLunchD1, txtOtherD1, txtFinishD1," & _
"txtProjectR1, txtClassR1, txtModuleR1, txtActivityR1D1);")
' Input Monday Week 1 Project dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR2, txtClassR2, txtModuleR2, txtActivityR2D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR3, txtClassR3, txtModuleR3, txtActivityR3D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR4, txtClassR4, txtModuleR4, txtActivityR4D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR5, txtClassR5, txtModuleR5, txtActivityR5D1);")
' Input Monday Week 1 Leave dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR1, txtRecLeaveCodeR1, txtRecLeaveR1, txtRecLeaveD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR2, txtRecLeaveCodeR2, txtRecLeaveR2, txtSickLeaveD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR3, txtRecLeaveCodeR3, txtRecLeaveR3, txtLSLD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR3, txtRecLeaveCodeR3, txtRecLeaveR3, txtLWPD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR5, txtRecLeaveCodeR5, txtRecLeaveR5, txtLeaveotherD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR6, txtRecLeaveCodeR6, txtRecLeaveR6, txtPublicHolD1);")
' __________________________________________________________________________________________________
my attempt at a loop for the first data group.
Dim i As Integer
i = 1
' Input Monday Week 1 dataset
Do While i <= 14 'this will do x rotations.
For i = 1 To 14
' Exmaple, Me.Controls ("txtStartD" & i)
' Input Monday Week 1 Project dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"me.controls txtTransDate & i, txtProjectR & i, txtClassR & i, txtModuleR & i, txtActivityR2D & i);")
Next i
Loop
Any help would be appreciated and thanking some one brilliant in advance.
Terry
Below is one days transactions input for one day of my timesheet. There are 14 days in my timesheet (see image attached) so I would have to repeat this code 14 times, not only hard to manage, but hard to maintain.
Here is the code, below, and further down my attempt at a loop. There are three groups of data, a) Timesheet b) Project allocation c) HR allocation. So theoretically we could have 3 loops, or maybe one depending on clever you guys are.
DoCmd.SetWarnings False
' Input Monday Week 1 TimeSheet dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate, Start, Lunch, Other, Finish," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1, txtStartD1, txtLunchD1, txtOtherD1, txtFinishD1," & _
"txtProjectR1, txtClassR1, txtModuleR1, txtActivityR1D1);")
' Input Monday Week 1 Project dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR2, txtClassR2, txtModuleR2, txtActivityR2D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR3, txtClassR3, txtModuleR3, txtActivityR3D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR4, txtClassR4, txtModuleR4, txtActivityR4D1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtProjectR5, txtClassR5, txtModuleR5, txtActivityR5D1);")
' Input Monday Week 1 Leave dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR1, txtRecLeaveCodeR1, txtRecLeaveR1, txtRecLeaveD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR2, txtRecLeaveCodeR2, txtRecLeaveR2, txtSickLeaveD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR3, txtRecLeaveCodeR3, txtRecLeaveR3, txtLSLD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR3, txtRecLeaveCodeR3, txtRecLeaveR3, txtLWPD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR5, txtRecLeaveCodeR5, txtRecLeaveR5, txtLeaveotherD1);")
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"txtTransDate1," & _
"txtDivisionR6, txtRecLeaveCodeR6, txtRecLeaveR6, txtPublicHolD1);")
' __________________________________________________________________________________________________
my attempt at a loop for the first data group.
Dim i As Integer
i = 1
' Input Monday Week 1 dataset
Do While i <= 14 'this will do x rotations.
For i = 1 To 14
' Exmaple, Me.Controls ("txtStartD" & i)
' Input Monday Week 1 Project dataset
DoCmd.RunSQL ("INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _
"TransDate," & _
"Project, Class, Module, ActivityHrs )" & _
"VALUES(txtGiven_Name, txtFamily_Name, txtEmployeeNumber, txtfortnightending," & _
"me.controls txtTransDate & i, txtProjectR & i, txtClassR & i, txtModuleR & i, txtActivityR2D & i);")
Next i
Loop
Any help would be appreciated and thanking some one brilliant in advance.
Terry