do loop with if then Access 2002

[Guest]

i want to loop through records and assign numbers for each. records are in
order and every group of records has one with a number 1 in "varnum". i want
to fill in a a field, "aftervar", with the 1 and then with 2,3,4,5 and so on
until it reaches next 1, stopping when it hits "DONE". it keeps saying "Loop
without Do"? i assume you can you do "if then" within loops? may not be
doing this correctly!! thanks in advance!



Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim tempnum As Variant
tempnum = 1

Set rs = db.OpenRecordset("newtable", dbOpenDynaset)
With rs
..MoveFirst
Do Until rs.EOF
..Edit
If rs!varnum = 1 Then
rs!aftervar = 1
Else
If rs!varnum Is Null Then
rs!aftervar = tempnum + 1
End If
..Update
tempnum = rs!aftervar
..MoveNext
If rs!varnum = "DONE" Then
Exit Do
End If
Loop
End With
End Sub

 

[Rick Brandt]

i want to loop through records and assign numbers for each. records
are in order and every group of records has one with a number 1 in
"varnum". i want to fill in a a field, "aftervar", with the 1 and
then with 2,3,4,5 and so on until it reaches next 1, stopping when it
hits "DONE". it keeps saying "Loop without Do"? i assume you can you
do "if then" within loops? may not be doing this correctly!! thanks
in advance!



Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim tempnum As Variant
tempnum = 1

Set rs = db.OpenRecordset("newtable", dbOpenDynaset)
With rs
.MoveFirst
Do Until rs.EOF
.Edit
If rs!varnum = 1 Then
rs!aftervar = 1
Else
If rs!varnum Is Null Then
rs!aftervar = tempnum + 1
End If
.Update
tempnum = rs!aftervar
.MoveNext
If rs!varnum = "DONE" Then
Exit Do
End If
Loop
End With
End Sub

If you indent your code like I have above you would see that you have an...

Else
If

....that should either be...

ElseIf (all one word)

or else should have an additional End If in there somewhere.