How to have a variable in a recordset field name

[Paul]

I have a table with the field name like F1, F2, F3 and so on. I create a vba
recordset to the table and loop through each record in the table. I also
have another loop inside to loop through each field names on that record.
The following is the code, however it does not recognize the field name
variable like rs!f(i).

Private Sub UpdateStatus()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Scantron", dbOpenDynaset)
If Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next
j = 0
For i = 1 To 10
With rs
If rs!f(i) <> 0 Or rs!f(i) <> Null Then
j = 1
Else
j = 0
End If
End With
j = j + j 'Sum of the value j to determine how to update the
Status field
Next i
Debug.Print j
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing

'ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox err.Description
Resume PROC_EXIT
'ErrorHandlerEnd
End Sub

 

[strive4peace]

Hi Paul

instead of treating the fieldnames like an array (which you have not
initalized or assigned values to), I believe what you want to do is
something like this (assuming your fields are all numeric):

With rs
for i = 0 to rs.fields.count-1
If nz(rs("f" & (i-1),0)) <> 0 Then
j = 1
Else
j = 0
End If
next i
End With


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*