Using Buttons to prompt a control box to open forms using On Click

[Colebean]

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
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

 

[Jeff Boyce]

Nicole

A couple observations (see in-line below):

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.

We aren't there. Since "how" depends on "what", consider posting a
description of your table structure.

For example, if you were working with a (over-simplified) student
registration system, you might have:

tblStudent
StudentID
FName
LName
...

tblClass
ClassID
ClassTitle
...

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate
...

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.

It appears you've already settled on a user interface with a button for each
operation. What happens if/when you need to add a new operation? Or remove
one step? (hint: you DON'T want to have to redesign all your pieces, so
look into giving the user a way to select an operation - e.g., a combobox)

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.

You may have confused the notion of a unique identifier with the combination
of data you described. Yes, you could try to jam multiple facts (doc#,
version#, SOP#, ...) into a single field, but this is poor database design.
Use a query to concatenate separate fields together to see "1.002v1.0" ...
and use something else (perhaps an Autonumber) as a unique row identifier in
the table. By the way, you didn't indicate which table you are trying to do
this in...

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

Look into using the AfterUpdate event of the combobox to drive opening the
related form. That way, you don't need to have BOTH a combobox and a set of
buttons to click.

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".

This seems like a detailed explanation of the business rules behind your
application. You might not find folks willing to develop an application to
your specs, or who have the time to reconstruct the details in order to
verify/refute your chosen approach.

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.

Try searching on-line for "custom autonumber" to get an idea of how you can
create that kind of "sequence number" routine.

What have you already tried doing to accomplish these various "business
rules"?

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

Again, not a good idea to try to add a button for each operation.
Maintaining such a design in the face of changing rules/requirements is a
bear!

"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

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[bhavarth]

thank you guys

Bhavarth van de Leemput

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
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