Is There a way to make this work?

[rshaw63]

Hello Everyone,



I was wondering if it was possible to when my access application loads to main screen i would like to check dates in a table field and automatically send an email to one person if the date matches current date. I have seen how to create a button to do this but i was wanting this automated as so i didnt miss any dates. the field is time critical so it has to been renewed by the date



As you can see above when the Renewal_Date field = now() send email to caller email.

thanks in advance for all that you guys do.

[Welshgasman]

Just put the code you would use for a button in the load event of the main screen.
What happens if the user opens the db more than once in the day?
You would not use now(), but date()

[rshaw63]

Usually The Application once opened stays that way for the day.

So i have a form called mainmenu that default loads when the application is started put that code on the load function of that ?

[Welshgasman]

Yes, but compare against date(), not now().

[orange]

Use Date as Welshgasman advises. Date provides today's Date only. Now provides today's Date and Time which you do not want.

[Micron]

Usually The Application once opened stays that way for the day.

That might be OK if there is only ever one user per day, or as long as the db doesn't barf and has to be closed and re-opened. It's also OK if there's no way to re-load the startup form. There ought to be a date field in some table that gets written to when this happens so that it doesn't happen more than once per day. That's like the difference between sophomore and senior.

So i have a form called mainmenu that default loads when the application is started put that code on the load function of that

Or use an AutoExec macro, but if I did that, I'd use it to run code rather than try to figure out how to actually do it with a macro. I don't actually use macros to do much of anything beyond running code.a

[orange]

@rshaw63

What email product do you use?
If you open your application on Monday and the renewal date was the Sunday(yesterday) or a holiday your equal will not work.

In general you will need some logic to determine if the renewal date is <= Date and some flag to say renewal Sent for this Year Yes or No. It would also seem that a query at startup to identify those People/Companies whose renewal date is <= Date and flag renewal Sent = No would be a good start. It the query has >0 hits, then you'll need code to process/send emails to that list.
As micron said, most will use vba/code rather than macros.

There are several articles re Sending Emails from Access. Here google will be your friend.
There is an email related link that may also help with options. Also see the Similar Threads at the bottom of that linked page.