CODEWITHSUNDEEP
ms-accessvba
JDT
JDT

Reputation: 107

How to reference certain column fields of an MS Access table

I have a Microsoft Access 2010 table with 3 columns.

The first column contains the numerical sequential primary key ID The 2nd contains site names. The 3rd contains yes/no for each site.

I want to do a loop through the 2nd column to perform an action for every field in column 2 where the entry in column 3 is 'Yes'.

The code I am trying to use which isn't working for me is 

Set rs = db.OpenRecordset("table1")
Set rs2 = rs.Fields("Column2")
set rs3 = rs.fields("Column3")

For each fld in rs2
If rs3.fields = "Yes" then

"The action code would follow here etc etc"

Next fld


End if

Loop

The code doesn't seem to like "For each fld in rs2"

I would appreciate any help with this

Many Thanks

Upvotes: 0

Views: 4337

Answers (1)

Andre
Andre

Reputation: 27634

The first recordset already has all the fields.

Set rs = db.OpenRecordset("table1")
Do While Not rs.EOF
    ' Alternatively: rs("Column3")
    If rs!Column3 = "Yes" Then
       ' stuff
    End If
    ' Next record
    rs.MoveNext
Loop

But: what you want to do in a Recordset loop can most probably be done easier and more efficient in SQL with an UPDATE query.

Upvotes: 4

Related Questions