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

assign, loops, ms-access, variables, vba

Solution

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

Problem

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 !

Original source