automatic email if stocks drop below a certin level

[Chris987]

hi,
im a novice with access but ive designed a basic stock control program for my boss which displays stock based on a calculation of [Tdeliveries.addition] Minus [Tsale.deduction] in my TProducts table i have a field MinStockLvl is there anyway to automate an email to be sent if the calculation is <= to the MinStockLvl for that product i have very little understanding of VBA but im learning fast but im not sure if this can be done as the stock level is based on a calculation. items are scanned out of our warehouse i would ideally like the email to be send the moment the item is scanned out but if it has to be a cmd button that i press and the end of the day to send and email with all products that match the criteria that would do

 

[MitchRandall]

There are several parts to this.

1. Think about how often you want to receive notification and how you flag a particular item as already being notified. I would add a date field to contain the email notification date.

2. Create a query that retrieves all the stock records that require notificateion and have a blank email notification date.

3. Generate the emails based on the records that are retrieved from the query. It could be data from the record itself or a report that lists it (just email the PDF of the report).

4. Update the email notification date field after the email is sent.

5. Decide how often a check should be done. Maybe daily?

You may be able to do this with a simple Docmd.SendObject command. Here's some info on it along with some limitations:

http://www.fmsinc.com/MicrosoftAccess/Email/SendObject.html

I use Total Access Emailer from FMS to automate my emails from Access. It runs as an add-in, so you can use it without any VBA programming (you'd have to come up with the queries of what you want to email). It also has a VBA library that you can tie to an event like a button on a form or a macro that can be run everyday at a specific time. They have a free trial here:

http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Hope that helps.