numbering column in access causing query to stop responding

Question

I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:

Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1
stringSQL = "SELECT * FROM [Inventory Transactions] " _
          & "WHERE [OrderID] >  0 Order By [OrderID],[TransactionID]"
Set rst = CurrentDb.OpenRecordset(stringSQL, dbOpenDynaset)
With rst
OID = rst!OrderID
    Do Until .EOF
        If OID = rst!OrderID Then
        .Edit
            !OrderLineNumber = i
        .Update
        .MoveNext
        i = i + 1
        Else
        OID = rst!OrderID
        i = 1
        End If
    Loop
    .Close
End With
Set rst = Nothing

Edit 1:

I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.

Edit 2:

I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by

Subquery 2:

SELECT 
  CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total], 
  CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax], 
  [Line Total With Tax]-[Line Total] AS [Line Tax], 
  [Inventory Transactions].* 
  FROM [Inventory Transactions] 
  WHERE ((([Inventory Transactions].OrderID) Is Not Null));

sum of payments Query:

SELECT DISTINCTROW Payments.OrderID, 
  Sum(Payments.PaymentAmount) AS [Total Payments]
FROM Payments 
GROUP BY Payments.OrderID;

Subquery 1:

SELECT 
  [Balance Due By Customers Subquery2].OrderID, 
  Sum([Balance Due By Customers Subquery2].[Line Total]) AS [SumOfLine Total], 
  Sum([Balance Due By Customers Subquery2].[Line Tax]) AS [SumOfLine Tax], 
  Sum([Balance Due By Customers Subquery2].[Line Total With Tax]) AS [SumOfLine Total With Tax], 
  [Sum Of Payments Query].[Total Payments]
FROM [Balance Due By Customers Subquery2] 
  LEFT JOIN [Sum Of Payments Query] 
  ON [Balance Due By Customers Subquery2].OrderID = [Sum Of Payments Query].OrderID
GROUP BY [Balance Due By Customers Subquery2].OrderID, 
  [Sum Of Payments Query].[Total Payments];

Answer 1

1. Embedded functions like nz() slow your query. They also make your SQL illegible. Improve your tables so nz() is not needed. For example, open your table in design view and give each numeric field a default value of zero.
2. Test each query without the DISTINCTROW keyword. The queries should run faster. I expect it's doing nothing, because I notice each query has an ID record in the SELECT clause. (Just see if the same number of records is returned -- yep, it's doing nothing.)
3. You seem to be grouping by OrderID on a query result that also groups by OrderID. The higher-level GROUP BY is doing nothing. Which means some of your SUM() functions are doing nothing...except being slow.
4. Subquery 2 is a mess (sorry to be blunt). Do you need all those fields? If so, at least have mercy on us by shortening the syntax: [Inventory Transactions].PriceChanged can be presented as PriceChanged. And (at least in the future) avoid spaces in table and query names -- you will save yourself trouble.
5. Calculations like [Line Total] might work better in a preliminary query. At least it would be cleaner programming, in my opinion.
6. Use a period at the end of each sentence. It will help us understand your post.

Bad design is brittle: it becomes broken for mysterious reasons. Good design facilitates query functioning, and in this case you need it for trouble-shooting. Fixing a query often requires

1. reduction and simplification, until the problem disappears
2. building back up to the needed result. (More info here.)

Based on your Edit 2, you need to use this approach on Subquery 2. Start with the simplest possible query that combines the two others. Leave off the aggregate functions (SUM(), GROUP BY) -- somehow they are messing things up.

Before simplifying in a way that changes the data result, you can simplify these queries by merely improving syntax. For example, here is Subquery 1 with alias names for the nested queries -- much easier to read:

SELECT 
  BalanceDue.OrderID, 
  Sum(BalanceDue.[Line Total]) AS [SumOfLine Total], 
  Sum(BalanceDue.[Line Tax]) AS [SumOfLine Tax], 
  Sum(BalanceDue.[Line Total With Tax]) AS [SumOfLine Total With Tax], 
  SumPmts.[Total Payments]
FROM [Balance Due By Customers Subquery2] AS BalanceDue
  LEFT JOIN [Sum Of Payments Query] AS SumPmts
  ON BalanceDue.OrderID = SumPmts.OrderID
GROUP BY BalanceDue.OrderID, 
  SumPmts.[Total Payments];