Do Loop

[aumstu]

This is probably a simple question but I cant figure it out. I am trying to shorten some code by using a Do Loop. What I have are Fields names (Access DB) that are named Q1-Q20 and instead of writing the same code 20 times I am hoping to do something like this.

Dim rstIn As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rstIn.Open "TableName", cnn, adOpenStatic, adCmdTable

Do Until rstIn.EOF

x = 1

Do until x = 22

question = rstIn!x


If question = “A” Then
Acount = ACount + 1	

QA(x) = Acount

ElseIf question = “B” Then
BCount = BCount + 1
QB(x) = BCount

End If

x = x +1

Loop

Loop

The code above does not recognize the x as a field name. I tried putting the x in parenthesis...but still no luck.

Does anyone have any suggestions? Thanks

 

[PHV]

A starting point (if the current form has controls named Q1 .. Q22):
Me.Controls("Q" & x)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

or
rstIn("Q" & x)

For x = 1 To 22
rstIn("Q" & x).Value = ...
Next x