Place a concatenate string for the Name parameter in OpenRecordSet

[dhlao]

I know the Title is difficult to understand. Please see the example code below
I want to run a serial of SQL in a for loop.

Dim rst As Recordset
Dim qryStr1, qryStr2, qryStr3 As String
Dim i As Integer
qryStr1 = "SELECT * FROM tableA WHERE blah blah blah"
qryStr2 = "SELECT * FROM tableB WHERE blah blah blah"
qryStr3 = "SELECT * FROM tableC WHERE blah blah blah"
For i = 1 to 3
  Set rst = CurrentDb.OpenRecordset("qryStr" & i)
  blah blah blah
Next i

It show error 3078, The Microsoft Access database engine cannot find the input table or query 'qryStr1'.

Of course, 'qryStr1' does exist. The only different is the 1 inside OpenRecordset is a concatenate string.

Is it possible to do this ?

 

[arnelgp]

it is evaluating your expression as literal string OpenRecordSet("qryStr1"), rather than OpenRecorset(qryStr1)

use Tempvars instead:

' create tempvars
Tempvars("qryStr1") = "SELECT * FROM tableA WHERE blah blah blah"
Tempvars("qryStr2") = "SELECT * FROM tableB WHERE blah blah blah"
Tempvars("qryStr3") = "SELECT * FROM tableC WHERE blah blah blah"
For i = 1 to 3
Set rst = CurrentDb.OpenRecordset(Tempvars("qryStr" & i))
blah blah blah

' remove tempvars
Tempvars.Remove "qryStr" & i
Next i

 

[dhlao]

Thanks arnelgp.
This is my 1st time to use TempVars()