newbie needs help

[Jackske]

I have a table call Members
In the table, one of the records is called 'rank'
I want to write some code to replace the rank with a value.
For example, in the first record, rank has to be replaced with number 67
In the second record, rank has to be replaced with number 68
etc…
I made a new module and I did write this code, but it does not work.
Why?
------------------------------
Sub Number()
Dim db As Database
Dim rs As Recordset
Dim Nr As Long


Set db = Application.CurrentDb
Set rs = db.OpenRecordset("Members")
Nr = 66


rs.MoveFirst

Do
Nr = Nr + 1
rs.Fields("rank").Value = Nr
rs.MoveNext
Loop Until rs.EOF
End Sub
--------------------------

Thanks in advance,

Jack/Belgium

 

[namliam]

Hiya, southern neighbour.... try using [ code ] and [ /code ] (without the spaces) to post code...

What version are you using? If A97

Sub Number() 
Dim db As DAO.Database 
Dim rs As DAO.Recordset 
Dim Nr As Long 

Set db = Application.CurrentDb 
Set rs = db.OpenRecordset("Members") 
Nr = 66 

rs.MoveFirst 

Do 
Nr = Nr + 1 
[COLOR=red]rs.edit[/COLOR] 
rs.Fields("rank").Value = Nr 
[COLOR=red]rs.update[/COLOR] 
rs.MoveNext 

Loop Until rs.EOF 
End Sub

If 2000 or 2002 same but make sure your DAO is referenced (from a module: Tools=>References)

Also:
1)
Do untill
loop
is better practice (you know in advance how long the loop is going to run, asspecially good if its a long loop)

2)
rs!rank = nr
Will do exactly the same as rs.Fields("rank").Value = Nr only its a little more acceptable...

Regards

The Mailman

 

[MadMaxx]

First thing is first.

What is the error message you get? Will make answering a little easier.

 

[namliam]

MaddMaxx: He wouldnt get an error, nothing wrong with his code. Exept he forgot the rs.edit and rs.update statements.

It simple doesnt update....

Regards

 

[Jackske]

I am using Office 2000 and in my references DAO 3.6 Object Library is checked.

Any way, thanks

 

[namliam]

Is it now working?

Also: Dont double post, its not done!

Regards

 

[MadMaxx]

oh right. never looked at his code hard enough.

 

[Jackske]

Thanks for your good will
The message is 'Run-time error 3020'
Update or CancelUpdate without AddNew or Edit

 

[Jackske]

What do you mean with rs.edit and rs.edit?
What is the code I forgot?

 

[namliam]

I went back and highlighted your missing lines...

Regards

 

[Jackske]

To all, thanks it is working.
Nice to have such a kind community
Like you say in English "Merci beaucoup"