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