Problem with looping through records

[ds_8805]

I have a code as follows that I have put behind a button:

Private Sub Command4_Click()
'Declare you variables
Dim dbs As Database
Dim rst As DAO.Recordset
Dim Counter As Integer
' set up a updateable recordset of your table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(cus, dbOpenDynaset)
' find number of records in your recordset
Counter = rst.RecordCount
' loop until run out of records
While Counter > 0
With rst
' sets your field to date for example
![CustomerID] = Date
End With
' moves to next record
rst.MoveNext
' one less record to go
Counter = Counter - 1
' start loop again
Wend
End Sub

It is throwing an error saying that it cannot find the table.However the table does exist. I am using access 2002. I hope someone can help me figure out what mistake I might have made.

Thank You!

 

[vbaInet]

Set rst = dbs.OpenRecordset([COLOR=Red]"cus"[/COLOR], dbOpenDynaset)

cus must be enclosed in quotes.

While Counter > 0

Suggestion, use this instead:

While Not rst.EOF

To update you need the Edit and Update methods. The record will not be editted without the Edit method and it will not update if you move to another record without calling the Update method. Look at the help file for definitions.

With rst
.Edit
' sets your field to date for example
![CustomerID] = Date
.Update

 

[ds_8805]

oooo thanks! it works perfectly fine! Thank u very much

 

[ds_8805]

I am just pasting my final code here in case someone else wants to use it
Private Sub Command4_Click()
'Declare you variables
Dim dbs As Database
Dim rst As DAO.Recordset
Dim Counter As Integer
' set up a updateable recordset of your table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("cus", dbOpenDynaset)
' find number of records in your recordset
'Counter = rst.RecordCount
' loop until run out of records
' While Counter > 0
While Not rst.EOF
With rst
' sets your field to date for example
If ![CustomerID] = Me!CustomerID Then
.Edit
![CustomerID] = Me!test
.Update
Counter = Counter + 1
End If
End With
' moves to next record
rst.MoveNext
' one less record to go
'Counter = Counter - 1
' start loop again
Wend
If Counter = 0 Then
MsgBox "No records updated"
Else
MsgBox Counter & " records updated."
End If
End Sub

 

[vbaInet]

oooo thanks! it works perfectly fine! Thank u very much

Goodie. You're welcome!