MS Access VBA Stop Function that sends email based on value of form textbox.

MS Access VBA Stop Function that sends email based on value of form textbox.  I want it to send out the email based on the first time the value exceeds 10 but not after that when additional records are added for the same date.  How can I do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to store whether or not the email has been sent. Without knowing any more details it's hard to give you good advice, but essentially you'd either add a "flag" field to the table, or use a separate table to store when an email is sent.

For example, if I have an Invoice table like this:


And I want to know if I've sent an email to that customer, I could add a new field to the table:

Email_Sent (boolean)

I'd then set that to True when I send the email. Afterwards, I can check that field to determine if I need to send an email.

Often, however, you may need to send multiple emails for the same item. For example, you may need to send an email containing the invoice at creation. YOu may also need to send followup emails if the invoices is unpaid after 30 days, again at 60 days, and so forth. In cases like this you'd need a table to store when those different emails are sent:

Item_Type (Invoice, or perhaps PO, etc)
Item_ID << the Primary Key value of the item
Alert_Type << eg Invoice_Sent, 30_Day_Notice
etc etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.