Update single record vrs. all records

[VBAWTB]

I am trying to update a single record at a time through a loop. But on the first trip through the loop it tries to update all the records at once. I am trying to update a list of records that have a start date and end date, starting with a user given date that is stored in a table. Here is my code:

Dim dbsSubwayDB As DAO.Database
Dim rstSettings As DAO.Recordset
Dim rstWEDates As DAO.Recordset

Set dbsSubwayDB = CurrentDb
Set rstSettings = dbsSubwayDB.OpenRecordset("tblSettings")
Set rstWEDates = dbsSubwayDB.OpenRecordset("tblW_E_Dates")
WkEndngDay = rstSettings!WeekEndingDay

Sql = "UPDATE tblsettings SET Weekendingday = " & cboWeekDay.ListIndex & ";"
DoCmd.RunSQL Sql

StartDate = #1/1/2006# + WkEndngDay
rstWEDates.MoveFirst

For I = 1 To 8
EndDate = StartDate + 6
Sql2 = "UPDATE tblW_E_Dates SET StartDate = " & StartDate & ", EndDate = " & EndDate & ";"
DoCmd.RunSQL Sql2
EndDate = EndDate + 7
StartDate = StartDate + 7
rstWEDates.MoveNext
Next I

The red is the update that is updating everything at once. There are no errors, just not doing what I want it to do...
Oh...and all I'm getting in my table is a time with no date (12:00:03 AM)

 

[plog]

You need to add a WHERE clause to your SQL statement. Right now your telling it to update all the columns, you need to tell it how to identify the one row you want to update.

If you need specific help in constructing a WHERE clause for your query, tell me in simple words how to identify which row it should update.

 

[VBAWTB]

I know how to do a where clause, but essentially it starts at the first record and continues until the last record (in this case 783 records). I tried pointing it to the first record and then doing a .movenext inside the loop, but that isn't working. The where clause would have to be different each time the loop runs...

 

[plog]

Ahh the ole first, last, next misconception.

There is no order to data in tables. No first record, no last record, no 247th record. Order exists in your data only when you tell a query to give order to your data using an ORDER BY clause in a SELECT query.

That means you need to create a query that uses an ORDER BY clause to order your data, use VBA to pull that query's data into a record set (instead of a table like you are now), loop through each record where you obtain a unique identifier for that record, construct an UPDATE query using that unique identifier in your WHERE clause of that UPDATE statement and execute that UPDATE query to change that one specific row of data.

 

[VBAWTB]

Ok, I got the record order worked out thanks to your advise. But do I need to put them all into a recordset before I can change the values? The problem I am having now is that the data is somehow getting lost when it reaches the table. Here is my current version of the code:

StartDate = #1/1/2006# + WkEndngDay
rstWEDates.MoveFirst
Counter = 1

For I = 1 To 4
EndDate = StartDate + 6
Sql2 = "UPDATE tblWEDates SET StartDate = " & StartDate & ", EndDate = " & EndDate & _
" WHERE ID = " & Counter & " ORDER BY ID;"
DoCmd.RunSQL Sql2
Counter = Counter + 1
EndDate = EndDate + 7
StartDate = StartDate + 7
Next I

I have put watches on StartDate and EndDate and the Sql string and then stepped through, and they all have the correct values as it loops around. But the table just receives a '0', or in date format '12/30/1899'. Please help =|

 

[plog]

Not too be too nit picky, but you don't need an ORDER BY clause on your UPDATE statement. I don't think that's the cause of your error though. My guess it has to to with this line:

StartDate=#1/1/2006# + WkEndngDay

It sounds like its not evaluating to a date. I would first declare StartDate as a Date variable and then use the DateAdd function like this:

Dim StartDate AS Date
StartDate=DateAdd("d", WkEndngDay, "1/1/2006")

The above assumes WkEndngDay is a number and is the number of days you want to add to 1/1/2006. If its not, check out the DateAdd function here: http://www.techonthenet.com/excel/formulas/dateadd.php

It looks like you increment the EndDate and StartDate variables inside a loop. I would change those lines to increment those variables using the DateAdd function as well.

Last, one trick I use when debugging an SQL action statement is to display it instead of executing it so I can verify that the text inside the sql variable (your variable is called Sql2) is what I want it to be. So instead of this line:

DoCmd.RunSQL Sql2

I would use this:

MsgBox(Sql2)

Just to see exactly what data is in it.

 

[VBAWTB]

I like using the dateadd better, thanks... so I made those changes but I am getting the same result. I mentioned earlier that I had set up watches on those variables and stepped through the code, and the variables were all correct as it hit the docmd.run sql. I'm really stumped on this one...

StartDate = DateAdd("d", WkEndngDay, "1/1/2006")
rstWEDates.MoveFirst
Counter = 1

For I = 1 To 4
EndDate = DateAdd("d", 6, StartDate)
Sql2 = "UPDATE tblWEDates SET StartDate = " & StartDate & ", EndDate = " & EndDate & _
" WHERE ID = " & Counter & ";"
DoCmd.RunSQL Sql2
Counter = Counter + 1
StartDate = DateAdd("d", 7, StartDate)
EndDate = DateAdd("d", 7, EndDate)
Next I

 

[plog]

Sorry I didn't catch it before, but I think you need either single quotes and/or the pound symbol around your dates in your SQL:

Sql2 = "UPDATE tblWEDates SET StartDate = '" & StartDate & "', EndDate = '" & EndDate & "' WHERE ID = " & Counter & ";"

Its hard to see, but there are ' in the string before where you put the StartDate and EndDate values.

 

[keikoebe]

I like using the dateadd better too

 

[VBAWTB]

Thank you Plog! That was what was missing. It works great! Here is my final code for anyone who wants it...

For I = 1 To 783
EndDate = DateAdd("d", 6, StartDate)
WeekNum = Excel.WorksheetFunction.WeekNum(StartDate)
Sql2 = "UPDATE tblWEDates SET WeekNum = '" & WeekNum & "', StartDate = '" & StartDate & "', EndDate = '" & EndDate & _
"' WHERE ID = " & Counter & ";"
DoCmd.RunSQL Sql2
Counter = Counter + 1
StartDate = DateAdd("d", 7, StartDate)
EndDate = DateAdd("d", 7, EndDate)
Next I