Please help with loops

[AmberO]

I need help writing a loop. I want to go through the first 12 records in a table and mark them 1,2,3 ... 12, then do the same for the next 12 records until I have marked all records with a number. This way I am hoping I can print the records by number in a somewhat randomised manner, yet can ensure that all records are printed.

Thank you in advance,
Amber

 

[Calvin]

Hi Amber,

Try something like this:

dim i as integer
i = 1
dim rst as recordset
set rst = currentdb.openrecordset ("SELECT [MyField] FROM MyTable")

if rst.bof or rst.eof then goto 50
rst.movefirst
do while not rst.eof
rst.edit
rst![MyField]=i
rst.update
i=i+1
if i>12 then i = 1
rst.movenext
loop

50:
rst.close
set rst=nothing

 

[AmberO]

Thanks Calvin!

When I run this, I get an error "Method or data member not found" and the rst.edit is highlighted. I should probably have said that I am using Access 2000 - does this make a difference?

I tried changing it to rst.editmode, which is an option on the drop down, but then it objects to that too! (invalid use of property).

Can I please impose on you for some further assistance?

Thank you,
Amber

 

[Calvin]

I wrote it in 97 but it will work in 2k if you add the DAO Reference to the project.

Open a module (any module) in design/edit mode and select Tools Menu and then select References...

The References window will appear listing the libraries that are used in the project. Slide down the list until you fine "Microsoft DAO" and there may be a couple to choose from 2.5/3.5, 3.51, 3.6 select the highest one and click OK and then try running the code again.

 

[AmberO]

Thank you Thank you Thank you Thank you Thank you !!!

NB - I did have the DAO reference, and setting it to a higher priority allowed the code to run. Just thought I would add that, in case it helps someone else.

Thank you Calvin, the code does exactly what I wanted!