Acces: For Loop to number 123 in column

Hi. I know this is a simple question.
But when I try it out, my database keeps not responding and I have been reopening it a couple of times.
Would like to just check if my code is okay

I'm just using a For loop to number 1,2,3 in the WeekNo Column.
The 123 will keep on repeating.

Function seq()

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

    rs.Edit
    For i = 1 To 3
 
        rs("WeekNo") = i
       
     Next i
Wend
rs.Close
End Function


Thank you!
Nihara AzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you forgot to put:
rs.movenext

Open in new window


in the loop.

While rs.EOF = False
...
   rs.movenext
Wend
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you also forgot to put rs.Update in your loop.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if you want to have a repetitive sequence of 1,2,3 into your records, you may try:

Function seq()

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

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

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nihara AzAuthor Commented:
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!
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
Nihara AzAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
Fabrice LambertFabrice LambertCommented:
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

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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)
0
Nihara AzAuthor Commented:
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.

The PORI 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!
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
Nihara AzAuthor Commented:
yup i think i should create another question instead of editing it..
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.