numbering column causing query to stop responding

ccg121

New member
Local time
Today, 14:11
Joined
Mar 19, 2013
Messages
3
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:

Code:
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:

Code:
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:

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

Code:
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];
 
Your problem appears to be that you have your

.MoveNext

in the wrong place.

You have:
Code:
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

But it should be:

Code:
With rst
OID = rst!OrderID
    Do Until .EOF
        If OID = rst!OrderID Then
        .Edit
            !OrderLineNumber = i
        .Update
        i = i + 1
        Else
        OID = rst!OrderID
        i = 1
        End If
    [B][COLOR=red].MoveNext[/COLOR][/B]
    Loop
 
well that brings the runtime on subquery1 down from about 1:45 to about 1:20 but before the original code is run it takes under 5 seconds and without the totals it takes under 2 even after the update
 
well that brings the runtime on subquery1 down from about 1:45 to about 1:20 but before the original code is run it takes under 5 seconds and without the totals it takes under 2 even after the update

First you say that it is causing the query to stop responding, and then you say that it is now taking 20 seconds less? I don't get it. The problem of it not responding would be because it was in a perpetual loop as it would not have moved on after it hit a certain record based on your If statement.

So how and where are these sub queries used? I don't see any reference to them in the original code unless [Inventory Transactions] is a query but you also didn't make it clear as to what its SQL was.
 

Users who are viewing this thread

Back
Top Bottom