User Interface, Button to prompt combo box and open forms 2

[Colebean]

I am designing a document control system. This is my first time on a forum but I have run out of options! I am closing thread707-1587863 as requested by MajP and Duane. It was very lengthy and complicated, so I am consolidating it as follows. I have attached the database link, and the tables/relationships/forms info is listed after my problem description. You can refer to thread707-1587863 for my primary key explanation (how my document number is required to function).

I guess where I am having the most trouble is at the user interface part of the database. How do I create a form control that when selected it has a combo box appear with options that have different forms opened depending on the selection? Also, how do you have other columns on the combo box find previous and current records and use a formula to input values into the opened form? Finally, how do you have the previous record moved into an archive table, when a document is changed and the document version changes, so that only the most recent version is accessible?

Further Explanation/Details:

I need to have a form appear upon opening the database that has two functions 1. Start New Process and 2. Modify Existing Process. When the user selects one of these options I need a combo box to pop up with the options for Change Type (Create, Supplement, Pen and Ink, Revise), Document Type, Document Series, and Document Version.
When the user chooses Start New Process, I need different things to happen when each Change Type is selected:
"Create" will find the next record, by the user inputting the Document Type. Then it adds .001 to the previous record's Document Series for its' Document Series value and force v1.0 as its' Document Version, finally it would open the grouped form CREATE SOP.
"Supplement" would find the most recent record, by the user inputting Document Type and Document Version. Then it adds .1 to the previous Document Version, finally it would open up the group form SUPPLEMENT SOP.
"Revise" will find the most recent record, by the user inputting Document Type and Document Series. Then it adds 1 to the previous Document Version and forces the decimal value to be 0, finally it would open up the group form REVISE SOP.

When the user chooses modify existing process, I need the Change Type to pull up the group form associated with each Change Type and by the user inputting Document Type, Document Series, and Document Version to go to the existing record that needs to be modified.

TABLES:
Change Type
Change Type dbText PrimaryKey Indexed

Document Information
Document Type dbByte PrimaryKey Indexed Required
Document Series dbDecimal PrimaryKey Indexed
Document Version dbText PrimaryKey Indexed
Document Title dbText
Document Description dbText
Change Type dbText Indexed
Reason Change dbText
Purchase Order Number dbText
Purchase Order Approval Date dbDate
Estimated Delivery Date dbDate
Actual Delivery Date dbDate
Date Posted to Sharepoint dbDate
Date Submitted to EPA dbDate
Date of EPA Approval dbDate
Link to Document dbMemo
Date Document Provided for Signature dbDate
SOP Lead Name dbText
Date SOP Lead Signed dbDate
QAQC Evaluator dbText
Date QAQC Signed dbDate
Manager Name dbText
Date Manager Signed dbDate
Review Start Date dbDate
Review Complete Date dbDate
Reviewer Name dbText
Date Retired dbDate
Reason Retired dbText
Link to Archived Document dbMemo

Document Type
Document Type Number dbLong PrimaryKey Indexed
Type of SOP dbText

Draft Reviews
Review Number dbByte PrimaryKey Indexed
SOP Lead Name dbText
SOP Lead Date Assigned dbDate
Date SOP Lead Draft Complete dbDate
Field Evaluator dbText
Date Draft Provided for Field Eval dbDate
Date Field Eval Complete dbDate
QAQC Evaluator dbText
Date Draft Provided to QAQC dbDate
Date QAQC Evaluation Complete dbDate
Document Type dbByte PrimaryKey ForiegnKey Indexed Required
Document Series dbDecimal PrimaryKey ForiegnKey Indexed
Document Version dbText PrimaryKey ForiegnKey Indexed

Employees
EmployeeNo dbLong PrimaryKey Indexed
LastName dbText
FirstName dbText

Relationships:
Name: Change TypeDocument Information
Table: Change Type
Foreign Table: Document Information
PK: Change Type FK:Change Type

Name: Document InformationDraft Reviews
Table: Document Information
Foreign Table: Draft Reviews
PK: Document Type FKocument Type
PK: Document Series FKocument Series
PK: Document Version FKocument Version

Name: Document TypeDocument Information
Table: Document Type
Foreign Table: Document Information
PK: Document Type Number FKocument Type

Name: Document TypeDraft Reviews
Table: Document Type
Foreign Table: Draft Reviews
PK: Document Type Number FKocument Type

Name: EmployeesDocument Information
Table: Employees
Foreign Table: Document Information
PK: EmployeeNo FK:SOP Lead Name

Name: EmployeesDraft Reviews
Table: Employees
Foreign Table: Draft Reviews
PK: EmployeeNo FK:SOP Lead Name

Forms:

INDIVIDUAL FORMS:
Document Information: Document Type, Document Series, Document Version, Document Title, Document Description, Change Type, Reason Change

Equipment: Purchase Order Number, Purchase Order Approval Date, Estimated Delivery Date, Actual Delivery Date

SOP Signatures: Date Document Provided for Signature, SOP Lead Name, Date SOP Lead Signed, QAQC Evaluator, Date QAQC Signed, Manager Name, Date Manager Signed

Draft Review: Review Number, SOP Lead Name, SOP Lead Date Assigned, Date SOP Lead Draft Complete, Field Evaluator, Date Draft Provided for Field Eval, Date Field Eval Complete, QAQC Evaluator, Date Draft Provided to QAQC, Date QAQC Evaluation Complete, Document Type, Document Series, Document Version {This is a Continuous form since there will be more than one review for each document}

GROUPED FORMS:
CREATE SOP: Document Information (Master Table); Tabs for: New Equipment, Draft Review, SOP Signatures, Finalized

SUPPLEMENT SOP: Document Information, Tabs for: Draft Review, SOP Signatures, Finalized

PEN AND INK SOP: Document Information, Tab for: Finalized

REVISE SOP: Document Information, Tabs for: Draft Review, SOP Signatures, Finalized

 

[MajP]

If that is the case, I think the easiest for you would be to move the Equipment fields back into the doc table. Then decide about the Milestone fields. The easiest for you to design an interface would be to move those fields back into the doc table (just know it will be hard in the future to add additional milestone events. Because you will have to change tables and forms instead of just adding a new record to a table.)

Then you just need a signature table because a signature entity has a lot of related information.
who sign
when sign
type of signature
role of person signing

I would still have a link table (links to documents and related information)
link name
link description
linkaddress
So you can have your required links and then add additional

Keep the review table because that is like signatures where
you have many reviews associated with a document

I think that is it.

Now,
For archiving a document, I would not delete it or remove from the db. Just tag it as archived. Then in the rowsource for the search form include " where blnArchived = False". It exists in the db but you just do not show it anywhere. I forgot to add code for archiving, but lets get the rest working first.

I still do not understand the
Create
Pen and Ink
Supplement
Revise
for a current record (continue SOP process). Does the user even need to pick one of these if the user picks "Modify Current SOP" should it just go to the pick SOP form?

 

[MajP]

http://www.4shared.com/file/215079484/6314d2f8/SOP_Demo_V7_4Feb.html

This version includes the archiving. Play with this a little to verifying it incrments, navigates, and archives correctly.

 

[Colebean]

I think I am almost done with the tables. I am moving most of the info back to the doc info table to prevent having to write an extensive user's guide after this is finished. I did keep the workflow table for Draft Evaluations and the Annual Review. (A draft eval is making sure the SOP does what it says it is doing by a field test, qaqc proofreading, and rewriting the draft by the SOP Lead, where the annual review is reading the SOP once a year to make sure nothing has changed and it is still accurate). For the continue an SOP Process, they just need to pick the document out of your search form. Picking the Create, Pen and Ink, supplement, and Revise is for the initial creation of the process to assign its' doc numbers and hopefully open the form and hide/show the correct tabs and fields.

 

[Colebean]

Here is the table restructure. i put the links under the doc info table for sake of the form. Do you think this will be okay? I'm getting ready to run the archive and interface

http://www.4shared.com/file/215114396/27386ac8/SOP_Demo_V8_4Feb.html

 

[Colebean]

Just realized that I need to connect the signature table to the docinfo, reworking.....sorry

 

[MajP]

Wait one here are a few things.

1) Earlier, I had to make a decision about the change category. I decided to save the autonumber pk. This becomes important because it is used in the code. So I modified the change category table back, and the doc info table back. See this version.

2) From the way it looks you do not save roles as part of a document only as part of a signature. So it depends on how you want to do this.

From the way you have it now then you can get rid of the the table
docEmployeeRole
and just do this in your signature tables.
tblSignatures
docID_fk (foreign key to doc table)
empID_fk (fk to employee table that is the who)
roleID_fk (fk to the role table)
date provided
date complete
then the other fields unique to a signature

That relates information from 3 tables.

If you want to have a list of people and their relation to the document then keep the table
doc_employee_role
And then you could pull the empID, and role ID out of the signature table.

I think I might do it in the signature block, because what happens when John is the SOP manager but Sue signs for him?

Now I called this table Signatures, But it may be the same as "Reviews". Looking at your original post I am not sure if reviews are different than the signature process. If so I would think you need a review table. You have something called a review number, but maybe that is just part of the signature process? Or are there lots of reviews that need to get tracked and they end with a signature.

Also the EPA dates. Could they just go in the signature table. Or this possible review table?

Are Evals and roles the same thing?

http://www.4shared.com/file/215167845/a75fd920/SOP_Demo_V9_4Feb.html

Use this version as starting point.

 

[Colebean]

Perfect. Thank you. You've all been very helpful.