Loop for DoCmd.RunSQL ("INSERT INTO tbl (1 Viewer)

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

Please post lengthy code within CODE tags to retain indentation and readability.

I presume none of this code is working. Don't include variables within quote marks, concatenate. Reference to control is a variable. However, control name can be built dynamically. Use text and date/time delimiters as appropriate. I am not sure what type txtEmployeeNumber, txtStartD1, txtLunchD1, txtOtherD1, txtFinishD1 are. If you are saving employee number, why save name parts?

I prefer CurrentDb.Execute.

Hi June7


The existing code is working, I am looking to convert it to a loop, so instead of 14 variations of that code repeating, I can have simple loops to manage.


Thank you for your input, i will take a look at your code to see if it works. I also found this.


For counter = 1 To 5
strSDate = "StartDate" & counter 'variable to increment control name/number by one
strEDate = "EndDate" & counter

If IsDate(Me.Controls(strSDate)) And IsDate(Me.Controls(strEDate)) Then
sqlUpdateTable = "UPDATE DateRange SET DateRange.StartDate = " & _
Format(Me.Controls(strSDate), "#yyyy-mm-dd#") & _
",DateRange.EndDate = " & Format(Me.Controls(strEndDate), "#yyyy-mm-dd#")

DoCmd.RunSQL sqlUpdateTable

endif

Next counter
~~~~~


So I will try them all, please if anyone else has any idea, please let me know.



Terry

Interesting, I would have expected code with field names without full form path reference inside quotes to fail. I know RunSQL can resolve embedded references but thought it required full path.

undertm said:

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);")

Click to expand...

If It was me, I wouldn't store Given_Name, Family_Name in the tblTimesheets.
You have Employee_ID and that's enough. and with a query you can pull the names from employee table.
Search this forum or google for normalization database.

I wouldn't also choose Class & Module as table's field names.

Hi June7


The code did not work. It comes up with Syntax error, its a bit beyond my skills to break it down.



Hi Tera


Yes, i will simply the code once I get it running. Just for the moment using it to verify the data.


Terry

Can't you input the data to a temporary table, and then write a query to load it to your work records?

What exactly is the information you are keying in. Is it already in a spreadsheet or something?

Dave

Take a look at the image attached above.

Terry

I think it's doable but since you already have it working what would be the benefit?
To consolidate you need to combine what is common into variables. Since 1st one is unique I would just run that then start my loop at 2. Also, 2nd "phase" seems to have exact same sql start but not all ending parts are the same, which would mean you'd need a SELECT CASE block to deal with those variations between loop 3 and 4 for instance. Because of that I'd say there is little consolidation to be had.
BTW - "didn't work is of zero help to those trying to help you.

It's just a bunch of concatenation. Provide your attempted code for analysis.

Could use a string variable to hold the compiled SQL statement then Debug.Print the variable to see result.

I did have typo, missing apostrophe:

.txtStartD1 & "," & .txtLunchD1 & "," & .txtOtherD1 & "," & .txtFinishD1 & ",'" & _

Hi Terry,

Some things to look at doing;
1) Use a string variable to hold your SQL. Not a requirement from ACCESS, but a very useful tool for debugging.
2) As much of your SQL is going to be the same, have ONE formatted constant that holds your fixed portion and just add on what is going to change.

This does mean your code starts looking more like

in your code, you would then see something that looks like

This cut down on a lot of your redundancy. It also means you can do a debug.print of your asSQL or a msgbox so you can see exactly what you are passing for debugging.

3) I'd look at redoing your logic for this. As is it looks like you are trying to recreate an excel spreadsheet for your interface. I'd personally allow an end user to add just the data they need without having to go through a lot of non-essential controls.

Hi June7


Get the following syntax error, see attached image.



Dim i As Integer
i = 1

' Input 14 days Timesheet dataset

Do While i <= 14 'this will do x rotations

For i = 1 To 14

With Me
CurrentDb.Execute "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 & "," & _
("txtProjectR" & i) & "','" & ("txtClassR" & i) & "','" & ("txtModuleR" & i) & "','" & ("txtActivityR" & i & "D1") & ")"

End With

Next i

Loop

Dim i As Integer
i = 1

' Input 14 days Timesheet dataset

Do While i <= 14 'this will do x rotations

For i = 1 To 14

With Me
CurrentDb.Execute "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 & "," & _
.controls("txtProjectR" & i).value & "','" & .controls("txtClassR" & i).value & "','" & .controls("txtModuleR" & i).value & "','" & .controls("txtActivityR" & i & "D1").value & ")"

End With

Next i

Loop

would still like to see a sample database per crosspost

Two things,
First, please use code tags around your code (the "#" button does this for you)
Second, declare a string, fill the string with your SQL code, then review what you are about to pass in your .execute so you can verify exactly what is the problem.

We don't have your form to verify what controls are/should be referenced. Seeing exactly what will be passed to the SQL engine first will allow you to see if here are obvious problems.

cross posted

http://www.accessforums.net/showthread.php?t=77071