我如何使这段代码通过查询中的每条记录并执行if语句?

Question

我试图让这段代码通过查询中的每个记录，并检查每个记录的JobGrade字段的值。根据值的不同，2个字段将加载数字。我试图在表单的事件之一实现此代码。代码工作，但只对第一个记录。不确定我是否在错误的事件中实现此代码。

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

Answer 1

您需要将代码放入循环中，以便它遍历记录。

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