If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Buttons-ComboBox-OpenForms "On Click"
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 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 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. 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 form opened upon opening the database (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/admin procedure. "List of Archived SOPs" which would show all of our inactive SOPs, for the Quality Analysis/Quality Control lady's benefit. I have spent over a month trying to figure this out, 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, it would be helpful. I have torn down the database 6 times and rebuilt from scratch so far and I am starting to think it is impossible..... Nicole Romisch -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#2
|
|||
|
|||
Buttons-ComboBox-OpenForms "On Click"
Colebean -
See comments below... -- Daryl S "Colebean via AccessMonster.com" wrote: 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 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. You can create your main form and then set it up to open automatically. Look up STARTUP in help - it is very easy to do. 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. I would use an autonumber field be the primary key for each table. Then within the document, I would include three fields - the DocumentTypeNumber (integer), the DocumentSeriesNumber (integer), and the VersionNumber (single). As for what you show on the form, you can concatentate the DocumentTypeNumber, and period, a formatted SeriesNumber (to show leading zeros), the letter "v", and the version number. Having separate fields will let you increment these easily, and yet the user will only see the one string. 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). It makes sense to keep the old version available until the new one is completed. Between the time a document is changed until it is completed, then you need to think about how to handle which of the two to bring up. For example, if a user want to see the document, then show them the old one, but don't allow changes. If they want to work on the new one, then bring up the one being changed so they can update as needed. 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... You will have a single OnClick event for each combobox. In the OnClick event, you can test for the option chosen, and then open the form based on the contents of the combobox. SELECT CASE statements work really well 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). Same as above - use a SELECT CASE statement to select the forms based on the the contents of the combo box. 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 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. If you set up the primary key and other fields as described above, then this will be easy. Just add one to the DocumentSerialNumber, and set the DocumentVersion to be 1.0. 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. This is now simple - add 0.1 to the DocumentVersion 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. 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. This is now simple - use the INT function on the DocumentVersion to remove decimals, and then add one to it. Additional buttons I would like to have on the form opened upon opening the database (includes "Start New SOP Process" and "Modify Existing SOP Process") would be: You can add all these buttons to the form. It sounds like you have a handle on what you want to do with them. "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/admin procedure. "List of Archived SOPs" which would show all of our inactive SOPs, for the Quality Analysis/Quality Control lady's benefit. I have spent over a month trying to figure this out, 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, it would be helpful. I have torn down the database 6 times and rebuilt from scratch so far and I am starting to think it is impossible..... Nicole Romisch -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 . |
Thread Tools | |
Display Modes | |
|
|