Trigger event to update all records on application open

[blogmk]

There are around 100 customers.And on application open, need to check if customer has paid invoice. If the customer has paid/not paid need to change the status accordingly. Form on open event changes status of only the first customer.Form on current changes status only if we move to the specific record. Is there an event to trigger to check the entire table records on application open?

Please help.

 

[Mr Smin]

If you only check when the database is opened, what will happen if status changes during the day? I imagine someone keeping the database open all day so the status won't get refreshed.

 

[blogmk]

Mr Simn , U r right. Is there an event to update table every 4 or 5 hrs?

 

[pr2-eugin]

blogmk, I am not sure how is your table structured, but this is just a guess.. If your table has the invoicePaid field as True/False.. Then create an UPDATE query something like..

UPDATE tableName SET customerStatus = "Not Paid" WHERE invoicePaid = False;

Save the Query and use an AutoExec macro to run the Query...

 

[blogmk]

I have a date when invoice is generated. Once the invoice is generated the status changes to due. If not paid within 21 days the status changes to overdue. If not paid within the 3 weeks of overdue then the status changes to stop.If the customer is new then the status is Active. And I have a option to enter vacation weeks. Like Vacation starts on week 36 and ends on week 39. then from week 36 the status is vacation. All these needs to checked for every record when the form/application is opened. And also need to check 3 times a day if the application is kept open. Currently i'm using form on current event. But this triggers only when you move the records in the form. Is there any other way? Thanks

 

[blogmk]

I have a date when invoice is generated. Once the invoice is generated the status changes to due. If not paid within 21 days the status changes to overdue. If not paid within the 3 weeks of overdue then the status changes to stop.If the customer is new then the status is Active. And I also have a option to enter vacation weeks. Like Vacation starts on week 36 and ends on week 39. then from week 36 the status is vacation. All these needs to checked for every record when the form/application is opened. And also need to check 3 times a day if the application is kept open. Currently i'm using form on current event. But this triggers only when you move through the records in the form. Is there any other way? Thanks

 

[Mr Smin]

You have two things to do:
1. Apply the business rules (the status changes based on dates)
2. Update status every so often

I think you can cover this with what pr2-eugin wrote, but make the query more detailed to cover the other options.

But can you describe your setup a bit more? I imagine you have an Access db with a back end and front end(s). One of your users is inputting when payment is received and another is chasing late customers.
If that is true, then you really should just update when a record is opened in a form as that will be the most up to date information available.
The other time you want to do a big update is for end of day/week reports. Again , no need to schedule this, just do the update at the time you run the report.

 

[blogmk]

Front end and back end is both access. I have a form to input all basic user information. The first time the customer is added the status of the customer is Active. There is a Invoice button in the form.Which on click generates invoice, and the day the invoice is created is the invoice sent date which is updated in the table. The status changes to due automatically. After 3 weeks the status needs to change to overdue automatically for respective customers if not paid. After 6 weeks from invoice sent date the status needs to change to stop automatically.

There is a separate form to see reports.Its just few combo box and generate report button.

Is it possible to verify the date for each customer and change the status accordingly without clicking next for each record.

How do I do this?

 

[blogmk]

Thank you. It works.