I have been collecting a lot of very nice tools from this site and want to put a few of them to good use. I've got s situation that I need some brainstorming from others on feasability and paths of least reisistance.

I've got an established working db that is full of hundreds of records of customers containing all sort of vital information on each. The users of this db have made a request that I'm having a hard time wraping my brain around for some reason. My problem is defining what a tirgger mechanism would be in this situation. Here's the details

End users want to be reminded some action is required when a specific date field has become less than 30 in the future. When there's less than a week, they want some sort of extra pop-up message to appear to remind them in stronger terms...something needs to be done in regards to this specific record and soon!

The field in question would be a date field. Right now it's manually input by the end users but I'm going to change that so it's automatically generated based on a manual input in another date field. The auto-gen date will be exactly 1 year after end user puts in a date in the other field. Now the end users want two unique reminders to prompt them when it's getting close to that autogenerated date.

With one month left, they want the date field (or others for that matter, let's not get too restrictive at this point) to change colors to draw attention to it and remind them they need to do something in regards to that record soon. In addition, they also want some sort of pop-up dialog box or warning message to come up when there's less than a week to go before we hit that autogen date.

My inital thought is this can be done, but what would trigger these reminders? Basically, this autogen date would sit there doing nothing for 11 months then it would start "lighting up" when the date is approaching.

aTdHvAaNnKcSe

How about putting your code to change the back color and to pop up a form in the forms On Current event?

Okay, both triggered by the same.

Current date <= autgen date - 30 {days} and current date <= autogen date - 7 {days}?

Will code just automatically recognize the current date is getting close to the autogen date? I think that's what my main concern is. I thought I needed to do a date comparision somewhere in the code to trigger the action.

Maybe I'm just having a non-creative day. :(

You can use the DateDiff() function and if the date difference is 7 days or less then popup up the message. If it is between 30 days and 23 days then change the color. Search Access help for DateDiff() to see the particulars for this function.

hth,
Jack

Thanks Jack,

I was searching the forums and found the right area's on here for clues to a solution. DateDiff() is the way to go on this one.

I should have put this in the Access Date & Time for sure. Sorry folks. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

You are welcome and where you placed your post worked out just fine...

Jack