Datasheet displaying Aging records problem

[OC2]

Ive created an aging report inwhich all the coding is done using VB on a form. When a record is over one day old the status text box changes from "open" to "overdue and highlighted red.

when creating a record and skipping a head one day, I have a problem where, when viewing as a datasheet or continous form, only by clicking the record will it update its status to show its over due.

Is there anyway to make the form update all the records to show the correct data instead of having to cycle through all of them first to get the updated status?

 

[AlexMidd]

You would have to use an update query and call it in code or loop through all the records and change the field value based on the other fields' values. The first option is easier to code.

You could use the query design grid to design the query (ensure that it is an update query), select the field to be updated and what to and the criterion (i.e. date older than 1 day). The use the SQL view to see the SQL sytnax.

Copy the SQL into a code module and assign it to a string variable, e.g. strSQL = &quot;<SQL>&quot;. Then use the runSQL command to run it. I have not tested this so I'm not sure how it will work, but hopefully this will give you an idea of where to start. Have fun! )

Alex Middleton

 

[OC2]

Hi, There are too many variables to perform an update query.

An example of what my aging report already does is,

I have a field called Status. The aging report recognises that if the status = &quot;escalation&quot; &quot;Pending&quot; &quot;closed&quot; &quot;assigned&quot;
to ignore them and leave them as is. But if the status = &quot;Open&quot; I have some code that will calculate the time period from transaction open to transaction close. If the transaction ages by 1 day the status will change the &quot;open&quot; to &quot;overdue&quot; and highlight it in red. All this works beautifully. My report can tell me the status of the transaction before it was closed ie before it was closed it was &quot;pending an investigation&quot; and so on.

The only problem im having is that after a day all the &quot;open&quot; remain as open and in black font color. only when I click on the record will it update to the &quot;overdue and red font color, which is defeating its purpose. This is only the first stage, once I have got this established I intened to enhance it to an aging report per category of transaction (multiple timelines) An update query for this problem seems impossible. This has to be done through VB code.

Is there another way I can automatically update all records to my forms code when opening the database in both main and sub form?

Thx