need help figuring out if linking access to outlook is an option.

[JRatHairmax]

This is what I want to try and do.

I have just been put in the position to oversee my companies inventory system. we are using MS 2003 access to acomplish this, which is also very choppy from the way it was put together.

With alot of redundant work being put in on my end to update the inventory.

Unfortunately we dont have a programmer and the only programming knowledge I have is one semester of Python langauge.

Now with this basic information in mind, I want to try and create an alarm in access which sends me a msg that my current on-hand quantity has reached at or below re-order level (while also checking to make sure that items are not on the order list)...

I dont know where to look, the only idea I had was to interface access and outlook together. so that when it runs a script to accomplish what i want to be my End-of-Day reorder report, that i will receive a msg in my outlook with the product idroduct type/h qty:reorder level:and vendor information assosiated with that particular product.

This would kill the redundancey of having to run a report and manually having to address these issues and then having to search the system for the information for me to begin working on the PO for the material.

there is a thread reply posted by JonFer in the forum where he describes a script that can be use to send an alarm into outlook; however, it is not what i need entirely, just mainly the idea.

so basically the script would:

1. have a library
2. check products > check for o/h qty > order qty > check it against the reorder level. and then follow two different paths:
a. first check the critiria and if it falls below the reorder point an email is then created and sent with information > continue on to the next product.
b. or it would read the product check the information > continue to the next as long as what it reads is true.
3. and it would loop once through the list of products from the first one to the last one..

now i dont know if it is too much or if there is a simple script based on sql that can easily accomplish this task for me.

 

[MazeWorX]

you could simply runa query which would look for items based on certain criteria. if found would email a snapshot report to you via your email. so yes is the answer to your question. This can be accomplished quite easily with vba code and a report. Additionally vba could loop through the records and accomplish the same inventory checks.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[JRatHairmax]

Thank MazeworX,

However, could you give me an idea as to how or where I could look for a vba code?

Again many thanks.

~JR

 

[MajP]

Having Access send an email to outlook is relatively easy. There are many ways to have the database run code at a specified time. So this is relatively easy, but you will need to be very clear on your buisness rules, your system design, and your desired results

1)Lets first clearly define the requirements and understand your buisness rules.

Your System:
Access 2003 database
Multi user?
Split front end back end?
On a network?
Sql backend?

How do you want this to run:
Everyday at a given time?
When the user closes the db?
Anytime an update?
At a give time if someone is in the database?
At a given time if someone is not in the database?

Redefine these as requirements. Do not state how it should be designed, but focus on inputs and outputs.

1. have a library
2. check products > check for o/h qty > order qty > check it against the reorder level. and then follow two different paths:
a. first check the critiria and if it falls below the reorder point an email is then created and sent with information > continue on to the next product.
b. or it would read the product check the information > continue to the next as long as what it reads is true.
3. and it would loop once through the list of products from the first one to the last one.

I believe the requirements are simply:
1.Send an email to a specified user for all products in the DB where (O/H quantity - daily orders) < reorder level

Document all tables and relationships with the following Faq700-6905. Post these tables and relationships