We help IT Professionals succeed at work.

Set a reminder based on a varaible of days after an event

yddadsjd95
yddadsjd95 asked
on
Great Day, I am working on an application for a small vehicle repair shop. It keeps track of customers' estimates and repair orders. It also keeps track of a repair item that the technician may discover while working on a repair order: For instance, the customer may bring the car in for an oil change, but the technician sees that a wheel bearing needs to be replaced. What I would like to do is identify the wheel bearing as something that needs to be done down the road,  but it could be anything from something minor to something major. I have a field that permits the user to identify the replacement of the wheel bearing as something that needs to be done. I also have a field where I place the number of days from the repair when I would like to send an email reminder of this particular service item. The reminder can be set for 10 days or 20 days or 30 days, etc.

What would I need to do to have access remind me that it is now time to send the email reminder to the customer?

Thanks in advance for the assistance.

r/David
Comment
Watch Question

Hi ydd,
Having some code behine the Form Timer even of the main form may be an option
Get the code to do the necessary checks and then display a message box
So you will have a table behind a form that stores service reminder items- something like
Log Date          |      Item        |  No of Days when Due
03 Nov 2011    |   Bearing     |       10


Create a query  that looks at the reminders items table
Have an expression on this query which uses DateAdd to adds No of Days when Due to Log Date
DateAdd("d", [Log Date], [No of Days when Due]) and add criteria where this expression field = Now()

Create a reminder form on this query
In Form Timer or Frm Load use DCount to get count of number of records in above query - if count > 0 display the reminder form with the reminders
   
You could take it a step further where you put code in the Timer even which loops through all records in the above mentioned query and auto-generates an e-mail for each

Also you will need a customerid and vehicle id or something like this on the reminder items table to say who the reminder relates to and which vehicle

Author

Commented:
Thank you BCUNNEY, I will work on it this morning. I have an hour or so before I have to leave for an appointment. I'll keep you posted.

r/David

Author

Commented:
BCUNNEY, Taking it one step at a time, I've created the query and placed the expression that determines whether an email reminder is sent "DateAdd("d", [Log Date], [No of Days when Due]) and add criteria where this expression field = Now()". This is the expression that I created:

ServiceReminder: DateAdd("d",[RepairDateOut],[RemindInDays])

where "RepairDateOut" is the date that the repair was completed and the vehicle is picked up, and "RemindInDays" is the the number of days before a reminder goes out. The ServiceReminder field displays the proper date, so step one appears to be completed.

Step two is the criteria: this is what I created:

("ServiceReminder"<Date()-6 Or "“ServiceReminder”"<Date()+6)

If I am understanding my own logic, only records that have a reminder date between today's date - 5 days and today's date + 5 days, which basically gives me a 10 day window to send out the reminder. (I'm trying to take into account the days that the shop may be closed or the secretary is out of the office and the computer does not get turned on.) However, all of the records are still displaying. Right now, I only have two records that have a repair item reminder, and based on the "ServiceReminder" field, one reminder is in the past on September 1, 20011 and the other reminder is on January 10, 2012. Consequently, based on the criteria above, between October 29 and November 8 , then neither of these records should display, but they both do.

Thanks thus far,

David

Change the OR to an AND

Here is a sample I put together

SELECT tblReminder.ID, tblReminder.RepairDateOut, tblReminder.RemindInDays, DateAdd("d",[RepairDateOut],[RemindInDays]) AS ServiceReminder, Date()-6 AS StartDate, Date()+6 AS EndDate
FROM tblReminder
WHERE (((DateAdd("d",[RepairDateOut],[RemindInDays]))>=Date()-6 And (DateAdd("d",[RepairDateOut],[RemindInDays]))<=Date()+6));

Author

Commented:
BCUNNEY, Thanks for helping me through this first step. It is exciting to see code give the desired results.

Have a great evening and an even greater day tomorrow.

Sincerely,

David

Explore More ContentExplore courses, solutions, and other research materials related to this topic.