CODEWITHSUNDEEP
sqlvbams-access
cm2115
cm2115

Reputation: 61

While Loop in VBA Access

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.

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.

Here's what I tried:

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

To illustrate:

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

Upvotes: 1

Views: 1859

Answers (2)

Parfait
Parfait

Reputation: 107567

Consider actually no For loop, no BuildCriteria and even no VBA SQL. Save the update query as an MS Access action query object that is run on button click.

Specifically, you would need several domain functions --DLookUp, DSum, and DMax-- where you calculate a running sum of duration days (i.e., a correlated aggregate computation) and then DateAdd the result to the DueDate of the corresponding maximum ID with no missing DueDate.

SQL 

UPDATE myTable d
SET d.DueDate =  DateAdd("d", -1 * DSum("Duration", "DueDateDuration", "ID >= " & d.ID),  
                                         DLookUp("DueDate", "DueDateDuration", "ID = " &  
                                                 DMax("ID", "DueDateDuration", "DueDate IS NOT NULL") 
                                       )
                        )
WHERE d.DueDate IS NULL;

VBA

Private Sub Command7_Click()
    DoCmd.OpenQuery "mySavedUpdateQuery"     ' WITH WARNINGS

    CurrentDb.Execute "mySavedUpdateQuery"   ' WITHOUT WARNINGS
End Sub

To demonstrate on sample data:

Before Update (mytable)

ID      Item       Duration     DueDate
2674    Issue             1   2/18/2019
2675    Shipping          1   2/19/2019
2678    Completed         0   2/20/2019
2679    Issue             1 
2680    Shipping         10 
2681    Other             6 
2682    Buy Off           6 
2683    Punch List        3 
2684    Completed         0   3/29/2019

After Update (mytable)

ID      Item       Duration     DueDate
2674    Issue             1   2/18/2019
2675    Shipping          1   2/19/2019
2678    Completed         0   2/20/2019
2679    Issue             1    3/3/2019
2680    Shipping         10    3/4/2019
2681    Other             6   3/14/2019
2682    Buy Off           6   3/20/2019
2683    Punch List        3   3/26/2019
2684    Completed         0   3/29/2019

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

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.

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...

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

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).

EDIT

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

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

Upvotes: 1

Related Questions