How do I loop through a MS Access recordset with VBA and assign variables to the results?

Question

My VBA is quite rusty and I seem to be drawing a blank. I am using MS Access 2010 if it matters.

I have the results of a query that return two fields (Field1 is text and Field2 is double) and five records and I want to assign Field2 values to five different variables based on what is in Field1. To me this is some kind of case statement - how would I do this in VBA on the recordset ?

  Private Sub test()

  Dim myRS As DAO.Recordset
  Dim db As Database
  Dim strSQL As String
  Dim v1, v2, v3, v4, v5 As Double

  ' Pretend strSQL is a different query that gives five records

  Set dbs = CurrentDb
  strSQL = "SELECT Field1, BigNumber FROM tmp1"
  Set myRS = dbs.OpenRecordset(strSQL)

  Do While Not myRS.EOF
      v1 = ?         ' I want v1 = Field2 when Field1="A"
      v2 = ?         ' I want v2 = Field2 when Field1="B"
      v3 = ?         ' I want v3 = Field2 when Field1="C"
      v4 = ?         ' I want v4 = Field2 when Field1="D"
      v5 = ?         ' I want v5 = Field2 when Field1="E"
  Loop

  End Sub

Many Thanks !

What are you doing with the variables v1-v5? I'm thinking I have a better solution than looping through the dataset, but I'd need to know what the end game is to know for sure. — JohnFx
– JohnFx, Commented Feb 20, 2013 at 16:48

score 5 · Accepted Answer · 2013-02-20 16:15:13Z

5

Private Sub test()

Dim myRS As DAO!Recordset
Dim db As Database
Dim strSQL As String
Dim v1, v2, v3, v4, v5 As Double

' Pretend strSQL is a different query that gives five records

Set dbs = CurrentDb
strSQL = "SELECT Field1, BigNumber FROM tmp1"
Set myRS = dbs!OpenRecordset(strSQL)

Do While Not myRS!EOF
    Select Case myRS.Fields(0)
    Case "A"
        v1 = myRS.Fields(1)
    Case "B"
        v2 = myRS.Fields(1)
    Case "C"
        v3 = myRS.Fields(1)
    Case "D"
        v4 = myRS.Fields(1)
    Case "E"
        v5 = myRS.Fields(1)
    End Select
Loop

edited Feb 20, 2013 at 16:15

answered Feb 20, 2013 at 15:37

user2063626

Sign up to request clarification or add additional context in comments.

2 Comments

user918967 Over a year ago

Hi, I get an error that Field1 is a Method or data member that cannot be found.

user918967 Over a year ago

I have to remove the ! from the code is that a problem ? It still gives me the same error.

Octahedron · Accepted Answer · 2013-07-04 15:20:52Z

1

I think you are missing MoveNext:

Do While Not rec.EOF
    rec.MoveNext
Loop

edited Jul 4, 2013 at 15:20

Octahedron

8992 gold badges18 silver badges32 bronze badges

answered Jul 4, 2013 at 14:48

gunslingor

1,53516 silver badges38 bronze badges

Comments

Fionnuala · Accepted Answer · 2013-02-20 15:32:46Z

0

In this case, you may as well just use DlookUp and forget about the recordset:

 v1 = DLookUp("BigNumber","tmp1","Field1='A'")
 v2 = DLookUp("BigNumber","tmp1","Field1='B'")

 <...>

answered Feb 20, 2013 at 15:32

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

4 Comments

user918967 Over a year ago

Well, the problem is that my SQL statement is much more complicated than pulling data from one table, I was just making a simplification for the question.

Fionnuala Over a year ago

If your sql is more complicated, it should be possible to build it to return 5 columns, or to return the data in an unambiguous way. Nearly anything is better than walking a recordset.

user918967 Over a year ago

If I return five columns then I still need to assign the results to five variables, or I just make 5 queries and assign the results to variables - it is a lot more typing that is for sure.

Fionnuala Over a year ago

If you return 5 columns, you are not walking a recordset, v1=col1, v2=col2, a lot less typing.

Collectives™ on Stack Overflow

How do I loop through a MS Access recordset with VBA and assign variables to the results?

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related