CODEWITHSUNDEEP
vbaforeachms-access-2010
Bryan
Bryan

Reputation: 1893

How to do a "For Each" Distinct record of a column in MS-Access 2010?

I am trying to create a VBA code that will run through each distinct record for a Table1.FieldName Value. I have a Field called "Collector" and for an example lets say the the result of a query:

Select Distinct Collector From Table1

is the following

Smith Road
Fifth Ave
Samford

I would like to be able to use a for each and each run through have a variable that is assigned each of the resulting records in turn.

If I have left any needed information out or if I have not been clear on anything please let me know and I will try to give more information.

Thanks in advance.

Upvotes: 0

Views: 4599

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Dim db As DAO.Database 
Dim rs1 As DAO.Recordset 
Dim v as string

  Set db = CurrentDb()  
  Set rs1 = db.OpenRecordset("Select Distinct Collector From Table1")  

  Do While Not rs1.EOF 
     v = rs1.Fields(0).Value
     'do something with v
     rs1.MoveNext 
  Loop 


  rs1.Close

Upvotes: 1

Related Questions