Open Access Recordset

[FinChase]

I know this is really basic, but it's driving me crazy...

I am trying to open a recordset, and based on the data in
one field, save a value in another field in the same
table. I need to do this for each record in the table.

This is the code I have written:

Dim DB As Database
Dim RS As Recordset
Dim Col As Field
Dim Col2 As Field
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblPTOBenefits")
Set Col = DB.OpenRecordset("tblPTOBenefits").Fields
("LOS")
Set Col2 = DB.OpenRecordset("tblPTOBenefits").Fields
("YearlyPTOBenefit")

For Each Col2 In RS
If Col > 0 Then
Col2 = "$2000"
Else
Col2 = "$0"
Exit For
End If
Next

It is giving me a Data mismatch error and highlighting the
line "Set RS = DB.OpenRecordset("tblPTOBenefits")".

Can someone help me here?

Thanks, Lee

[WorkHorse]

ADO replaced DAO. Access 2000 and beyond doesn't seem to like a record set. I think this is what you want:

Dim DB As Database
Dim RS As Variant
Dim i as Long
 
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblPTOBenefits")
 
For i = 1 To RS.RecordCount
    With RS
        .Edit
        If .Fields("LOS") > 0 Then
            .Fields("YearlyPTOBenefit") = "$2000"
        Else
            .Fields("YearlyPTOBenefit") = "$0"
        End If
        .Update
        .MoveNext
    End With
Next

[Ecniv]

You need to set the references to DAO to use Dao (and remove ADO if req.

You should be able to run Workhorses solution, or if you don't have too many records (like <10000) you could just run an Update Sql statement.

Update [tblPTOBenefits] ( [YearlyPTOBenefit] )
Select iif(nz([tblPTOBenefits].[LOS],0)>0,2000,0) as CalcResult from [tblPTOBenefits]

There are many ways to do what you need, use which ever you feel more comfortable with.


Vince