Saving a form with data

[VA-EngiNerd]

I am new to this site, but it looks like you all really know your stuff so here's a fun one for you.
For my company I am making an inspection report, but we are standardizing as we have so many inspectors and they all fill out reports differently.

I have a form that when the document is opened, the form pops up. The user enters information into the form and when they are done it automatically fills in the Word Document, saves as a .doc and will at some point email as an attachment (but that is a problem for later date, please don't respond to that one here).

My problem is:
How can I save the form so that the document can be closed, but when re-opened it has the form data still entered? I plan on adding a SAVE button and adding the code onto that.
The way the report is, I really need the entire form filled out before I write it to the .doc.
My only thought is to save the form data as a temp file of some type and then add an additional button to load the data back in. Is there an easier way to accomplish this?

If I need to attach my for as an example I can, but I would prefer not to.

Edit- I am using Word 2007 on Win7 (if it matters)

[Frosty]

It sounds like you are utilizing a UserForm as your primary data input, and a Word document as your primary data output.

But you need an intermediary place to hold data when the UserForm is not completely filled out.

There are ways to do this, but I'm not sure why the document isn't the best place to store this information.

You could store the data in the .Variables collection of the Document, or you could store data in the .CustomDocumentProperties collection... or you could store the data in an external location.

But why? Can you explain the following statement more fully: "The way the report is, I really need the entire form filled out before I write it to the .doc"

Because, honestly, I disagree with this statement. The word document is the best place to store the data, unless you are trying hide data which has already been entered from the typical end-user (which I'm guessing is not a requirement, since you want to grab that data and put it back into the form when the document is re-opened).

You might want to skip the whole UserForm and explore using a protected document with FormFields. Content Controls in Word 2007 are pretty buggy, but they would be a good alternative. I think the user form is probably the least desirable approach, unless you are already extremely familiar with the process.

[VA-EngiNerd]

It sounds like you are utilizing a UserForm as your primary data input, and a Word document as your primary data output.

Yes, you are correct, I guess I did not state it clear enough.


There are ways to do this, but I'm not sure why the document isn't the best place to store this information.

I would agree that the document itself is the best place to save this, but the problem I run into is re-populating the userform when the document is re-opened. I am using a template designed by my boss and have laid out bookmarks as placeholders. Then the userform data is placed into the bookmarks and once the report is completed it is saved as a .doc and emailed to the boss as well as to our client.



You might want to skip the whole UserForm and explore using a protected document with FormFields. Content Controls in Word 2007 are pretty buggy, but they would be a good alternative. I think the user form is probably the least desirable approach, unless you are already extremely familiar with the process.

I am familiar with userforms and would prefer to work with them. Perhaps the best solution is to save the entered information into the report but then I have the 2 issues: re-populting the userform so that in inspector does not think that the entered data was lost and marking the document programaticaly so that I can know if it has been saved to know when to run the code that re-populates.

I appreciate your help with this and am sorry that I was not more clear before.

[Frosty]

Easy enough, then. You'll notice that bookmarks tend to be replaced when you simply replace them with different text (this is regardless of whether the bookmark is an insertion point (looking like an "I") or defining an existing range of text (looking like "[hello my text]").

All you need to do is restore the bookmark. This is done by setting it to a range, replacing the text of the range, and then redefining the bookmark. For example... create a document, put some text in it, then select a chunk of that text. Insert a bookmark called "myBookmark".

Sub Demo
dim rngBK As Range

Set rngBK = ActiveDocument.Bookmarks("myBookmark").Range
MsgBox rngBk.Text
rngBK.Text = "New Text"
ActiveDocument.Bookmarks.Add "myBookmark", rngBK
MsgBox rngBK.Text

End Sub
This is how you replace the bookmark range while "preserving" the bookmark. Encapsulate this functionality, and then any time you open the document and load your form, repopulate the form with your info.

The problem with bookmarks, however, is that they can easily be typed over/deleted/replaced by normal user interaction. This is why it is sometimes desirable to protect the document from normal interaction by some means. Whether you choose to use the Document.Protect method, or if you simply want to, when dismissing your UserForm, to save the document and then close it... that's a design spec you'd need to decide on.

Does this help point you in the right direction?

[Frosty]

In terms of your second question (knowing whether the document has data entered into it), I don't think that matters.

If the process is...
1. Open the document (or create a new document based on the template)
2. Run a user form, prepopulating the form with any data which exists in the document

Then it doesn't really matter what data exists in the document. In fact, you could prepopulate your bookmarks with default data or informational input, such as...
Address: [Enter Address Info]
(where the brackets represent a bookmark called "Address")

Then the first time you do the document, your form will say "Enter Address Info" in the address block, which can be replaced by your inspector with "123 Main Street, Anytown, SomeState 12345" ... which would be re-populated using the exact same process.

[fumei]

The main purpose o0f document variables is precisely to retrieve data originating from macros that have terminated. In other words to have a method of persistent data. And more importantly, data that can NOT be easily altered by the user.

Yes data can be written to bookmarks, but as Frosty points out, it can be written over by users. However, unless the usaer knows there are variables, they are not going to look for them, never mind know how to change them.

So do both. Write data to the bookmarks, and write to DocVariables as well. In fact, if you want to be careful you can refresh bookmark data from the variables on Document_Open.

BTW: writing to bookmarks is a very good basic way to have data in a Word doc. If you are even remotely going to be using this route often, write a callable procedure, and use it. Rather than duplicating it over and over again.Sub UpdateBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End SubKeep this in a global add-in if you use one. Then any time, in any document, you want to re-write to a bookmark you can use it.


So while you can not "save a form with data" - as forms can not be saved - you CAN save the data from a userform using document variables. And you CAN repopulate userform controls with that data.

[VA-EngiNerd]

Thanks guys, I will give this a try, but it sounds like my problems have been resolved.
I like the idea of using both the bookmarks and doc variables as I want the info to show in the report, but I don't want the user accidentaly deleting info or a bookmark. At least with the doc variables the data is saved.

One more question, what is the easiest way to protect the document on open and then once the report is completed and my error checking has run, to make it editable again? (So they can not delete essential things, but they can add something that I may not be able to forsee.)

Not only are you guys very helpful, but you're quick to respond too. :)