loop through table columns

[CD Tom]

I have a table with the following columns class1 class2 class3 class4 etc I
want to set up a loop so I can check each column to see if it's greater than
0. I thought about creating a ver with Class and then add the number to the
end then just add 1 to the number for each loop. The problem is the ver is
showing as a string "class1" and will not reflect the column in the table.
This is new to me so I hope someone has a simple answer to this. Thanks for
your help

 

[Ofer]

I'm not sure if you want to check the columns in a form or in a table

In a table, after openning recordset, you can use
Dim I As Integer

For I = 1 To 10
If MyRecordSet("class" & I) > 0 Then

End If
Next I
===============
In a form
Dim I As Integer

For I = 1 To 10
If Me("class" & I) > 0 Then

End If
Next I
====================
This example will loop 10 times, if you have more or less, then change the 10.
I hope that what you are looking for

 

[Klatuu]

Howdy, again CD. Here is a sample routine you can modify to suit your needs.
This example loops through each column in each row of a table looking for
values greater than 0. (note, it does not work for Colt copies

Sub CheckValues()
Dim rst As Recordset
Dim fld As Field

Set rst = CurrentDb.OpenRecordset("cisattributetable")
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
End If
Do While Not rst.EOF
For Each fld In rst.Fields
If fld > 0 Then
Debug.Print fld
End If
Next
Loop
rst.Close
Set rst = Nothing
End Sub

 

[Douglas J Steele]

You're probably not going to like this, but having fields named like that is
indicative of the fact that your database hasn't been properly normalized:
that's called a repeating group.

You should have two tables: one that contains the information that's common
to all of the class entries, plus a second one that points back to that
first table, and has one row for each different class, rather than one
column for each possible class. An immediate drawback to your design is what
are you going to do if you have to add class10 (or whatever one more than
your current maximum is)?

Jeff Conrad has a bunch of good references regarding normalization at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

To answer your specific question, though, you'd be able to refer to the
fields like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intLoop As Integer
Dim strField As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("MyTable")
For intLoop = 1 To 9 ' or whatever your range is
strField = "Class" & intLoop
If rsCurr.Fields(strField) = 0 Then
MsgBox "Class " & intLoop & " is zero"
End If
Next intLoop

 

[CD Tom]

Thanks, and it does work with Colt copies.

Howdy, again CD. Here is a sample routine you can modify to suit your needs.
This example loops through each column in each row of a table looking for
values greater than 0. (note, it does not work for Colt copies

Sub CheckValues()
Dim rst As Recordset
Dim fld As Field

Set rst = CurrentDb.OpenRecordset("cisattributetable")
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
End If
Do While Not rst.EOF
For Each fld In rst.Fields
If fld > 0 Then
Debug.Print fld
End If
Next
Loop
rst.Close
Set rst = Nothing
End Sub