Date Calculations/Review Trigger

[John Lee]

Good day,

I have a form name Internal Audits (frmInternalAudits) that I have created to track when SOP's (Standard Operating Procedure) are due to be audited. I have the following fields:

txtAuditSubject (Short text field)
txtCurrentDate (Date field [ddmmyyyy])
txtNextReviewDate (Date field [ddmmyyyy])
txtReviewed (Yes/No field)
txtInternalAuditors (Short text field)
txtAuditoremailaddress (hprLink field)
txtSOPOwner (Short text field)
txtSOPOwneremailAddress (hprLink field)

I believe that the code I need should be written in the "On Got Focus" event, of the form.

What I would like to achieve is work out from the current date 12 months forward and enter that date in the txtNextReviewDate field.
On reaching the next review date if the txtReviewed box is ticked then the txtCurrentDate becomes the txtNextReviewDate and a new txtNextReviewDate is generated on the txtCurrentDate be updated.

I think the code for this bit would need to be written elsewhere, but to be honest I'm not sure where that should be.

Also I would like the Auditor and SOP owner to be notified four weeks and two week prior to the txtNextReviewDate that the Audit Subject is due to be audited, by opening Outlook and sending an automated email to the Auditor and Owner of the audit subject.

Also if the next review date (txtNextReviewDate) has passed without being reviewed (the txtReviewed is not ticked) I would l like an email to be generated to both the Auditor and Owner every 7 days until the Audit is completed (This will clearly only happen when the database is open, because it won't be open 24/7).

I hope I've posted this in the right place, I know I'm asking a lot and I hope there is someone that can help, which will be most appreciated.

John

 

[GaP42]

A couple of things:

1. txtCurrentDate - is the name of the control which is the date the last audit was completed?
It would be a simple matter using the OnCurrent event of the form to perform a datediff calculation to determine the days elapsed between the txtCurrentDate and Date(): or more likely Days Remaining till the next Audit is due - like (datediff("dd", txtCurrentDate + 1 year, date()). You can then use this to determine if the due date has passed - negative days, or less than 30 days or less than 14 days: Your form can highlight these situations - showing the datediff result.

2. In respect of notifications: Do you want to view each Audit record, and if the form shows the highlighted situation referred to in 1. that you want the ability to initiate the notification or do you want an automated process to run against all the Internal Audit records identify imminent audits (less than 30 days etc) and another for those overdue? The second option is an automated process your app might undertake each day, each week, each fortnight, month (?), that initiates when the application starts or .... - it is not based upon your form.

3. Given that you can calculate txtNextReviewDate as txtCurrentDate + 1 year - is there really a need to store this?

On completion of the new audit the txtCurrentDate is updated, and this record no longer falls within the criteria that includes it in the reports.

 

[CJ_London]

think you need to clarify some of your process definitions

- add a a year - 4/2 weeks before... Do you run a 7 day a week operation? what if when you add a year it is a weekend or public holiday? What if no-one opens the form on the required day?

 

[LarryE]

Good day,

I have a form name Internal Audits (frmInternalAudits) that I have created to track when SOP's (Standard Operating Procedure) are due to be audited. I have the following fields:

txtAuditSubject (Short text field)
txtCurrentDate (Date field [ddmmyyyy])
txtNextReviewDate (Date field [ddmmyyyy])
txtReviewed (Yes/No field)
txtInternalAuditors (Short text field)
txtAuditoremailaddress (hprLink field)
txtSOPOwner (Short text field)
txtSOPOwneremailAddress (hprLink field)

I believe that the code I need should be written in the "On Got Focus" event, of the form.

What I would like to achieve is work out from the current date 12 months forward and enter that date in the txtNextReviewDate field.
On reaching the next review date if the txtReviewed box is ticked then the txtCurrentDate becomes the txtNextReviewDate and a new txtNextReviewDate is generated on the txtCurrentDate be updated.

I think the code for this bit would need to be written elsewhere, but to be honest I'm not sure where that should be.

Also I would like the Auditor and SOP owner to be notified four weeks and two week prior to the txtNextReviewDate that the Audit Subject is due to be audited, by opening Outlook and sending an automated email to the Auditor and Owner of the audit subject.

Also if the next review date (txtNextReviewDate) has passed without being reviewed (the txtReviewed is not ticked) I would l like an email to be generated to both the Auditor and Owner every 7 days until the Audit is completed (This will clearly only happen when the database is open, because it won't be open 24/7).

I hope I've posted this in the right place, I know I'm asking a lot and I hope there is someone that can help, which will be most appreciated.

John

Just looking at your question and the fields in the form, it appears you are attempting to use ACCESS as you would a spreadsheet. Would you be willing to take a screenshot of your Relationships window and copy it into a Response post so we can see what the initial design looks like? The Auditors names, SOP Owner and Audit Subject should all be un separate tables, then brought together in a junction table that includes Foreign Keys linking the three tables. In addition to the three Foreign Keys, that junction table (let's call it AuditReview) should also hold the Reviewed field. The CurrentDate and NextReviewDate should be calculated in a textbox on your form and not stored in a table field.

 

[Pat Hartman]

The second option is an automated process your app might undertake each day, each week, each fortnight, month (?), that initiates when the application starts or .... - it is not based upon your form.

Let me emphasize this suggestion. It is the ONLY solution. To implement it, you use a query that selects the records in a specific date range. Then you have to decide how frequently you want to do this and that will determine the date range you use. Other considerations.
1. WHO will generate the message? Once you decide how frequently to generate the list, should every person who logs in get the notice or only "John"?
2. Will you log the notice so that it only happens once a week, month, day, etc?

Once you get on board with this as a solution, we can help you to refine the details that control when it runs (always automaticially but you should have a menu option so someone can run it on demand also), who gets prompted, and the rest of the rules.