Acces: For Loop to number 123 in column

you forgot to put:

rs.movenext

Select allOpen in new window

in the loop.

While rs.EOF = False
...
   rs.movenext
Wend

you also forgot to put rs.Update in your loop.

Hi,

I tried adding in the rs.update and rs.MoveNext lines but I had errors.
I tried ur code and it worked!
Thank You!

I tried adding in the rs.update and rs.MoveNext lines but I had errors.

I tried ur code and it worked

example above in ID: 42434453 tested working fine, so i assume it's working fine with you as well.

Yup the example at 42434453 works well!
Thank you!
but wouldn't having a For loop make things much easier... I was just wondering why it wasnt working though

I was just wondering why it wasnt working though

it depends on the requirement where we try to build the logic accordingly, which it may involve codes or else executing some SQL, for examples.

Instead of testing i value, you might want to take advantage of the modulo (Mod) operator:

Function seq()

 Dim i As Integer
 Dim db As DAO.Database
 Set db = CurrentDb()
 Set rs = db.OpenRecordset("NewTypes", dbOpenDynaset)
 
 i = 0
While rs.EOF = False

    rs.Edit
    rs("WeekNo") = (i Mod 3) + 1
    rs.Update
    i = i + 1
    rs.MoveNext
Wend
rs.Close
End Function

Select allOpen in new window

Since nobody else mentioned it, I'll jump in.

It appears that you have a column ([WeekNo]) in your table, and you want consecutive records in that table to read 1, 2, 3, 1, 2, 3, 1, 2, 3.  

Your request has me confused.  Could you provide an explanation of why you are attempting to do this?  We might be able to prevent you from making a database design decision which will cause you more trouble in the long run if we understand what you are trying to accomplish.

We might be able to prevent you from making a database design decision which will cause you more trouble in the long run if we understand what you are trying to accomplish.

Agreed with Dale, inserting fixed values based on row sequence could be risky in generating data errors and you got to make sure your codes cover that. Or generate that weekno dynamically when necessary (in case it's not critical to store it in table)

I was just trying out a small sample on converting a random Purchase of Order from horizontal to vertical based using my own random values.
I have been trying to transpose it vertical based and it ended up getting a bit too haywire.

I need to transpose this to vertical based in access such that it will be

MPA PlanningPartGroup  Dates Values

FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     8/21    9
FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     8/28   14
FLEX-PTP      SPT MID DW M SFP GROUP      LIMO PRO     9/4     12

Is it possible to be done in access?
Or if not, transpose it in Excel and then import?

Thanks!

what you are asking here now seems not related to what you have been initially asked.

i would suggest you to create another question or project (gigs) to address this issue.

yup i think i should create another question instead of editing it..
Thanks