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 FK
ocument 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
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 FK
ocument TypePK: Document Series FK
ocument SeriesPK: Document Version FK
ocument VersionName: Document TypeDocument Information
Table: Document Type
Foreign Table: Document Information
PK: Document Type Number FK
ocument TypeName: Document TypeDraft Reviews
Table: Document Type
Foreign Table: Draft Reviews
PK: Document Type Number FK
ocument TypeName: 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
