如何让Access窗体VBA代码遍历查询全部记录并执行If条件赋值逻辑

架构老林

2026-10-4

问题原因

你的代码仅执行了单条记录的修改逻辑，没有添加循环遍历全表的逻辑，所以只会处理记录集的第一条数据就结束运行，同时你代码里判断JobGrade=5的逻辑存在语法错误，会直接把不等于1-4的JobGrade强制赋值为5，属于逻辑bug。

修复后的完整代码

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
    
    ' 循环遍历所有记录
    Do While Not rs.EOF
        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
            ElseIf rs.Fields("JobGrade") = 5 Then ' 修复原代码的赋值bug
                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
            ElseIf rs.Fields("JobGrade") = 5 Then ' 修复原代码的赋值bug
                rs.Fields("1PercWorkLoad") = 40
                rs.Fields("2PercWorkLoad") = 1
            End If
        End If
        rs.Update
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    db.Close
End Sub

可选优化建议

如果你不需要每次打开窗体都全量更新所有记录的工作量字段，可以把这段逻辑放到独立的按钮点击事件中，按需触发
如果Unassigned_Jobs的数据量较大，可以用SQL的Update语句批量更新，比遍历记录集的执行效率更高
原逻辑中没有对JobGrade不在1-5区间的异常值做处理，可以根据业务需要添加异常分支的兜底逻辑

内容的提问来源于stack exchange，提问作者user16653457