Electronic Authorization using Access and e-mail

[rochelle]

I've had a request to make our purchasing system paperless. I've been trying to figure out how to do this but I'm floundering.&nbsp;&nbsp;Here's what needs to happen:<br><br>1) The end user fills out a request form of some kind that details what he wants to buy and why. Question:&nbsp;&nbsp;What format should this form take?&nbsp;&nbsp;Access?&nbsp;&nbsp;HTML on our intranet?&nbsp;&nbsp;Word?&nbsp;&nbsp;Excel?<br><br>2) The form needs to be authorized by the end user's manager.&nbsp;&nbsp;Questions: How should the manager be notified that there's a new form to be reviewed?&nbsp;&nbsp;How should the manager indicate approval/disapproval?&nbsp;&nbsp;How should the form be routed to our purchasing agent/end user under the various choices available in 1) above?<br><br>3) If the purchase is approved by the manager, the form needs to go to our purchasing agent and she needs to get 3 competitive bids that need to be added to the form.&nbsp;&nbsp;She then sends it to our CFO for approval.<br><br>4) If the CFO approves the purchase, he needs to indicate which bid to accept and the information on the form needs to be transmitted to our Purchase Order system (Access97)for processing&nbsp;&nbsp;-- preferably with no rekeying of data.<br><br>Does anyone have anything like this working in whole or in part?&nbsp;&nbsp;Any suggestions or things to watch out for?&nbsp;&nbsp;Not everyone in our company has Access so the form needs to be in some format that's accessible to everyone.<br><br>All help eagerly accepted!&nbsp;&nbsp;<br><br>Rochelle

 

[Cornerstone]

I believe you will want to use Access with User Level security. <br><br>You will want a Request table and a form for the entry and you will want a Bids table related to the Request table and a form for entering bid info.<br><br>You can program an e-mail message be sent on the after update event of the form. Look up info on SendObject. You can also program Outlook more directly from Access.<br><br>By using userlevel security you can ensure only certain people can use the forms and approve records by disabling controls based on the currentuser(). <br><br>Well there is some info to go on. You have your work cut out for you.

 

[DiscoDuane]

Take a look at <A HREF="

http://www.microsoft.com/Officedev/"

TARGET="_new">

http://www.microsoft.com/Officedev/</A>

or there abouts it may say the page has moved but look near the bottom&nbsp;&nbsp;they have a few links to articles on outlook's object model.&nbsp;&nbsp;Once you become familiar with the Object Model sending email adding contacts notes etc. is a snap

 

[elizabeth]

well that's a big question to answer in a forum. here's a bunch of ideas off the top of my head... <br><br>I would suggest you personally interview users in each stage of the process to make sure you understand all the exceptions before you begin (things that happen on the holidays, when the purchasing agent is out sick, etc.) to make sure you've got the specs. You also might find that approval is dependent upon dollar amount, which may vary by dept, title, etc. Also you may find some fields should be locked after entry but some should be open to change (such as Purchasing agent may have to correct spelling or name of item). <br><br>Use the existing paper format for a purchase request and purchase order for a starting point for your form. Your process should ideally leave no gap between where it ends and another system picks up. For instance you may want to enter Check numbers if a check number is later generated by another system, so you can cross-reference.&nbsp;&nbsp;Eliminate fields no one uses or that are always filled out the same or dependent on other fields. Build lookup tables for all standard data based on the paper files or other systems.<br><br>For example:<br>tblEmployee: EmployeeID, Name, Title, PhoneNo, EmailAddress, UserName, etc.<br>tblEmployeeManager: EmployeeID, MgrID (both foriegn keys related to tblEmployee)<br>tblVendor: VendorID, VendorName, PhoneNo, etc.<br><br>You'll also need tblPO: POID, EmployeeID, DateRequested, DateOrdered, DateExpected, DateRec'd, other descriptive fields. Try to avoid free-form text wherever possible by using categories that the Purchasing agent can pre-identify for you. Also one or more status fileds could be very useful for the employee and purchasing agent to look up the status of all their outstanding requests. This status should be updatable automatically and manually. <br>For bids: tblBid: BidID, POID, VendorID, Price, Date, Comment, FollowUpDate. You can build reports for the Purchasing Agent based on follow-up dates for records with no price entered.<br>For approvals you can use tblApproval: BidID, Date, Approved (yes/No), comment.<br><br>You can restrict the manager's approval and CFO's approval by making the approval field enabled in the form's OnOpen event depending on their username. Likewise for the purchasing agent(s). I also would suggest you validate the entire form when complete and if valid have a message box pop up asking if next person should be notified, just in case they change thier mind and would like to cancel midstream.<br><br>