Fill One form and auto-populate another form.

[E-mail report using Lotus Notes rather t]

Hi,

I have two forms. One form is for the company to keep and the other is a
government agency form which I have to e-mai/fax to governemnt agency. Both
are MS Word forms. What I want to do is populate one form (company form) and
then auto-populate the second form (government form). Since about half of
the data for the company form is the same as the government form i want to be
able to pupulate the government form when user enter the information in the
company form. Is this possible to do?

 

[Greg Maxey]

Yes it is possible. However, I would create a UserForm that is laid
out to gather all the information required for both forms (in a single
Word document).

 

[E-mail report using Lotus Notes rather t]

Greg,

I actually know how to do this using {REF Text1} in a single form. But what
I want is in a separate form so I can save it separately. So just wondering
if that is possible. Also is it possible to have data populate from a MS
Access database into a drop down menu in a word form? Basically taking data
from Access database into the drop down menu for the Ms Word form and user
select the data for the form to populate the field.

 

[Greg Maxey]

Ok. Yes it is still possible, but I personally would abandon the MS Word
Form in favor of a UserForm the gathers "all" the required information for
both forms. Creaates and saves the Business copy and creates and saves the
government copy.

If you want to stick with the MS Word Form then the easiest way may be to
use IncludedText fields in the second form.

In this link you will find some examples of code to populate UserForm
Listboxes from external sources (including Access). I suppose (have never
tried) that it could be adapted to populate a formfield dropdown.

 

[E-mail report using Lotus Notes rather t]

Hi Greg,

Can you please send me a link on how to use the "IncludedText fields" for
the second form? I checked out your attached URL
http://gregmaxey.mvps.org/word_tips.htm but I could not find that information.
Also, the second option is userform. I also went through your link but it's
not that detailed. Are there other links I can look at? Thanks a lot.

 

[Greg Maxey]

Word's Help will show you how to use IncludeTextFields. Basically the
formfields in your first form will have a bookmark name. In your second
form you use IncludeText fields pointing to the first document and bookmark.
When the second form is opened and fields updated the value of the text in
the bookmark of the first form is populated.

Let's say you have a Goverment letter template with three bookmarks where
data needs to go. The bookmark names are "Name" "Address" and "DOB."

Lets say you have another Personal letter template with two bookmarks where
data needs to go. The bookmark names are "Name" and "Address."

You could add a Userform to your Government letter template that provides
text fields for Name, Address, and DOB. When the form is processes you
could populate the three bookmarks in the government letter and then create
a new document based on your personal letter template and populate the
bookmarks in the personal leter.

Basically:

Private Sub CommandButton1_Click()
Dim oGovLtr As Word.Document
Dim oPerLtr As Word.Document
Dim oRng As Word.Range
Set oGovLtr = ActiveDocument
With oGovLtr
Set oRng = .Bookmarks("Name").Range
oRng.Text = Me.TextBox1.Text
.Bookmarks.Add "Name", oRng
Set oRng = .Bookmarks("Address").Range
oRng.Text = Me.TextBox1.Text
.Bookmarks.Add "Address", oRng
Set oRng = .Bookmarks("DOB").Range
oRng.Text = Me.TextBox1.Text
.Bookmarks.Add "DOB", oRng
End With
Set oPerLtr = Documents.Add("E:\PerLtr.dot") 'Create the new personal letter
document and populate its fields.
With oPerLtr
Set oRng = .Bookmarks("Name").Range
oRng.Text = Me.TextBox1.Text
.Bookmarks.Add "Name", oRng
Set oRng = .Bookmarks("Address").Range
oRng.Text = Me.TextBox1.Text
.Bookmarks.Add "Address", oRng
End With
oGovLtr.Activate 'Return to Governement Letter.
Unload Me

End Sub

 

[E-mail report using Lotus Notes rather t]

Hi Greg,

Thank you for the code. The code you include is for a button on the
Userform for my Government letter is that correct? Also, down the line if I
do make this process work I have a question about my future process. If it
works and I save it as a template (name the temple as XYZ) using the codes
you indicated on a Userform, can I save the form as another name different
from the XYZ name and will that temple still work or do I have to modify the
code each time I enter information in the primary form and save the form with
a differenct name. As part of the government regulation I have to save the
forms for 2 years. So I have to save the form differently with different
names each time I populate the form. Thank you for all your help.

 

[Greg Maxey]

Yes. The code goes in a command button on the UserForm. The UserForm is
part of the Government letter template. The concept is this:

You have a template lets call it E:\Government Letter.dot

You have a second template lets call it E:\PerLtr.dot

The templates have bookmarks to indicate where data is populated.

The UserForm is in the Government Letter template.

You put code something like this in a standard module of the Governement
Letter template to fire and display the UserForm when a new document is
created based on the template:

Sub AutoNew()
UserForm1.Show
End Sub

You create a new Government letter based on the Government Letter template.

The UserForm fires and you enter the data

You execute the code I sent earlier with a command button.

The new government letter is created and a new personal letter is created.

Save the two letters however you please.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"E-mail report using Lotus Notes rather t"