• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 53
  • Last Modified:

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!
0
Nihara Az
Asked:
Nihara Az
1 Solution
 
Ryan ChongCommented:
you forgot to put:
rs.movenext

Open in new window


in the loop.

While rs.EOF = False
...
   rs.movenext
Wend
0
 
Ryan ChongCommented:
you also forgot to put rs.Update in your loop.
0
 
Ryan ChongCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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 ChongCommented:
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 ChongCommented:
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 FyeCommented:
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 ChongCommented:
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 ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now