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 !

Answer 1

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