FOR.....NEXT loop problem 1

[Guest_imported]

We keep track of our payroll information in a table called "TblWeeklyHours". So, every time an user clicks the "Add a New Week" button, a row is added into the table "TblWeeklyHours", where the Date field is automatically filled in based on the last entered date for a particular school. The new date added is a week from the last entered date.
But Now we want to insert 3 rows into the table "TblWeeklyHours", so I added the FOR...NEXT loop into the code. It inserts 3 rows into the table, but it doesnt keep the date same in those 3 rows.
Is there a way by which when the user clicks the "Add a New Week" button, 3 rows are inserted with the same date based on a week from what the last entered date was.
I might have placed the FOR...NEXT loop in the wrong section of the code but I dont know how to debug since I am new to access programming.
Any input will be appreciated. Thanks.


Here is the code:
-----------------


Private Sub Command6_Click()
Dim db As Database
Dim strDate As String
Dim rstdate As Recordset
Dim rstAddDate As Recordset
Dim strAddDate As String
Dim strPasteSchool
Dim x As Date
Dim y
Dim c

strPasteSchool = " WHERE (((TblWeeklyHours.SchoolLink)=" & SchoolLink & " ))"

y = MsgBox("Click Yes or hit enter to create a new week.", vbYesNo, "Create new week&quot
If y = vbYes Then
Set db = CurrentDb()

For c = 1 To 3

strDate = "SELECT TOP 1 TblWeeklyHours.SchoolLink, TblWeeklyHours.WeekOf, TblWeeklyHours.Department, TblWeeklyHours.HrsSched, TblWeeklyHours.OTSched FROM TblWeeklyHours" & strPasteSchool & " ORDER BY TblWeeklyHours.WeekOf DESC"

strAddDate = "SELECT * FROM TblWeeklyHours" & strPasteSchool & ""
Set rstAddDate = db.OpenRecordset(strAddDate)

Set rstdate = db.OpenRecordset(strDate)


x = rstdate!WeekOf


rstdate.AddNew
rstdate!WeekOf = DateAdd("d", 7, x)
rstdate!SchoolLink = NoCommaSchoollink
rstdate.Update
rstdate.close

Next c

db.close
Me.Requery
Else
End If


End Sub

 

[gwinn7]

I am not sure if I am on the right track here, but I would remove any unnecessary items from your FOR LOOP and create a separate function soley for the purpose of adding the fields. You can then call that function from the click event (Why? No big reason, just it seems clearer).

There is no need to re-execute commands when it only takes one time to run them. Here is a general outline of how I would structure the code....

Private Function MakeNewWeeks()

Dim dNewDate as Date
Dim rcNewWeek as Recordset
Dim icount as Integer

On Error Goto Handler:

' Determine new date based on the last date entered in that table...
dNewDate = DateAdd(&quot;d&quot;, 7, DMax(&quot;Weekof&quot;, <yourtable>))

Set rcNewWeek = Currentdb.OpenRecordset(<your SQL code>)

With rcNewWeek

For icount = 1 to 3
' Add your record and apply the nNewDate
Next icount
.Close
End With

Set rcNewWeek = Nothing
Exit function

Handler:
msgbox <your error message>

End Function

Did this help?

Gary
gwinn7

 

[Mongooses]

you might want to try:

strDate = &quot;SELECT TOP 1 Tb...
strAddDate = &quot;SELECT * FROM TblWeek...

Set rstAddDate = db.OpenRecordset(strAddDate)
Set rstdate = db.OpenRecordset(strDate)
x = rstdate!WeekOf

For c = 1 to 3
rstdate.AddNew
rstdate!WeekOf = DateAdd(&quot;d&quot;, 7, x)
rstdate!SchoolLink = NoCommaSchoollink
rstdate.Update
Next

rstdate.close

HTH,
JC

 

[Guest_imported]

Gary:
Your solution makes sense and its an efficient way to write code. But since I'm just troubleshooting and I wanted a quick fix I followed JC's method. Thanks a bunch for taking the time and effort to help me out.


JC:
I made the changes you suggested and the code works smoothly now. I appreciate your help.

Have a great weekend guys.