Buttons-ComboBox-OpenForms "On Click"

[Colebean via AccessMonster.com]

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

 

[Daryl S]

Colebean -

See comments below...
--
Daryl S

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.