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 working code, below. 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);")
' __________________________________________________ ________________________________________________
Any help would be appreciated and thanking some one brilliant in advance.
Terry