时间戳

首页 > 解决方案 > Access-VBA For 循环只执行一次

sql - Access-VBA For 循环只执行一次

问题描述

我正在尝试测量两次更新之间的持续时间。已经有一个表可以跟踪“lastvalue”、“newvalue”和“updatetime”。我的方法是搜索满足条件的实体并将相关的更新时间分配给变量然后计算。但是，我的 for 循环不起作用，它们只执行一次。我是 access-vba 的新手，我使用 debug.prints 来查看事情的变化（它们是永久性的）。下面是我的代码，谢谢

Private Sub olcum()

Dim gs As Long
Dim db As Database
Dim rs As Recordset
Dim pt As Date
Dim ct As Date
Dim pc As String
Dim cc As String
Dim id As Integer
Dim i As Integer
Dim l As Integer
Dim k As Integer
'Dim a As Index
Dim strMessage As String


Set db = CurrentDb
Set rs = db.OpenRecordset("GecenSure", dbOpenSnapshot)
pc = "acilmasi bekleniyor"
cc = "onayda"


rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
    strMessage = "Burada: " & (rs.AbsolutePosition + 1)
    Debug.Print (strMessage)
    'Set a = rs.AbsolutePosition
    'Debug.Print (a)
    id = rs.Fields("Kimlik")
    rs.MoveFirst

    For l = 0 To (rs.RecordCount - 1)
        strMessage = "Burada: " & (rs.AbsolutePosition + 1)
        Debug.Print (strMessage)
        If (rs.Fields("Kimlik") = id) And (rs.Fields("PreviousCase") = pc) Then
        pt = rs.Fields("UpdateTime")
        Else
        End If

        For k = 0 To (rs.RecordCount - (1 + l))
            strMessage = "Burada: " & (rs.AbsolutePosition + 1)
            Debug.Print (strMessage)
        'If (rs.Fields("Kimlik") = id) And (rs.Fields("CurrentCase") = cc) Then
            'Debug.Print "rs.AbsolutePosition"
            If (rs.Fields("Kimlik") = id) And (rs.Fields("PreviousCase") = cc) Then
            ct = rs.Fields("UpdateTime")
            Else
            End If
        'Else
        'End If
        rs.MoveNext
        strMessage = "Burada: " & (rs.AbsolutePosition + 1)
        Debug.Print (strMessage)
        Next k
        'bu prosedurle 2 kere ct atıyor.

    rs.MoveFirst

    For z = 0 To i
    rs.MoveNext
    strMessage = "Burada: " & (rs.AbsolutePosition + 1)
    Debug.Print (strMessage)
    Next z

    Next l

    gs = ct - pt
    Debug.Print gs

    'Debug.Print rs.Fields("CurrentCase")
rs.MoveNext
strMessage = "Burada: " & (rs.AbsolutePosition + 1)
Debug.Print (strMessage)
Next i

rs.Close
Set rs = Nothing
db.Close

MsgBox "Simdiki durumlar gosterildi"

End Sub

标签： sqlfor-loopms-accessvba

解决方案

rs.RecordCount不返回记录集中的记录总数；而是返回已访问的记录数。

有几种选择；选项 3 通常被认为是最好的，并且是许多 Microsoft 示例中使用的方法：

  1. rs.MoveLast - 在循环之前；这不是一个高性能选项
  2. Query a SQL Count - 在循环之前，确定记录数
  3. 对 rs.EOF使用While或Do循环检查（请参阅https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb243789(v=office.12)

Do循环的一个例子：

Set rs = db.OpenRecordSet("GecenSure", dbOpenSnapshot)

Do Until rs.EOF
    ' do something
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

推荐阅读