Update fields in table using recordset

[dcollins]

I am trying to loop through a recordset and update a currently empty field in a table with something depending on another field. After hours of frustration I can't seem to get it to figure out what I am doing wrong. Only the first record is updated though I know it is cycling through all the records. Here is the code, any help would be appreciated.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim count As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TblTransactions", dbOpenDynaset)
With rst
rst.MoveFirst
count = 0
Do Until rst.EOF
Select Case rst!PlanNumber
Case Is = 21
rst.Edit
rst!Subcode = 20351
rst.Update
Case Is = 23
rst.Edit
rst!Subcode = 20352
rst.Update
Case Is = 25
rst.Edit
rst!Subcode = 20353
rst.Update
Case Else
rst.Edit
rst!Subcode = "error"
rst.Update
End Select
count = count + 1
rst.MoveNext
Loop
Me!testtxt = count
End With
rst.Close

 

[dcollins]

You're right about the null. My test data did not have any nulls but that would have been a problem. I inserted breakpoints in the code and watched it go through all records. Also the count field shows the correct number of records. And I just realized what my problem is....I had originally made the field in the table a combo box because I was going a different route. So, magically when I change it to text box it works. It never fails as soon as you ask for help, the answer appears. Thank you for your response!

 

[Crilen007]

"It never fails as soon as you ask for help, the answer appears. Thank you for your response!"


Thats happened a lot on these boards to me lol.