Creating an form that builds as it changes hands

[bja]

Hi.

I've been give a task of creating a Word form using VBA where a form is created and initial information is sent to a reviewer's email address.

The act of sending the form adds a section for the reviewer to complete. The reviewer then selects some options and sends it to an approver's email.

The act of sending adds some more options. The approver selects from the options, OK's the document and sends it to a final email address.

Anyone been down this path before? This is a real head-banger for me at the moment so ANY help or advice would be greatly appreciated.

Cheers.

BJA :banghead:

[mdmackillop]

Hi bja,
Welcome to vbax.
I don't know about "the act of sending", but rather than add text to the form, insert it at the start, bookmark it and hide it with a hidden font. You then just need code to unhide the bookmarked text.

[fumei]

I am with Malcolm on this. Generally it is easier to have everything, and take stuff out, than put stuff in.

That is generally. It depends. Every project is different and its solution is based on the ACTUAL needs and requirements.

As we do not know what those are, it is hard to suggest concrete solutions.

Perhaps if you describe - in precise and exact detail - the process, and what happens along the way.

Probably #1 is the question md mentions. What the heck does "act of sending" mean??? Does it mean that Send Email has some code attached to it? Does it mean that the current holder of the document runs some code to add stuff? Does it mean the current holder of the document manually adds stuff? WHAT stuff exactly?

I have certainly worked on documents that evolved in content and structure as part of the process. I may be able to offer suggestions, but you need to give more details.

"form is created" - does this means a call to a template and a cloned document is created?

Shrug...I have no idea, but it makes a difference.

[bja]

Thanks Fumei/MD.

Sorry for not being clear.

I need to create a form as follows:

- The form is to be used for the review and approval of our documents.
- A person (the Originator) opens the form and, in a range of fields, enters their name and email address and the name and email address of a document Reviewer.
- They then enter the name of the document to review and in another field, enter the path to that document (preferrable using a 'Browse' capability).
- The Originator then performs some type of click event which:
+ protects the section just completed,
+ adds (or displays) a range of new fields with checkboxes and drop-down options,
+ saves the form then sends it as an email attachment to the email address shown in the 'Reviewer's' email field.

The Reviewer then gets the form as an email attachment.

- The reviewer then selects from the range of check box/drop-down options/message field etc and adds an Approver's name and email address into two fields.
- The Reviewer then performs some type of click event which:
+ protects the section just completed,
+ adds (or displays) a range of new fields with checkboxes and drop-down options,
+ saves the form then sends it as an email attachment to the email address shown in the 'Approver's' email field with a CC to the Originator's email address.

The Approver then gets the form as an email attachment.

- The Approver clicks on the check box options then performs some type of click event which:
+ protects the section just completed,
+ saves the form then sends it as a mail attachment to a default email address with a CC to the Originator and the Reviewer.

Again, any help would be appreciated.

MTIA

Cheers,
BJA

[fumei]

Sorry, but
+ adds (or displays) a range of new fields with checkboxes and drop-down options,is not that informative, now is it?

Read that yourself. Do YOU know what those "fields" are from that? Do YOU know what "options" they have?

I sure don't.

OK. I don't know what experience you have, but this is a reasonably serious project. Not impossible by any means.

It also seems that no one is actually doing anything beside putting the name and email address of the next person.

From reading it, it seems the document looks like:

Originator: Bob Newhart
Email: bobnewhart@whatever.com
Document: U:\WhateverFolder\blahblah.doc

Reviewer: Red Skelton
Email: rskelt@yahoo.com

Approver: U Thant
Email: UN_head@useless.org

Hmmmm.

Why are these "sections" being protected, as there does not seem to be much in them?

How is the form initiated? By template? You don't say.

OK. My suggestion. Break this up into chunks. Work on making each chunk work.

Chunk 1: use a userform to get the information. Have it displayed with the Document_New event of the template. Assuming you are using a template. How is that going to be initiated? By keyboard shortcut? By menu? By macro icon on a toolbar?

Have textboxes on the userform for the originator name and email address. Or, pick up the originator name from the system logon name if you want. Are there only a certain number of reviewers? You don't say, but if there are, then have those as a list in a dropdown on the userfor,. The originator selects one, and that selection will fill that that person's email address from a list.

They select the file, and yes, it could be done a browse function. Bit of work, but possible.

OK, so now what? They press OK, and the information (originator name and email, document path, reviewer name and email) goes into the document. WHERE? In bookmarks? Do you need to possibly edit it after? Into formfields?

So get this part done first. Do it step-by-step. WRITE out exactly the steps. I mean it. Write out what you are going to do, being very precise. As much as possible.


+ adds (or displays) a range of new fields with checkboxes and drop-down options,
is not good enough - even for you. Which is it? Add...or display? What fields? Are they really fields? What are the checkboxes? What are they for? What do you want to do with them? Do you need to get the state out of them by code, or is it just displayed in the document. In othe rwords, do you need to know (in code) if checkA is checked or not? or is it just something visible in the document?

I have no way of knowing with what you posted.

I am off for a few weeks so I am not going to be much help. Would like to as I enjoy these kind of things. I have made a few similar projects.

You need to be much more precise in your requirements. You need to work on do-able chunks.

Good luck.

[bja]

Hi fumei,

You are correct. In coding I need to be very specific.

Ist, I'm not a total newbie but don't often get the opportunity to script. As such, I tend to ass-u-me a lot and it sometimes takes me a little while to get my brain into 'programmer' mode. I use to know Assembler, Cobol, Pascal and am comforatble with raw HTML and UNIX so while not brilliant, I have some knowledge and eventually sort out most VBA requirements BUT this task has a deadline that I'm having trouble with.

2nd, I've been dicking around with this all day and the form is now completed but not the VBA back-end so here is where I'm up to at the moment:

I have set up a form using protected form fields and hide/unhide.

The form is to allow a document to be selected, reviewed and then approved and is basically broken onto three parts:

One:
1. The form is opened as a template document showing only the first section. This covers the 'Originator' fields. The other sections are hidden.
2. The Originator enters their details (name and email address) into the first two form text fields.
3. The originator then enters the details of the Reviewer (only 1 Reviewer is needed).
4. The Originator then enters the name of the document and the path to that document (I haven't worked out how to browse for this yet).
5. The Originator then clicks on a MacroButton and is finished with the document.

The onClick event (I think it is called) then does several things:

1. Attaches the document (as stipulated in the previous text fields) to the form.
2. Saves the form.
3. Sends the form as an email attachment to the Reviewer's email address.

Two:
1. The Reviewer opens the email with the attached form and document.
2. The Reviewer opens the form.
3. There is a 'Document_Open' event that unhides the second section.
4. I haven't worked it out yet but I'm also hoping I can 'lock' the information in the Originator section at this point in the process.
5. The Reviewer section contains a text field for comments and a number of check boxes that the Reviewer can select to highlight areas of the document that need fixing etc. This will include check box options recommending either Approval, non-approval, or conditional approval.
6. The Reviewer vets the attached document then enters any comments in the text field and clicks on the appropriate check box options.
7. The Reviewer then decides who will approve the document and enters their details (only one approver is needed) into the next two text fields.
8. The Reviewer then clicks on a MacroButton and is finished with the document.

The onClick event then does several things:

1. Attaches the document (as stipulated in the Originator section) to the form.
2. Saves the form.
3. Sends the form as an email attachment to the Approver's email address.
4. Sends the form as an email attachment to the Originator's email address.

Three:
1. The Approver opens the email with the attached form and document.
2. The Approver opens the form.
3. There is a 'Document_Open' event that unhides the third section.
4. The Approver, after also vetting the attached document, adds any comments in a text field then shows whether the form is approved, rejected etc based on the check-boxes selected.
5. The Approver then clicks on a MacroButton and is finished with the document.

The onClick event then does several things:

1. Attaches the document (as stipulated in the Originator section).
2. Saves the form.
3. Sends the form as an email attachment to the Originator's email address.
4. Sends the form as an email attachment to the Reviewer's email address.
5. Sends the form as an email attachment to our DMS (Document Management System) - this is effectively just another email address.

Sorry for the earlier lack of clarity. Hopefully my need is clear enough now.

TIA,

BJA

[fumei]

I have got about 48 hours that I can possibly help, then I will be away with no 'Net access.

Post a file and I will look at it.

Have a specific question.
Attaches the document (as stipulated in the previous text fields) to the form.What do you mean by that? You can not attach the document (to be reviewed) to the form document.

You mention Document_Open. Yes, there is such an event. But you seem to be having it do different things at different times. It could be fussed with to get close to doing something like that, using document variables perhaps. It would be tricky.

[bja]

I'm sending this so that my 'post' count goes to 4. I need 5 to email a member.

[bja]

I'm sending this so that my 'post' count goes to 5. I need the 5 to allow me to email a member.

[fumei]

I would suggest not sending an email to me (although you can if you like). You can post the file here, in this thread. That way others can take a shot at it.

There are other very competent people here who are just as capable of possibly helping.

[mdmackillop]

I have got about 48 hours that I can possibly help, then I will be away with no 'Net access.
Hi Gerry,
Have a good break.:hi:
Regards
Malcolm

[fumei]

Off to hike in the redwoods of California.