asked on

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

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

Barry Cunney

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

SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Barry Cunney

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

yddadsjd95

ASKER

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

yddadsjd95

ASKER

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

ASKER CERTIFIED SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yddadsjd95

ASKER

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