While Loop in VBA Access

Question

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:

Before Running SQL
After running SQL once
After clicking several times

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

Please show a sample of current data and desired results. Also, we need to see BuildCriteria. Altogether, this helps set up a minimal reproducible example. — Parfait
– Parfait, Commented Oct 29, 2018 at 13:46
I don't really understand what BuildCriteria does, but that is my entire code pasted above, there's nothing else to really show. — cm2115
– cm2115, Commented Oct 29, 2018 at 13:53
My Table1 has a variable number of rows, that's why there are variable to identify the last row, the row previous to it, and then it subtracts the duration of the previous row from the last row and pastes it in the previous row. However, one button click only does this once, and I need it to do it for every record in the table without clicking the button 20+ times to complete it. — cm2115
– cm2115, Commented Oct 29, 2018 at 13:55
Please edit your post and show original data (few rows) and what your desired output (few rows). It is hard to see what you mean in words. Interesting, in all my years, I have never heard or used MS Access' BuildCriteria() method. Learn something new everyday! — Parfait
– Parfait, Commented Oct 29, 2018 at 14:01

Lee Mac · Accepted Answer · 2018-10-29 14:45:30Z

1

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.

edited Oct 29, 2018 at 14:45

answered Oct 29, 2018 at 13:05

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

cm2115 Over a year ago

The SQL statement only updates the second to last record. The table has an ID field, Task field, duration field, and due_date field. When it's first created the due date is appended at the bottom and the rest of the records in that field are Null. Then the SQL takes the due date, subtracts the duration (in days) from the record above it, and makes that the due date of that record. The problem is that it only does this once, and my table varies in length dependent on user input.

cm2115 Over a year ago

If I click cmd_7 a bunch of times it fills that same number of records.

Doug Coats Over a year ago

Id have to agree - without knowing what buildCriteria does there's only so much we can do for you. However, all the wasted processing power in all the D Functions seems unnecessary. Even without knowing what BuildCriteria does, I am pretty certain you could handle this entire procedure with one SQL command

cm2115 Over a year ago

Is there a way to have it click the button for the number of records that exist? Running the SQL command once only fills one record, I need to fill the whole table.

Andre Over a year ago

learn.microsoft.com/en-us/office/vba/api/… @DougCoats

|

Parfait · Accepted Answer · 2018-10-29 15:24:31Z

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

Hello all. Any chance we can get my original code to work? I'm going to have to add a few more variables amd make it more complex, so being able to write sql with my variables would be a huge help.

Collectives™ on Stack Overflow

While Loop in VBA Access

2 Answers 2

12 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related