Help! My vba code executes too slow.

[spenz]

The code below executes okay and does what i want. The thing is
it's damn too slow for a measly 3,000 records this code executes for about 10seconds. What if the records have already reached hundreds of thousands users could probably be waiting the whole day ouch!..

I am looking for suggestion or an alternate code that does the same but executes a lot faster than my code below. hope anyone can help. Thanks in advance

On Error GoTo ErrHandler
Dim db As Database
Dim rsRecTo As DAO.Recordset
Dim rsRec As DAO.Recordset

Dim rsDays As Integer
Dim rsDate As Date
Dim lngTranID As Long
Dim intAnswer As Integer
        
    intAnswer = MsgBox("Update interest?", vbYesNo + vbQuestion, "CONFIRM")
        
    Select Case intAnswer
        Case vbNo
            Exit Sub
        
        Case vbYes
        Set db = CurrentDb
        DoCmd.Hourglass True
        
        Set rsRecTo = db.OpenRecordset _
            ("SELECT * FROM tblReceivables WHERE Active = -1")
           
        Do Until rsRecTo.EOF
            lngTranID = rsRecTo!TransactionID
        
        If DCount("*", "tblReceivables", "TransactionID = " & lngTranID) = _
            DCount("*", "tblReceivables", "TransactionID = " & lngTranID & " And isnull(Paydate)") Then
                rsDays = Date - DFirst("TransactionDate", "tblReceivables", "TransactionID = " & lngTranID)
        Else
            Set rsRec = db.OpenRecordset("tblreceivables", dbOpenDynaset)
                rsRec.FindLast "TransactionID = " & lngTranID & " And not isnull(PayDate)"
                    rsDate = rsRec!PayDate
                    rsDays = Date - rsDate
        End If
            With rsRecTo
                .Edit
                !TotalDays = rsDays
                .Update
                    rsRecTo.MoveNext
            End With
        
        Loop
            rsRecTo.Close
            rsRec.Close
            Set rsRecTo = Nothing
            Set rsRec = Nothing
            Set db = Nothing
            
            DoCmd.Hourglass False
            Me.Requery
            MsgBox "Update Successful!" & vbCrLf & vbCrLf & _
            "Thank you for waiting.", vbInformation, "Interest Update"
    
    End Select
                              
Exit_Now:
DoCmd.Hourglass False
Exit Sub

ErrHandler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Now
Resume

What's the code on the do while loop about:

It is used to determine the number days to get the specific interest value that the client had to pay based on current date and his/her latest payment date if client had already paid. If client has not yet paid, then current date minus the very first transaction date will be used to get the number of days instead.

 

[Banana]

Seems to me it can be expressed in a query without any VBA at all.

SELECT 
   Paid,
   Iif(Paid, Date - PayDate, Date - (SELECT Min(PayDate) FROM tblReceivables s WHERE s.TransactionID = p.TransactionID)) 
FROM tblReceivables p 
WHERE Active = -1;

Notes:

1) I'm not clear how you determine whether a client has paid or not, I see a null check on PayDate but this may or may not be sufficient depend on exactly how it's structured.

2) We don't sue First() because it doesn't what you think it's doing. Min() is actually the correct function to use to get the very first date.


You wouldn't need VBA at all once the query is correctly written beyond the routine to pop a messagebox and run query.

 

[spenz]

Hi banana thanks for replying.

The database I'm creating is all about money loan. This is my very first one so i was quite excited when i finished it last week i thought i was really done and all. But user wanted an update for the interests of clients. So i tried to make a button named cmdUpdate that when clicked; it would execute a loop to update all interest based on current date minus their very first date of transaction if client had not made even a single payment at all. If client had a payment already then the computation would be current date minus the latest payment date. The resulting number of days will be used as part of the formula to get the interest value for that client.
The formula to get the interest is: Amount X Rate / 30 * NumberOfdays

The main thing is to get the number of days for the user to get the exact interest value that the client owed them.

By the way, are you suggesting that i could use an update query instead of vba looping code to update interest for the entire table? Because i've tried it already, in fact, that's the first thing i did. My problem is the limitation of setting the condition on where to get the number of days.

so for example: if i use this code
"UPDATE tblReceivables set NumberOfDays = Date - min("TransactionDate","tblReceivables","TransactionID = " & me.TransactionID) where Active = -1"

This one will update all the NumberOfDays fields based on current date minus the very first transaction of that client for the entire table for as long as the transaction is active yes. But what if the client had already paid, the NumberOfDays value should now be:
Date - Dlast("DatePaid","tblReceivables","TransactionID = " & me.TransactionID) .

So basically, I only had to choose one condition, that's why i opted to use vba looping technique instead just to get the right condition that i wanted for the NumberOfDays and I've given up on using the update query method because i thought; with it, you are only limited to certain condition like my example above and not be allowed to pull multiple conditions.

Sorry for the long talk i thought i had to make my problem clearer. Thanks again banana

 

[Banana]

My example above was not an update query but rather a select query formatting the output. If we wanted to update the values, the query would have had used UPDATE, not SELECT.

As for having only condition, I don't think that's accurate. You can embed more than one condition by something like this:

UPDATE tblReceivables SET NumberOfDays = Iif("DatePaid" IS NOT NULL, X, Y);

But... I don't think you really want to have a column in your table named 'NumberOfDays' as this is a calculated columns and as a rule we don't usually store calculated store. We always derive it in a query:

SELECT (LastDate - FirstDate) As NumberOfDays
FROM tblReceivables;

Now for your actual formula, I suspect it'll be more complicated because we need to look up the first transaction dates for clients without any payments, then look up the last transactions for clients who has paid, then use that information to help us determine what formula to apply for interest.

Therefore, you need a bunches of queries here.

1) A query to retrieve the first transaction date where there are no payments made from clients.
2) A query to retrieve the last paid date from clients where there are payments.
3) A query to join the original table to both queries and either select or update the interest as required.

Try and build each query separately and you can bring it together in #3 and you will be able to get the ingredients you need for the formula. That said, I have to retiterate: Do *not* use First() and Last() because those may not necessarily do what you think it's doing. What it actually does is retrieves the first or last record entered, irrespective of the value it contains.

For example, if you by mistake enter the 2nd payment made and realize the mistake, enter the 1st payment that was made earlier in the calendar, the First() will retrieve the 2nd payment because it's the first record entered. In this case, you want to use Min() or Max() to get you the 1st payment or the last payment.


HTH.

 

[spenz]

Very nice and thank you very much banana. I think you got my problem right. And as of speaking i realized i had a lot of dlast() and dfirst() in my codes and iam about to change all that. Thanks for the tip about it i would have had never known if it wasn't for you. I really thought it functions the same as dmin() or dmax().

Yes, I really have a NumberOdDays column in my tblReceivables. I realize it's bad to store calculated field but users wanted it so they can manipulate the actual number of days as they wish. I told them we can adjust it through payment date or transaction date but they said they wanted to make those dates original as possible. So I had to make that extra field. I don't know if it's justified; but as of now, that's the only way i can think of to solve that particular problem.

As for the actual problem, yes it really got complicated for me. And although, the first code i made earlier executes ok but as i mentioned earlier it's painfully slow so iam determined to get an alternative code for this one. Thanks to you hope is up and future is promising.^_^

I will try to build 3 query ingredients you've suggested. Oh my! I feel there will be a lot of changes in that database. . Thanks again banana for your critical analysis and sharp advise.