时间戳

首页 > 解决方案 > 如何使此代码遍历查询中的每条记录并执行 if 语句？

vba - 如何使此代码遍历查询中的每条记录并执行 if 语句？

问题描述

我试图让这段代码遍历查询中的每条记录，并检查每条记录的 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

标签： vbams-access

解决方案

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

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

推荐阅读