VBA Access 中的 While 循环 [英] While Loop in VBA Access

问题描述

我有删除和追加函数，可以根据用户的输入构建 Table1.因此，表 1 为每个用户附加了不同数量的记录.

I have delete and append functions that build Table1 based on inputs from the user. Therefore Table1 has a different number of records appending to it for every user.

我的 SQL 代码可用于查找日期，但它只执行一次，我需要为表的长度循环 SQL 代码.我不太擅长编码，我尝试了一个 while 语句，不确定我是否可以在该条件中使用变量 Z，但我希望它一直运行，直到填充了具有最小 ID 值的记录中的 Due_date.

My SQL code works to find the dates, but it only does it once, I need to loop the SQL code for the length of the table. I'm not great at coding, I tried a while statement, not sure if I can use variable Z in the criteria for that, but I want it to run until the due_date in the record with the smallest ID value has been filled.

这是我尝试过的:

Private Sub Command7_Click()
Y = DMax("ID", "Table1", BuildCriteria("Due_date", dbDate, "Null"))
A = DMin("ID", "Table1", BuildCriteria("Due_date", dbDate, "Not Null"))
X = DMin("ID", "Table1")
Z = DLookup("Due_date", "Table1", BuildCriteria("ID", dbLong, CStr(X)))

B = DLookup("Duration", "Table1", BuildCriteria("ID", dbLong, CStr(Y)))
C = DLookup("Due_date", "Table1", BuildCriteria("ID", dbLong, CStr(A)))
E = DateAdd("d", -B, C)

Dim SQL As String

SQL = "UPDATE Table1 " & _
    "SET " & BuildCriteria("Due_date", dbDate, CStr(E)) & " " & _
    "WHERE " & BuildCriteria("ID", dbLong, CStr(Y))


While Z Is Null
DoCmd.RunSQL SQL

End While

End Sub

举例说明:

• 运行 SQL 之前

• Before Running SQL

运行一次 SQL 后

点击几次后

目标是点击一次，整个表格就会填满

The goal would be to click once and the whole table fills

推荐答案

你的变量 Z 包含 DLookup 函数返回的 result 当评估为子 Command7_Click 定义的第四行；除非重新定义该变量，否则该变量的值不会改变.

Your variable Z contains the result returned by the DLookup function when evaluated as the fourth line of the definition of your sub Command7_Click; the value of this variable will not change unless the variable is redefined.

BuildCriteria 函数的使用使您的代码意图有些模糊，因此很难建议编写代码的最佳方式......

The intent of your code is somewhat obscured by the use of your BuildCriteria function, so it is difficult to advise the best way to write the code...

编辑:BuildCriteria 对我来说是一个新的 - 感谢@Andre 指出这一点.

Edit: BuildCriteria is a new one for me - thanks to @Andre for pointing this out.

由于您的 SQL 语句的内容是静态的，因此不需要循环，因为循环内没有任何变化 - SQL 语句将更新所有符合您的条件的记录，并且不会对每次后续迭代执行任何操作(除非，也就是说，您更新记录的值也满足选择标准).

Since the content of your SQL statement is static, there should be no need for a loop, as nothing is changing within the loop - the SQL statement will update all records which meet your criteria and will do nothing for every subsequent iteration (unless, that is, the value to which you are updating the records also fulfils the selection criteria).

编辑

根据您的其他解释和屏幕截图，您可以通过遍历按 ID 字段排序的记录集并连续计算每个记录的适当 Due_date 来完成任务 - 类似于:

Based on your additional explanations & screenshots, you could approach the task by iterating over a recordset sorted by your ID field and successively calculating the appropriate Due_date for each record - something like:

Private Sub Command7_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim dat As Date
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("select * from Table1 order by ID desc")
    With rst
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF
                If Not IsNull(!Due_date) Then
                    dat = !Due_date
                Else
                    dat = DateAdd("d", -!Duration, dat)
                    .Edit
                    !Due_date = dat
                    .Update
                End If
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rst = Nothing
    Set dbs = Nothing
End Sub

虽然根据您的屏幕截图，您似乎正在尝试像 Excel 电子表格一样使用 Access.

Though based on your screenshots, it seems that you are trying to use Access like an Excel spreadsheet.

这篇关于VBA Access 中的 While 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！