How to move to next field in dao/vba

[apna]

I'm trying to process a table like this:
read all records in table, process field1, write results to a new table.
then
read all records in table, process field2, write results to the new table.
then
etc, etc, etc. (note the field is changing each time.)

My code so far is this:
Private Sub readrecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("results")

rst.MoveFirst
Do While Not rst.EOF
If rst!Q01 = 1 Then
BossSigS = BossSigS + 1
BossSigC = BossSigC + 1
OtherS = OtherS + 1
OtherC = OtherC + 1
End If
If rst!Q01 = 2 Then
BossSomS = BossSomS + 2
BossSomC = BossSomC + 1
OtherS = OtherS + 1
OtherC = OtherC + 1
End If
rst.MoveNext
Loop


So after the Loop I need to move to the next field (Q02) and then reread all
of the records again. I can reread the records but how can I make rst!Q01
change to rst!Q02 and so forth rst!Q03......rst!Q04......all the way to
question 56: rst!Q56?

thanks

 

[Douglas J Steele]

You're not going to like this answer, but having fields named Q01, Q02,...,
Q56 is almost always a sign that your database hasn't been properly
normalized. Rather than 56 separate fields in a single row, you probably
should have 56 separate rows, probably in another table related to your
first table. The adage "rows are cheap, fields are expensive" applies here.
What are you going to do when you suddenly have a 57th question?

If this is a survey-type application, check Duane Hookom's "At Your Survey"
for an example of a well-designed application.
http://rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[Guest]

Although Douglas is absolutely correct and you should rethink your design.
For your further edification, the technigue for doing this sort of thing is
to use the ordinal position of the field rather than the field's name. Then
you can use a For Next loop to do this:

In this example, it will look at every field in the table. If there are
fields before or after the fields you want to process, you will have to
change what value lngCtr starts with and what value lngTotFlds stops at.
Remember, the field numbers start with 0 as the first field. Now, if there
are fields in between the fields you want to process, you will have to test
for and skip over. For example you have one field you don't want to process.
it is the 11th field in the table, so to omit it from your processing you
would do:
If lngCtr <> 10 then
'Do the processing
End If

Private Sub readrecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngFldCtr as Long
Dim lngTotFlds as Long

Set db = CurrentDb
Set rst = db.OpenRecordset("results")
'Good Idea follows
If rst.RecordCount < 1 Then
MsgBox "No Data To Process"
Exit Sub
Else
rst.MoveLast
rst.MoveFirst

lngTotFlds = rst.RecordCount -1

Do While Not rst.EOF
For lngFldCtr = 0 To lngTotFlds
If rst.Fields(lngFldCtr) = 1 Then
BossSigS = BossSigS + 1
BossSigC = BossSigC + 1
OtherS = OtherS + 1
OtherC = OtherC + 1
End If
If rst.Fields(lngFldCtr) = 2 Then
BossSomS = BossSomS + 2
BossSomC = BossSomC + 1
OtherS = OtherS + 1
OtherC = OtherC + 1
End If
Next lngCtr
rst.MoveNext
Loop
End If

 

[apna]

Thanks Klatuu, that's what I needed. I was about to just copy the code 56
times.

Thanks Douglas. That Survey database is good. And yes, my database is also
for a Survey we are doing.