mysql - 循环 Access MS Access 中的连续记录

Question

上下文:我在我的数据库中制作了一个假期跟踪模块。员工可以要求在当年期间提前休假，并将其从下一年期间扣除。

案例:我正在尝试对员工的记录集进行连续循环，以查看他们是否已获得提前工作时间，如果是，则将其添加到他们的休假时间中。

问题:我有逻辑，但我无法让它在我的连续表格中遍历每个员工。

With Me.RecordsetClone
    While Not .EOF
        adv = DLookup("advhours", "dbo_employees", "[empid] = txtempid")
        vac = DLookup("vhrs", "dbo_employees", "[empid] = txtempid")
        adate = DLookup("advdate", "dbo_employees", "[empid] = txtempid")
        andatestart = DLookup("anndate", "dbo_employees", "[empid] = txtempid")
        andateend = DateAdd("yyyy", AgeSimple([andatestart]), [andatestart])
        anddateend = DateAdd("yyyy", 1, andateend)
        morehours = DLookup("totalvachrs", "dbo_employees", "[empid] = txtempid")

        sum = adv + vac


        If adv > 0 And adate > DateAdd("yyyy", AgeSimple([andatestart]), [andatestart]) And adate < anddateend And morehours = 0 Then
            morehours = 1

            ' sets the flag to 1 if true.
            DoCmd.RunSQL "UPDATE dbo_employees " & "SET dbo_employees.totalvachrs='" & morehours & "' " & "WHERE dbo_employees.empid=" & txtempid & ";"

            'increment vacation hours
            DoCmd.RunSQL "UPDATE dbo_employees " & "SET dbo_employees.vhrs='" & sum & "' " & "WHERE dbo_employees.empid=" & txtempid & ";"

        End If

        'after Vac hours have been updated..

        If morehours = 1 And adate < andateend Then
            ' puts vacation hours back down to where they were.
            vac = vac - adv
            DoCmd.RunSQL "UPDATE dbo_employees " & _
                                "SET dbo_employees.vhrs='" & _
                                vac & "' " & _
                                "WHERE dbo_employees.empid=" & txtempid & ";"
            adv = 0
            DoCmd.RunSQL "UPDATE dbo_employees " & _
                                "SET dbo_employees.advhours='" & _
                                adv & "' " & _
                                "WHERE dbo_employees.empid=" & txtempid & ";"
             morehours = 0
            DoCmd.RunSQL "UPDATE dbo_employees " & _
                                "SET dbo_employees.totalvachrs='" & _
                                morehours & "' " & _
                                "WHERE dbo_employees.empid=" & txtempid & ";"

       End If
      Debug.Print txtempid ' CTRL G to see

      If Not .EOF Then .MoveNext
    Wend
End With
MsgBox "after loop: " & txtempid

我也试过这个，但是我在 access 中得到了 drop changes 对话框

Set rs = Me.RecordsetClone

rs.MoveFirst


Do While Not rs.EOF
    Me.Bookmark = rs.Bookmark
    adv = DLookup("advhours", "dbo_employees", "[empid] = txtempid")
    vac = DLookup("vhrs", "dbo_employees", "[empid] = txtempid")
    adate = DLookup("advdate", "dbo_employees", "[empid] = txtempid")
    andatestart = DLookup("anndate", "dbo_employees", "[empid] = txtempid")
    andateend = DateAdd("yyyy", AgeSimple([andatestart]), [andatestart])
    anddateend = DateAdd("yyyy", 1, andateend)
    morehours = DLookup("totalvachrs", "dbo_employees", "[empid] = txtempid")

    sum = adv + vac


    If adv > 0 And adate > DateAdd("yyyy", AgeSimple([andatestart]), [andatestart]) And adate < anddateend And morehours = 0 Then
        morehours = 1

        DoCmd.RunSQL "UPDATE dbo_employees " & "SET dbo_employees.totalvachrs='" & morehours & "' " & "WHERE dbo_employees.empid=" & txtempid & ";"

        DoCmd.RunSQL "UPDATE dbo_employees " & "SET dbo_employees.vhrs='" & sum & "' " & "WHERE dbo_employees.empid=" & txtempid & ";"      

    End If

    If morehours = 1 And adate < andateend Then
        vac = vac - adv
        DoCmd.RunSQL "UPDATE dbo_employees " & _
                            "SET dbo_employees.vhrs='" & _
                            vac & "' " & _
                            "WHERE dbo_employees.empid=" & txtempid & ";"
        adv = 0
        DoCmd.RunSQL "UPDATE dbo_employees " & _
                            "SET dbo_employees.advhours='" & _
                            adv & "' " & _
                            "WHERE dbo_employees.empid=" & txtempid & ";"
        morehours = 0
        DoCmd.RunSQL "UPDATE dbo_employees " & _
                            "SET dbo_employees.totalvachrs='" & _
                            morehours & "' " & _
                            "WHERE dbo_employees.empid=" & txtempid & ";"

    End If

    Me.Bookmark = rs.Bookmark
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Answer 1

点点，

我认为您的问题是对记录集和表单(以及连续表单)如何工作的基本误解。

无论表单是常规表单(一次显示一条记录)还是连续表单(一次显示多条记录)，在表单上引用值(字段)和/或使用的能力和限制记录集不会改变。

您不能“循环”通过代码中的连续表单并引用表单上的控件 (txtempid) 并从每条记录中获取值，正如您正在尝试做的那样。

任何时候您在表单上引用控件时，您都只会获得与当前记录相关联的控件的值。

循环记录集，即使您从表单 recordsetclone 实例化记录集，也不会更改表单上的当前记录指针。

您的第二个代码示例最接近可行，但仍需要一些工作。首先，在代码中使用记录集时，您很可能不会引用表单控件，而是直接从记录集中引用字段值。我不知道您的表结构，但这里有一个示例应该能让您走上正轨。

我假设此代码将在单击按钮或您想要评估所有员工的其他事件时运行。

Dim rst as DAO.Recordset
Set rst = Me.RecordsetClone
Do While Not rst.EOF
  adv = DLookup("advhours", "dbo_employees", "[empid] = " & rst("empid"))
  vac = DLookup("vhrs", "dbo_employees", "[empid] = " & rst("empid"))
  adate = DLookup("advdate", "dbo_employees", "[empid] = " & rst("empid"))
  andatestart = DLookup("anndate", "dbo_employees", "[empid] = " & rst("empid"))
  andateend = DateAdd("yyyy", AgeSimple(rst("andatestart")), rst("andatestart"))
  anddateend = DateAdd("yyyy", 1, rst("andateend"))
  morehours = DLookup("totalvachrs", "dbo_employees", "[empid] = " & rst("empid"))

  sum = adv + vac

  If adv > 0 And adate > DateAdd("yyyy", AgeSimple(rst("andatestart")), rst("andatestart")) And adate < anddateend And morehours = 0 Then      
    morehours = 1
    rst.Edit
    rst("totalvachrs") = morehours
    rst("vhrs") = sum
    rst.Update
  End If

  If morehours = 1 And adate < andateend Then
    vac = vac - adv
    rst.Edit
    rst("vhrs") = vac
    adv = 0
    rst("advhours") = adv
    morehours = 0
    rst("totalvachrs") = morehours
    rst.Update
  End If

  rst.MoveNext
Loop
Set rst = Nothing
'Refresh the screen
Me.Requery

您不需要使用DoCmd.RunSQL，您可以通过代码直接在Recordset中编辑数据。

请注意，在您的示例代码中，您似乎在引用表单上的控件的任何地方

[andatestart]

我将其更改为在记录集中引用该字段名称

rst("andatestart")

此代码将遍历您表单上的每条记录并执行您正在执行的计算和更新。

FYI, in your original code, by setting the form bookmark (Me.Bookmark) to the recordset Bookmark, you were forcing the form to move the current record pointer through each record, but that is really not the best way to accomplish what you are trying to do, IMO

希望对您有所帮助。