Send email if overdue (1 Viewer)

[Harry Taylor]

Hi All,

I have a vehicle database with MOT or service due dates. Is it possible for access to send an email 1 month before the due date as a reminder that the service is due?

 

[Gasman]

Yes.
Probably do it on startup each day, and mark it as sent, so you do not do it again on next startup.
Or have a table with the next inspection and inspect that for a certain date range.

I have to hope that is a crosstab you are showing?

 

[arnelgp]

it can be done of course.
also you need another table where you will save those records that have already been sent notice of service.

tblNoticedServed (table)
ID (autonumber)
Registration (your registration number field)
Service Due (your service due date)

the query to filter your table:

select A.*, B.ID from yourRegistrationTable As A Left Join tblNoticedwhere As B
On A.Regsitration = B.Registration And A.[Service Due] = B.[Service Due]
Where A.[Service Due] <= DateAdd("m", 1, Date()) And (B.ID Is Null)

you get the recordset from the query and loop through each record to send the email.
after sending the email, you add those records to tblNoticedServed table so that next time you
do same routine, those records already been sent noticed will not be again included on the recordset.

 

[Pat Hartman]

Also, you might want to consider normalizing your tables. The types of service belong in a separate table with one type per row rather than as columns in a single table. That allows you to support any type of service at any level of detail. You display the services as a subform as you have them. But instead of the sparse columns with the 1's scattered, you have ONE column that says what the service was. Tyre, Break, Clutch, etc.

 

[Harry Taylor]

Excellent advise, thank you all.
I'll work on the suggestions, but if there are any more, please keep em' coming.

Thank you

 

[gemma-the-husky]

Yes. Your query to select the vehicles needs to not include vehicles that have been sold or otherwise disposed of, or even still owned but taken off road for an extended period for repairs, which is likely to happen with a large fleet.

The service, or particular service elements might also depend on vehicle mileage, so it might all become a little less straightforward.