Where have I gone wrong with this bit of code?

[MeWivFree]

I wanted to add a button to a sub form that would automatically produce a
letter in Word using a combination of fields that are located on both the
main and sub forms.

I was trying to use this:
http://support.microsoft.com/Default.aspx?kbid=210271 as an example but it
seems to assume that all fields are on a single form and I think I am trying
to reference the fields I need from the sub form (actually located on a tab
control) incorrectly.

Can somebody point me in the right direction? (This is my first leap into
the world of VBA so you might have to speak slowly!!)

Thanks very much - code follows!

Ali


Private Sub LetterButton_Click()
On Error GoTo Err_LetterButton_Click

Dim objWord As Word.Application

'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
..Visible = True
'Open the document.
..Documents.Open ("C:\Printstation Contract Letter.dot")

'Move to each bookmark and insert text from the form.
..ActiveDocument.Bookmarks("Title").Select
..Selection.Text = (CStr(Forms!MainForm!Contacts!Title))

..ActiveDocument.Bookmarks("Forename").Select
..Selection.Text = (CStr(Forms!MainForm!Contacts!Forename))

..ActiveDocument.Bookmarks("Surname").Select
..Selection.Text = (CStr(Forms!MainForm!Contacts!Surname))

..ActiveDocument.Bookmarks("Address1").Select
..Selection.Text = (CStr(Forms!MainForm!Address1))

..ActiveDocument.Bookmarks("Address2").Select
..Selection.Text = (CStr(Forms!MainForm!Address2))

..ActiveDocument.Bookmarks("Address3").Select
..Selection.Text = (CStr(Forms!MainForm!Address3))

..ActiveDocument.Bookmarks("City").Select
..Selection.Text = (CStr(Forms!MainForm!City))

..ActiveDocument.Bookmarks("County").Select
..Selection.Text = (CStr(Forms!MainForm!County))

..ActiveDocument.Bookmarks("PostCode").Select
..Selection.Text = (CStr(Forms!MainForm!Postcode))

..ActiveDocument.Bookmarks("Dear").Select
..Selection.Text = (CStr(Forms!MainForm!Contacts!Dear))
End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
objWord.ActiveDocument.Close
'SaveChanges:=wdDoNotSaveChanges

'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit_LetterButton_Click:
Exit Sub

Err_LetterButton_Click:
MsgBox Err.Description
Resume Exit_LetterButton_Click

End Sub

 

[Gary Miller]

Ali,

If you are going to be able to do this with a Form / Subform
combination the subform will have to be in form mode and not
in datasheet mode. The button will also have to actually be
on the specific subform record detail that holds the subform
section of the data or else Access won't know which subform
record to use.

If you do the above I believe that it should be doable by
referring to the fields on the subform record from the
button on the subform record with...

Selection.Text = (CStr(Me!SubformFieldName))

For the fields on the Main form you should reference them
as...

Selection.Text = (CStr(Me.Parent!MainFormFieldName))

If I was to do this myself, I would probably open the
combination of data that I needed with a recordset in my
code so that I am fully in control of what data I would be
sending to Word.

--
Gary Miller
Sisters, OR



message

 

[George Nicholson]

For a pretty good good reference on form & subform & subform2 references:
http://www.mvps.org/access/forms/frm0031.htm

The following assumes LetterButton is on the subform (as you state) and that
Contacts is the name of the *control* on MainForm that contains your
subform:

.Selection.Text = (CStr(Forms!MainForm!Contacts!Title))

can probably be restated as
Me!Title

.Selection.Text = (CStr(Forms!MainForm!Address1))

could probably be restated as
Me.Parent!Address1

BTW, a control being on a tab control makes no difference. Treat the control
as if it were just another control on whatever form the tab is on because
they are all on the same form as far as VB is concerned.

Sorry, but I don't know enough about it to feel comfortable commenting on
the Word aspect of your code.

HTH,

 

[George Nicholson]

...or else Access won't know which subform record to use

Pretty sure it will use the Current Record of the form, of which there can
only be one, even in continuous form view.

 

[Gary Miller]

George,

Thanks. You are probably correct that it would work on a
subform header or footer as long as you were on a subform
record directly before clicking the command button . I may
have been over-emphasizing the fact that you can't put it on
the main form and have it know which subform record you
would want. Also, you would have a problem with the subform
header/footer if you accessed the button directly from the
Main form as you never would have activated the particular
subform record that you were interested in.

 

[MeWivFree]

Thanks George - that does nicely until I hit the problem of having a blank
field on the form, at which point I get an "invalid use of null" error.

How do I solve that one?

Also, any idea how I get the bookmarks in the Word document returned to
their original, non-filled state? The document that contains the bookmarks
is actually a Word template but it keeps the data I've sent to it regardless.

Thanks,

Ali

 

[Gary Miller]

Approach 1: Preferred
Use the Nz function to turn your null into a zero length
string
Selection.Text = Nz(Me.Parent!Title)

Approach 2:
Test for contents before assigning as Text
If Len(Me.Parent!Title) > 0 Then
Selection.Text = Nz(Me.Parent!Title)
End If

For your other problem it looks like this part of your code
is commented out:

'SaveChanges:=wdDoNotSaveChanges

Delete the leading apostrophe to uncomment it if you want to
pass the command not to save changes.

--
Gary Miller
Sisters, OR



message

 

[MeWivFree]

Thanks very much Gary!

Just another little quickie...

At the moment I'm opening the template that contains the bookmarks like this:

'Open the document.
..Documents.Open ("C:\Printstation Contract Letter.dot")

but what I would really rather do is have Word open a new document based on
that template as I'm sure that otherwise it'll be all too easy for things to
go wrong if somebody accidentally saves it, instead of choosing "save as...".

Do you know how I do that?

(Sorry, I seem to be drawing a blank on things like this and haven't found
the Access help files very useful as I don't really know what I need to look
under!)

Thanks...

Ali

 

[George Nicholson]

Do you know how I do that?

I'm not a Word expert, but per the Word VBA help file, I believe you want to
use
Documents.Add "TemplateName.dot"
That should create a new, blank document based on the specified template.

Documents.Open "TempalteName.dot"
will open the template itself.

and haven't found the Access help files very useful

....well, the Word help files might have been a better place to look for
this...

HTH,