title: How do I loop through a MS Access recordset with VBA and assign variables to the results?
body: <p>My VBA is quite rusty and I seem to be drawing a blank. I am using MS Access 2010 if it matters.</p>
  
  <p>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 ?</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>Many Thanks !</p>