Loop thru form fields and plug value into query

[xuanb]

I want to avoid repeating a query 11 times (number of form fields to be populated). My idea is to create a collection and loop thru the elements so the query can be modified on the fly.
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

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

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. But as explained I need Forms!frmCAS!fra... to change for each Form field.

Can anyone please help!?

 

[PauloRico]

Try leaving the single quotes out of the CompName evaluation.