SAP2 said:
Hello, I am looking for some help with creating a work flow in MS Access
2003. What I want is for a buyer to open a form, fill it out and send it to
another queue of sorts.
For instance, the buyer would fill out the form, date it, and submit it to
the purchasing manager. Upon submission the purchasing manager would receive
an email notification and the form would only be editable by the purchasing
manager. Further, the purchasing manager could make notes, reject it back to
the buyer, or approve it and submit to the quality manager. If approved the
qm would receive an email notification and the form would be locked for edit
to that queue. The qm would then have the ability to reject or approve the
form.
I have created the initial form but I am having trouble on what the next and
in between steps should be to make this as painless as possible.
Any help would be appreciated.
I think you need some more information. Here are some hints:
1) Access 2007 has the capability of updating a database automatically
from email.
2) SharePoint allows Access to use true Workflow without sophisticated
programming.
3) Workflow can be combined with Access using the .NET Framework with WWF.
4) Access 2003 can implement a "poor man's" workflow like you describe
by using a pair of checkboxes. The first checkbox is used by a query to
fill the queue. The second checkbox is used to filter out records that
have been processed from the queue. Access would be used to control the
email notification.
5) Collaboration Software is the generic term for software that controls
documents shared and analyzed by a group. See:
http://en.wikipedia.org/wiki/Collaboration_software
6) Microsoft Project has the capability of sending out Tasks through
Outlook.
A rough sketch of what you describe is shown below (without the email
from the PM to the QM):
Buyer PM QM
-------------------------------------------
Create ==> Edit ==> Lock ==> Approved
<== <==
Assuming you are interested in 4), and keeping in mind that you might
want to use a separate table as a more normalized way to deal with
checkboxes, a rough outline might look something like:
tblProposedPurchases
PPID AutoNumber
ProposedPurchaseReferenceNumber Text
ProposedPurchaseDescription Text
SubmittedToPM Y/N
PMNotes Text
ApprovedByPM Y/N
SubmittedToQM Y/N
QMNotes Text
ApprovedByQM Y/N
LockData Y/N
Buyer Subform RecordSource:
SELECT * FROM tblProposedPurchases WHERE SubmittedToPM = -1 AND
ApprovedByPM = 0;
PM Subform RecordSource1:
SELECT * FROM tblProposedPurchases WHERE SubmittedToPM = -1 AND
ApprovedByPM = 0;
PM Subform RecordSource2:
SELECT * FROM tblProposedPurchases WHERE SubmittedToQM = -1 AND
ApprovedByQM = 0;
QM Subform RecordSource:
SELECT * FROM tblProposedPurchases WHERE SubmittedToQM = -1 AND
ApprovedByQM = 0;
In PM Subform RecordSource1, when the PM checks ApprovedByPM, that
should run code to email the QM after a confirmation prompt and
automatically mark SubmittedToQM. The record should now flow to the QM,
assuming that the subforms are requeried appropriately. For information
about using a separate table for the checkboxes, see:
http://allenbrowne.com/casu-23.html
but be aware that the queries you use will be slightly more complex for
properly normalized data.
James A. Fortune
(e-mail address removed)
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular procedure,
but I am under no obligation to modify these examples to provide added
functionality or to construct procedures to meet your specific
requirements. Any code samples posted contain no known hidden material
defects. However, anyone who uses any code sample posted does so with
the understanding that they are responsible for any testing of any
illustrative code sample for any particular use. Furthermore, anyone
using an illustrative code sample I provide or code derived from it does
so at their own risk.
