Results 1 to 5 of 5
  1. #1
    undertm is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    1

    Smile Loop for DoCmd.RunSQL ("INSERT INTO tbl

    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
    Attached Thumbnails Attached Thumbnails Image for forum.png  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you post a sample database, I do not want to re-create your form or your data structure, but there should be a very easy way to get this done just by looping through controls rather than having 12098341098237408192734 sql build statements all doing roughly the same thing. To create a sample database just make a copy of your database, delete anything personal/private and leave only the objects needed to make the problem work, in this case, the tables you are trying to add information to, the form itself and any supporting tables involved in combo boxes etc.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't know if you followed to the cross post but OP has been given solution there in post 13, which ought to fix the incorrect syntax of prior posts.
    That the others were incorrect is a poor excuse for not declaring the cross post, I'd say.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    yar, post 13 was my post I went there just to see what else people had suggested to him.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-02-2017, 10:40 AM
  2. Replies: 2
    Last Post: 09-22-2016, 05:09 AM
  3. Cleaner Code with DoCmd.RunSQL Insert into
    By mduplantis in forum Programming
    Replies: 33
    Last Post: 04-30-2015, 07:49 PM
  4. Replies: 3
    Last Post: 01-27-2015, 12:25 PM
  5. DoCmd.RunSQL
    By Access_Blaster in forum Programming
    Replies: 6
    Last Post: 07-23-2013, 09:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums