Updating ADO Recordset

[scott]

i have a table with dayname, daydate. below is a sub function that i'm
trying to pass a date and have it loop through table, increasing the date by
1 day. i'm getting an error with my counter variable. any ideas?


Sub UpdateWeekDates(sDate As Date)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim sSQL As String
Dim iCount As Integer

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from t_dow")

iCount = 0

Do Until rst.EOF

rst!dow_Date = sDate ' + iCount
rst.Update
iCount = iCount + 1
Loop

rst.Close
Set rst = Nothing

End Sub

 

[Douglas J. Steele]

You've left out the .MoveNext statement inside the loop, so you've got an
infinite loop. As soon as you've gone through the loop 32,767 times, iCount
becomes too large for an integer.

But note that you're not going to actually be changing the date even once
you make that fix: your assignment statement is

rst!dow_Date = sDate ' + iCount

so that all you're doing is setting dow_Date to whatever you pass to the
routine. If that's actually your intent (i.e.: to set the date field to the
same date for every record), you'd be much better off using an Update
statement.