How can I make this code go through every record in a query and execute the if statements?-CodePudding

I am trying to have this code go through each record in a query and check the value of the JobGrade field for each record. Depending on the value, 2 fields will be loaded with numbers. I am trying to implement this code in one of the events of a form. The code works but only for the first record. Not sure if i am implementing this code in the wrong event.

Private Sub Form_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Unassigned_Jobs")   'name of query

rs.Edit

If rs.Fields("Repeat") = 0 Then      'Repeat = false

    If rs.Fields("JobGrade") = 1 Then
        rs.Fields("1PercWorkLoad") = 1
        rs.Fields("2PercWorkLoad") = 0

    ElseIf rs.Fields("JobGrade") = 2 Then
        rs.Fields("1PercWorkLoad") = 3
        rs.Fields("2PercWorkLoad") = 0

    ElseIf rs.Fields("JobGrade") = 3 Then
        rs.Fields("1PercWorkLoad") = 8
        rs.Fields("2PercWorkLoad") = 2.4

    ElseIf rs.Fields("JobGrade") = 4 Then
        rs.Fields("1PercWorkLoad") = 24
        rs.Fields("2PercWorkLoad") = 4.8

    Else: rs.Fields("JobGrade") = 5
        rs.Fields("1PercWorkLoad") = 40
        rs.Fields("2PercWorkLoad") = 4

    End If

ElseIf rs.Fields("Repeat") = -1 Then     'Repeat = true

    If rs.Fields("JobGrade") = 1 Then
        rs.Fields("1PercWorkLoad") = 1
        rs.Fields("2PercWorkLoad") = 0.25

    ElseIf rs.Fields("JobGrade") = 2 Then
        rs.Fields("1PercWorkLoad") = 3
        rs.Fields("2PercWorkLoad") = 0.75

    ElseIf rs.Fields("JobGrade") = 3 Then
        rs.Fields("1PercWorkLoad") = 8
        rs.Fields("2PercWorkLoad") = 0.6

    ElseIf rs.Fields("JobGrade") = 4 Then
        rs.Fields("1PercWorkLoad") = 24
        rs.Fields("2PercWorkLoad") = 1.2

    Else: rs.Fields("JobGrade") = 5
        rs.Fields("1PercWorkLoad") = 40
        rs.Fields("2PercWorkLoad") = 1

    End If

End If

rs.Update
rs.MoveNext

rs.Close
Set rs = Nothing
db.Close


End Sub

CodePudding user response：

You need to put the code into a loop for it to iterate through the records.

Private Sub Form_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Unassigned_Jobs")   'name of query
rs.movefirst 'Good habit to make sure you're starting at record 1
rs.Edit
Do Until rs.EOF 'Loop to the end
    If rs.Fields("Repeat") = 0 Then      'Repeat = false
    
        If rs.Fields("JobGrade") = 1 Then
            rs.Fields("1PercWorkLoad") = 1
            rs.Fields("2PercWorkLoad") = 0
    
        ElseIf rs.Fields("JobGrade") = 2 Then
            rs.Fields("1PercWorkLoad") = 3
            rs.Fields("2PercWorkLoad") = 0
    
        ElseIf rs.Fields("JobGrade") = 3 Then
            rs.Fields("1PercWorkLoad") = 8
            rs.Fields("2PercWorkLoad") = 2.4
    
        ElseIf rs.Fields("JobGrade") = 4 Then
            rs.Fields("1PercWorkLoad") = 24
            rs.Fields("2PercWorkLoad") = 4.8
    
        Else: rs.Fields("JobGrade") = 5
            rs.Fields("1PercWorkLoad") = 40
            rs.Fields("2PercWorkLoad") = 4
    
        End If
    
    ElseIf rs.Fields("Repeat") = -1 Then     'Repeat = true
    
        If rs.Fields("JobGrade") = 1 Then
            rs.Fields("1PercWorkLoad") = 1
            rs.Fields("2PercWorkLoad") = 0.25
    
        ElseIf rs.Fields("JobGrade") = 2 Then
            rs.Fields("1PercWorkLoad") = 3
            rs.Fields("2PercWorkLoad") = 0.75
    
        ElseIf rs.Fields("JobGrade") = 3 Then
            rs.Fields("1PercWorkLoad") = 8
            rs.Fields("2PercWorkLoad") = 0.6
    
        ElseIf rs.Fields("JobGrade") = 4 Then
            rs.Fields("1PercWorkLoad") = 24
            rs.Fields("2PercWorkLoad") = 1.2
    
        Else: rs.Fields("JobGrade") = 5
            rs.Fields("1PercWorkLoad") = 40
            rs.Fields("2PercWorkLoad") = 1
    
        End If
    
    End If

    rs.MoveNext 'Need this or you'll loop infinitely
Loop
rs.Update
rs.Close
Set rs = Nothing
db.Close


End Sub