Getting Opening Balance for a Ledger by using Open Record Set NOT Dsum

[nector]

Due to the high speed, I'm getting wherever, I replaced the domain function I have decided to replace also the Dsum functions to grab the opening balance on the accounting package ledgers. Below is a query I'm trying to open to grab the opening balance but I'm getting an error object not found in the collection, where do I go wrong?

Private Sub Report_Load()
Dim db As DAO.Database
Dim strSql As String
Dim rs  As Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
strSql = "SELECT Sum(Balance) As Totals FROM [QryCustomerLedgerOp] WHERE [CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#"
Set qdf = db.QueryDefs(strSql)
For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
rs.MoveFirst
    Do While Not rs.EOF
Me.txtOpSpecials = rs!Totals.value
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Set prm = Nothing
End Sub

 

[Minty]

To simply return one summed record the DSum() will be at least as quick as your record set and a lot easier to see what is happening.
You would have 2 or 3 lines of code instead of 20, with a probable slower result.

Your code as presented is very confusing.
You aren't defining which query def you are opening, and you have a loop to evaluate parameters that don't exist.
You then also have a loop to return multiple values from the query when there will (by the definition of the SQL) only one record returned.
Even if there were multiple values you would only use the last one in the recordset because of the loop.

Did you copy this from somewhere without really understanding what it is doing?
That is not a great idea.

 

[arnelgp]

you use CreateQuerydef()

With db.CreateQuerydef("", strSQL)
    For Each prm In .Parameters
        prm.Value = Eval(prm.Name)
    Next
    Set rs = .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
End With

also I see Endless Loop in your Code:

add, rs.MoveNext, before Loop statement.

 

[ebs17]

I'm absolute agree with @Minty.
Additionally:

strSql = "SELECT Sum(Balance) As Totals FROM [QryCustomerLedgerOp]" & _
      " WHERE [CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & _
      " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#"
Debug.Print strSQL

Your SQL statement does not contain any parameter elements that need to be served with values. By assembling the statement using VBA, the contents of the form text fields are built directly into the statement and, as it looks, formatted correctly. A simple recordset for reading is then sufficient.

With CurrentDb.Openrecordset(strSQL, dbOpenForwardOnly)
    Me.txtOpSpecials = .Fields(0).Value       ' Nz(.Fields(0).Value, 0)
End With

You go to great lengths to find good codes on the issue. But you don't understand them or not well enough. Therefore, you often put these actually good elements together incorrectly and awkwardly.

 

[nector]

Many thanks to all contributors but the amend code below is longer running unless I missed something see below:

Private Sub Report_Load()
Dim db As DAO.Database
Dim strSql As String
Dim rs  As Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
strSql = "SELECT Sum(Balance) As Totals FROM [QryCustomerLedgerOp] WHERE [CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#"
With db.CreateQueryDef("", strSql)
    For Each prm In .Parameters
        prm.value = Eval(prm.Name)
    Next
    Set rs = .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
End With
Me.txtOpSpecials = rs!Totals.value
rs.MoveNext
Do
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Set prm = Nothing
End Sub

 

[Minty]

I'm not sure if you have actually read the posts from everybody but this is all you need and it will almost certainly be quicker :

Private Sub Report_Load()

Me.txtOpSpecials = DSum("Balance","QryCustomerLedgerOp","[CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#")

End Sub

If you continue to ignore sensible advice people will stop trying to help you.

 

[nector]

Many thanks Minty I will use your method.

Regards

 

[arnelgp]

Private Sub Report_Load()
Dim db As DAO.Database
Dim strSql As String
'Dim prm As DAO.Parameter
Set db = CurrentDb
strSql = "SELECT Sum(Balance) As Totals FROM [QryCustomerLedgerOp] WHERE [CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#"
With db.CreateQueryDef("", strSql)
'    For Each prm In .Parameters
'        prm.value = Eval(prm.Name)
'    Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
            Me.txtOpSpecials = .Fields(0).Value
        End If
    End With
End With
'Set prm = Nothing
Set db = Nothing
End Sub

 

[nector]

Many thanks to you

arnelgp​

After uncommenting the parameters, it just worked the way I wanted it. It's true you are really gifted on advanced programing; this will surely benefit not only me but also the site users even those who will come in future.

I Salute you Sir!

 

[ebs17]

it just worked the way I wanted it

Exactly. Why make it easy when you can make it complicated. The possibility of making errors is then much greater.

 

[Minty]

I give up.

Maybe this complicated flow diagram will assist with my explanation.
Edit: Maybe the OP is being paid by the number of lines of code written, that would make more sense.

 

[Pat Hartman]

Maybe the OP is being paid by the number of lines of code written, that would make more sense.

That is my feeling also. This doesn't seem to be a need for a loop or embedded SQL. This is exactly the correct place for a domain function. Unless of course the form is bound to a table or query with no selection criteria which would force the domain function to run for every record in the table.

 

[nector]

That is my feeling also. This doesn't seem to be a need for a loop or embedded SQL. This is exactly the correct place for a domain function. Unless of course the form is bound to a table or query with no selection criteria which would force the domain function to run for every record in the table.

Sorry for the late reply people!

The Dsum is extremely slow that is the reason why I dropped it I cannot allow users to wait for over 49 minutes to add 2800000 records just to come up the opening balance while the open record set does not care how many records are there, that 2800000 it takes on 10 seconds. Can you imagine the time I'm saving here?

Private Sub Report_Load()

Me.txtOpSpecials = DSum("Balance","QryCustomerLedgerOp","[CustomerID] =" & [Forms]![frmCityLedger]![CboCustomerID] & " AND [ShipDate]<#" & Format(([Forms]![frmCityLedger]![txtDateA]), "yyyy\/mm\/dd") & "#")

End Sub

 

[Minty]

If there is that big a time difference, then you haven't shown us the entire process.

There is no functional difference in the code AS SHOWN, between using a DSum() and opening a recordset, so there is something else going on you haven't shown us, except the DSum should be at least as quick if not quicker.

I wonder if it is because you are using it in the opening of the report?
Why not calculate it before opening and then pass it to the report, it's probably evaluating it for the entire unfiltered report source, assuming you are filtering the report on open?

 

[ebs17]

Can you imagine the time I'm saving here?

You can only save the time you gave away before.

The DSum is not slow because of the simple execution, but because QryCustomerLedgerOp has to be executed. This query is the real evil and cause of slowness.
So you need to look a little deeper under the hood.

With 2800000 records you have to think about the optimal design and performance in time - or a good developer thinks about this anyway, no matter if 27 records or 5800000 records.

 

[CJ_London]

If you are concerned about performance with summing something like 2.8m records, I sometimes create a verified balance record. With stock that might be a physical stocktake record, with customers/supplier ledgers it might be when a balance is zero or audited. Once a verified record exists, no transactions are allowed to predate it. Typically a verified record would be identified with a flag on the record.

To get the current balance, find the (usually the latest) verified record and sum from there. This can also be used if there is a need to archive records. And of course, checks can be run to confirm the verified record is still correct.

On very rare occasions when there is a need to enter a transaction that predates a verified record, any later verified record flags would be reset - so the latest verified record becomes the one that predates the new transaction.

In the OP's posts, no mention that the relevant fields (customerID and shipdate) are indexed - lack of indexing will also be a drag on performance

 

[Pat Hartman]

Using a domain function in a query that selects 2.8 million rows is just plain wrong. That isn't what your post showed. Once you understand the problem, you will never use a domain function in a query or in a VBA loop. Each domain function runs a separate query. So, you might understand that running 2.8 million queries could cause a fair bit of overhead.

There are other solutions but only if we are told the actual problem which you haven't done yet.

 

[nector]

Many thanks to everyone your contribution is very valuable remember you are not only helping me alone there could be others out there who will surely benefit from your advises and in return will help this site to grow and become popular.

I can confirm to everyone especially those who are using the SQL cloud as backend to stay away from domain functions, but if you use the same domain functions with MS Access as front end and MS Access as back end the slowness is not really bad it works just fine. My conclusion here is that Microsoft reserved these domain function to be solely used within MS Access only.

I have got the same FE working with MS BE it works ok, but the same Fe to work with SQL Cloud you have to do away with Domain Function and also tune the backend and index the foreign keys.

 

[Minty]

If you want to really make your application run quickly with that many records, produce views on the server that returns the correct sums per customer and then use a pass-through query to select a single customer record from that.

Any time you are querying that much data the server will produce aggregated results hundreds of times quicker than the same access query, especially if you have set the correct indexes and have examined the execution plans for bottlenecks.

As for indexing foreign keys - that should be a given.