I am creating a document control system for standard operating procedures (SOP) in MS Access 2007. I have just started an online VBA class and have moderate MS Access 2007 experience.

This thread is very detailed and lengthy. Please don't let that deter you from reading it!!! If you have any ideas on ANY part of this, no matter how small or big for that matter, PLEASE leave a reply. I am at the end of my rope!!! I can email you my database if that would help with clarity. I could use VBA code suggestions, general theory, anything that you think would work better, or what to avoid all together... I attached a zipped microsoft access 2007 file. This is where I am with the database. I can't get anything to work any further, but I have it mapped out on paper

I have the tables set up and forms created for CREATE, SUPPLEMENT etc. I need to create a user interface so that when they open the database it pulls up a form that has buttons for "Start New SOP Process" and "Modify Existing SOP Process". Every step of writing an SOP is dated so that management can have outputs for target measures (to see why it takes so LONG to write an SOP from start to finish). After the user selects "Start New SOP Process" or "Modify Existing SOP Process" I am trying to have a Combobox appear with a drop down box of change types: CREATE, SUPPLEMENT, and REVISE.

Primary Key Explanation:
Each document has a 3-part number comprising a 3-part PK. 1.001v1.0.
Where 1 is (1-6) identifying what type of SOP: Document Type,
.001 is the Document Series (a sequential number for each type of equipment--the 2nd SOP written for a document type 1 would be 1.002v1.0) and
v1.0 is the version.

For example, lets say Document Type 2 indicates field equipment. The very first SOP written for a piece of field equipment would be 2.001v1.0. If a supplement is made to the SOP for that same piece of field equipment, then it becomes 2.001v1.1, if a revision is made to that same piece of equipment it becomes 2.002v1.0. If we buy a new piece of field equipment it would be assigned SOP document number 2.003v1.0.

After a document is changed (any part of the 3-part number is changed) the previous record is put into an archive table so that the technicians/general public have access only to the most recent version of each SOP. I am going to store them on Sharepoint with an Archive Folder and an Active Publications Folder and a link to the document is provided in the database. However, while a document is being modified (going through the SOP process of reviews and drafts) the users are going to need access to the previous version until the most recent version is approved (maybe I could have the document be archived upon completion of the Document Link control).

For each Combobox option I need a different "On Click" command to open the associated forms for each “Change Type”. I have the necessary forms already grouped (using subforms and tabs) for each change type: CREATE SOP (has all of the forms needed when creating an sop), SUPPLEMENT SOP (has all forms needed when supplementing an sop), REVISE SOP (all forms for revising). I am trying to use If Then statements for this...

The modify part is the simplest. I think the Combobox for the "Modify Existing SOP Process" should consist of "Change Type" (CREATE, SUPPLEMENT, REVISE), "Document Type", "Document Series", and "Document Version". I want the "Change Type" to open up the correct group of forms with "Document Type", "Document Series", and "Document Version" finding the existing record that needs to be modified (i.e. so that the user can add dates for completed activities as the SOP process progresses: draft revisions, reviews).

The "Start New Process" is even more complicated. Each change type does more unique event procedures than just opening the forms (which I can't even figure out to begin with!!). The Combobox would still be "Change Type" (CREATE, SUPPLEMENT, REVISE), "Document Type", "Document Series", and "Document Version".

However the following is what I am trying to do for each “Change Type”:

When “Create” is chosen from the Combobox it will find the next record in the document series by the user selecting the correct “Document Type”. I would like to add a formula so that it adds .001 to the previous record (giving the next sequential number), and force v1.0 to be entered and not able to be modified, since a newly created document can only be version v1.0. Then it would open the "CREATE SOP" form (all forms associated with “Create” compiled on one form by tabs and subforms--I already have this complete for each “Change Type”) and require the user to fill out all document information controls (title, description, etc) before closing.

When “Supplement” is chosen from the Combobox it will find the most recent record by the user's input of Document Type and Document Series. I would like to add a formula so that it adds .1 to the previous Document Version. Then it would open the "SUPPLEMENT SOP" form. It would be useful if the fields for the document information controls (title, description, etc) were appended to this form but it is not necessary.

When “Revise” is chosen from the Combobox it will find the most recent record by the user's input of “Document Type” and “Document Series”. I would like to add a formula so that it adds 1 to the previous Document Version and forces the decimal value to be 0 (revise and create change types have to end in a “0”). Then it would open the "REVISE SOP" form. It would be useful if the fields for the document information controls (title, description, etc) were appended to this form but it is not necessary.

Additional buttons I would like to have on the Main Menu Form--opened when the database is opened (includes "Start New SOP Process" and "Modify Existing SOP Process") would be:

"View All Publications" where a user can not edit anything, just gives them access to the current "active" SOP links. It would be a complete list of all of our SOPs. I think I could use a query

"Retire" which would put an SOP into the Archive table/folder on Sharepoint. This would be useful in addition to the automated retiring of previous version in case we discontinue use completely of a peice of equipment or administrative procedures (yes we have SOPs for EVERYTHING!!)

"Target Outputs" which would be a report showing the timeline of the SOP process so that management can see where we are spending too much time (my vote is for the design of the document control system!!)

"Maintenance History" which would show all previous versions for each piece of equipment and administrative procedure.

"List of Archived SOPs" which would show all of our inactive SOPs, for the Quality Analysis/Quality Control person’s benefit.

I have spent over a month trying to figure this out on my own time outside of work, if anyone could PLEASE help me I would GREATLY appreciate it!! If you have any ideas with ANY part of this, no matter how small it may appear to you--it could be my missing link, would be extremely helpful. I have already torn down this (theoretical) database 6 times and rebuilt from scratch so far, and I am beginning to think it is impossible.....

Nicole Romisch

Can you put an Access2003 version that I can look at it?

Not if it's hooked in to Sharepoint. Deep client integration is a 2007+ feature set.





Before we go too far down this path, is there a business reason why you're not doing this entirely within Sharepoint? You can accomplish 90% of what you've talked about here with nothing but default versioning/publishing and list views baked in to WSS. For something more involved, Access still may not be the best option. You could go with webparts or infopath forms, for example.