For Loop Problems

[CraddyP]

Hi,

I am having a problem with a for loop. Heres what im trying to do.

Basically when a new rate set is added for a job in our system the system needs to go back through the timesheet table and update all entries made after the new rate set comes into play.

Each member of staff has a position (eg, Technician, Engineer, Director etc). Rates are stored against position not staff member.

Also instead of dates we are using year periods in the format (000000) Eg. 200401 is the first two weeks of 2004.

Heres the section of code I am having a problem with. The problem is that it only seems to check the first Job number in the timesheet table and then exits. Its probably something really simple I have missed so i'm pretty sure someone will be able to help

---------------------------------------------------------------

Set rs2 = db.OpenRecordset("TIMESHT")
Set rs3 = db.OpenRecordset("Staff")
rs2.MoveFirst
rs3.MoveFirst

For i = 0 To rs2.RecordCount
    If rs2!jobno = Forms![rates]![jobno] Then
        If rs2!yr_prd >= Me.DateFinish Then
            For k = 0 To rs3.RecordCount
                If rs3!stseqno = rs2!stseqno Then
                    Position = rs3!Position
                    Select Case Position
                        Case "Director"
                            rs2.Edit
                            rs2!Rate = Me.DirNewRate
                            rs2.Update
                        Case "Associate"
                            rs2.Edit
                            rs2!Rate = Me.AssocNewRate
                            rs2.Update
                        Case "Senior Engineer"
                            rs2.Edit
                            rs2!Rate = Me.SenEngNewRate
                            rs2.Update
                        Case "Engineer"
                            rs2.Edit
                            rs2!Rate = Me.EngNewRate
                            rs2.Update
                        Case "Senior Technician"
                            rs2.Edit
                            rs2!Rate = Me.SenTechNewRate
                            rs2.Update
                        Case "Technician"
                            rs2.Edit
                            rs2!Rate = Me.TechNewRate
                            rs2.Update
                        Case "Junior Engineer"
                            rs2.Edit
                            rs2!Rate = Me.JunEngNewRate
                            rs2.Update
                        Case "Graduate Engineer"
                            rs2.Edit
                            rs2!Rate = Me.GradEngNewRate
                            rs2.Update
                        Case "Trainee"
                            rs2.Edit
                            rs2!Rate = Me.TrainNewRate
                            rs2.Update
                        Case "Admin Assistant"
                            rs2.Edit
                            rs2!Rate = Me.adminNewRate
                            rs2.Update
                        Case "Site Monitor"
                            rs2.Edit
                            rs2!Rate = Me.SiteMonNewRate
                            rs2.Update
                        Case Else
                        
                    End Select
                Else
                    rs3.MoveNext
                    If rs3.EOF Then
                        Exit For
                    End If
                End If
            Next k
        Else
            rs2.MoveNext
            If rs2.EOF Then
                Exit For
            End If
        End If
    Else
        rs2.MoveNext
        If rs2.EOF Then
            Exit For
        End If
    End If
Next i

-------------------------------------------

Thanks for your time

 

[Simplycongy]

Wouldn't you be better off using an update query to select all entries after period x in for positon x???

 

[Mile-O]

What would be much easier is to get a proper structure - that table with rates does not meet First Normal Form ergo it can't be considered normalised.

 

[CraddyP]

simplycongy -- > I dont know too much about update queries but will have a read around and try it out.

S J McAbney --> I have picked up developing the system from someone else who spent a year making it. There was no planning and the structure doesnt make any sense whatsover.
You would laugh if you saw it, I cried!!

Anyway I have a deadline of Jan 1st to get it in place and changing the structure to what it should be would mean re-writing the whole thing.
Also the tables have been filled with data as the system is being developed, again not ideal.

Anyway thanks for the comments

 

[allan57]

Try putting 'MoveLast' prior to MoveFirst, this should populate the recordset.

eg

rs2.MoveLast
rs2.MoveFirst

rs3.MoveLast
rs3.MoveFirst

Allan

 

[CraddyP]

That seems to have done the job.
Cheers