MS Access - Recordset not updating with new value

[butters27]

Hi,

I am running the following code which works ok the first time through the loop. When it gets to the next run through however, it stops at the text highlighted in red below. The value stored is still the old value. For some reason the sql does not update the recordset even though the sql is definitely run the 2nd time. Any ideas???

Thanks.


Private Sub AddDates_Click()
Dim retvalue As Integer, i As Variant, t As Variant, strSQL As String, rst As Recordset, k As Variant, db As Database, myQuery As QueryDef

For i = Int(CVDate(Me!StartDate)) To Int(CVDate(Me!EndDate))

If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
DoCmd.RunSQL "INSERT INTO [Date]([Date-Date], [Date-valid]) VALUES ('" & Format(i, "dd/mm/yyyy") & "', TRUE )"

Set db = CurrentDb

strSQL = "SELECT [Date-Key] FROM [Date] WHERE [Date-Date] = #" & i & "#"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
k = ![Date-key]
End With
rst.Close

For t = #9:00:00 AM# To #4:00:00 PM# Step #1:00:00 AM#
DoCmd.RunSQL "INSERT INTO [Timeslot]([Timeslot-date-key], [Timeslot-time]) VALUES ('" & k & "', '" & Format(t, "hh:mm") & "')"
Next t

Set rst = Nothing
Set k = Nothing

End If

db.Close
Next i

End Sub