Traversing through records.

[pr2-eugin]

I have a Form which is bound to the Customer table. I have a scenario, in which I have to switich the policy status of the customer to 'Failed' if the customer payment has not been recieved in 15 days from the date of first inception.

So I have used the Form_After_Update event. This works fine, but it requiers me to go into the record and come out everytime, If it needs to be updated.

So I used the on timer event to open the form and traverse from the first record to the last. This does the trick. But takes around an hour to do it, as my table has around 8000 records. Now is there anyway it can be a bit faster? I would appreciate any help. Thanks.

-- Paul

 

[Trevor G]

You can use an Update Query to do this and run the query when the Database is opened via a macro.

 

[gemma-the-husky]

even if you do it in code, an hour for 8000 records is much too long. a minute maybe at the most.

can you show us your update code?

 

[jdraw]

Have you considered running a query to identify those records/policies where
the customer payment has not been recieved in 15 days from the date of first inception?

A select query could identify such records. As Trevor says, the selected records could be updated en masse with an Update query in a few seconds.

OOOPs: I see Dave has answered while I was typing and doddling.

 

[pr2-eugin]

Hi Trevor, Dave and jdraw.. Thank you for responding.. But the way I have done is. I have used the following code..

Private Sub Form_Timer()

If (Format(Now(), "hh:mm") = "00:01") Then
    Dim c, i
    c = DCount("[CUSTOMER ID]", "Customers")
    
    DoCmd.OpenForm "Customers", acNormal
    For i = 1 To (c - 2)                          'iterate through all records (c-1)
        DoCmd.GoToRecord , , acNext
    Next
    DoCmd.Close acForm, "Customers", acSaveYes
    
    Exit Sub
End If
End Sub

So as in the code, I have trigged (sort of) at midnight everyday to run the open the form and move from one record to another (my way of updating; as I have most of the functionalities, such as Switiching policy status, Updating the next installment date, Clearing messages (in memo fields) if they are over 90 days.. )

All the functions are available only inside the Form's after update property.

So it is technically not using a update query, but moving from one record to another. Hope that is clear.

--Paul

 

[missinglinq]

...I have used the Form_After_Update event. This works fine, ...

I'm curious as to how you changed the value of a field using the Form_AfterUpdate event! Doing so should make the Record Dirty, again, throw you into an endless loop, and never let you exit the Record.

How many Values besides "Failed" can the Control have? If there are only two possible Values, in addition to the suggested Update Query, you could use the Controls' Control Source and the IIF() function to populate it.

Linq ;0)>

 

[pr2-eugin]

Hello missinglinq.. You are correct.. I am so sorry.. I have it wrong, they are placed in the Form _Current property.. Ther is only one functionality present in the Form_After_Update property, for which I have used an update query.. Sorry about that... There are four outcomes.. Failed, Active, Lapsed, Cancelled..

 

[ypma]

if only failed and else blank you could use and update query ; i have tried the following and updates to Failed on all blank fields where the date diff is greater than 15 days.


failed DateDiff("d", [Policy Date], Date()
table name

Updated to: FAILED
Criteria Is Null > 15

 

[sparks80]

Hi Paul,

I think others are right to point out using an update query. You can still call the update in exactly the same way as opening a form, unless I have misunderstood.

You have a table called Customers and I assume a field called something like InceptionDate. You can work out how many days have passed since the Inception Date by calculating DateValue(Now()) - InceptionDate. Now you can return the records where this is greater than or equal to 15.

This should return the subset of records in the database that require updating. You probably then have a status field that you can update for these records to "failed".

Example

UPDATE Customers SET Customers.Status = "Failed" WHERE DateValue(Now())-DateValue(InceptionDate)>=15

From your form you can now use the timer to call the query in the same way as before:

Private Sub Form_Timer()
  If (Format(Now(), "hh:mm") = "00:01") Then
    Dim qry as QueryDef
    set qry = Currentdb.QueryDefs("YourQueryName")
    qry.Execute, dbFailOnError
  End If
End Sub

You can create a similar query or queries that will set memo fields to null, or modify dates.

I think you will find this is significantly faster than transversing the dataset with the form open.


As an alternative you could open the form, and instead of traversing each record in the form you could do the same using code like this:

Sub TraverseRecords()
    Dim rs As DAO.Recordset
 
    Set rs = Me.Form.RecordsetClone
 
    Do While Not rs.EOF
       If DateValue(Now()) - Me!InceptionDate >= 15 Then
           rs!Status = "failed"
       End If
       rs.MoveNext
    Loop
End Sub

 

[pr2-eugin]

Wow.. that is so nice of you all. Thank you so much for giving in your time to help out. I really appreciate it. I will give all your suggestions a try and get back.. Thank you sparks80 for taking the time in explaining it.

 

[MarkK]

A purist would suggest that you never store information that depends on a calculation using the current date. Notice that your age is not printed on your driver's license, but your date of birth is. Store the facts and calculate the interpretation as required.

You can bind your form to a query like...

SELECT c.*, IIF(Date()-c.InceptionDate >= 15, "Failed", "") As Status
FROM Customer As c

... and Status will appear and function exactly like any other field.

You can also calculate the value of unbound controls on a form using the Current event.

Information is a bit like a rope, so mostly you want to pull on it. Pushing is not so effective.

Mark