Loop thru collection to use as query criteria

[xuanb]

I have 10+ rows of textboxes that return calculated fields based on different criteria stored in the db. I want to avoid repeating a query 10+ times when the collumn name and the criteria change. So my idea is to create a collection and loop thru the elements to modify the query.
Here's what I came up with sofar:

Sub TestColl()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim sql As String, areaCol As String, frame As String, comp As String
Dim coll As New Collection, var As Variant

coll.Add "Grounds"
coll.Add "Parking"

Set cnn = CurrentProject.Connection

For Each var In coll

areaCol = "tblFacilities." & var & "Area"
frame = "Forms!frmCAS!fra" & var
comp = "Forms!frmCAS!txtComp" & var

'Returns the VALUES correctly
Debug.Print frame & vbCrLf & comp

sql = "SELECT " & areaCol & _
" FROM tblFacilities " & _
" WHERE (tblRatings.RatingID = " & frame & &quot AND " & _
"(tblComponents.CompName = '" & comp & "');"

'But NOT HERE
Debug.Print sql
rst.Open sql, cnn, adOpenStatic, adLockReadOnly
rst.Close

Next

End Sub

Debug.Print shows the PROBLEM:

SELECT tblFacilities.GroundsArea FROM tblFacilities WHERE (tblRatings.RatingID = Forms!frmCAS!fraGrounds) AND (tblComponents.CompName = 'Forms!frmCAS!txtCompGrounds');

Forms!frmCAS!fraGrounds is suppose to evaluate to an Integer and Forms!frmCAS!txtCompGrounds is suppose to evaluate to a String 'Grounds'.

Next time thru the loop the values for "Parking" should be plugged in etc...

If I write Forms!frmCAS!fraGrounds directly into the query instead of the variable "frame" it works. Looks like Access cannot expose the value of a variable at runtime.

Can anyone help!?