do loop through a table

  • Thread starter Thread starter Tamlee
  • Start date Start date
T

Tamlee

I want to go through records in a table "TESTLIST" and assign 1,2,3... to
Field1 based on whether there Field2 is not Null. if Field 2 is Null Field1
should start at 0.

Field1 Field2
1 Mr. and Mrs. John Smith
2 1 Park Avenue
3 New York, NY 10012
0
1 Mr. Joe Blow
2 10 Madison Avenue
3 New York, NY 10020
0
1 Ms. Jane Doe
2 22 Prairie Way
3 Chicago, Illinois 33233
DONE

I keep getting an error 3020 saying I'm updating without Addnew or Edit?
my sub is:
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim numbervar As Variant
numbervar = 0
Set rs = db.OpenRecordset("TESTLIST", dbOpenDynaset)
With rs
..MoveFirst
Do Until rs.EOF
If rs![Field2] <> Null Then
numbervar = numbervar + 1
Else
numbervar = 0
End If
rs!Field1 = numbervar
rs.MoveNext
If rs![Field2] = "DONE" Then
Exit Do
End If
Loop
End With
End Sub



I appreciate any help! this seems like it should be so easy!! (If at all
possible give specifics, not general suggestions.)
Thanks!!
 
Hi,

You cannot assign a new value to an existing record in VBA unless you invoke
rs.Edit before you assign the new value. You cannot assign a value to a new
record until
you have created that record with rs.addnew.

So before you invoke this line

rs!Field1 = numbervar
you need to have a line like
rs.Edit

otherwise you cannot change the value

Regards

Kevin
 
Back
Top