Automate merging fields in a form with a word97 document

[istuart]

I have a table that contains contract information. I created a form to enter data. Each time we create a new record in the form, we open the table and merge it with a document in word97 to create a contract. We do this from either the table itself or from MSWord97 and each time ALL RECORDS are merged. I'd like to automate this by creating a command button on the form itself that would allow us to merge that SINGLE record to the MSword97 document upon command.

Any help I receive on this is greatly appreciated.

istuart

 

[Shirley]

I do know how to create a report in Access and once the button is click, only the person that the form is showing report will appear. Have you thought about creating someting like this. The code for the button would be someting like this:

'The cmd Button will preview only the person ID that matches the form
Private Sub cmdButton_Click()
On Error GoTo Err_cmdButton_Click

Dim stDocName As String

stDocName = "rptContract" 'the name of the report
DoCmd.OpenReport "rptContract", acPreview, , "[ID]=" & [ID]

Exit_cmdButton_Click:
Exit Sub

Err_cmdButton_Click:
MsgBox Err.Description
Resume Exit_cmdButton_Click

End Sub

You can also have the address fields on the contract report.

I hope this helps

 

[tunsarod]

Here's some code I have taken from a Microsoft Press book called Step By Step Microsoft Access 97 Visual Basic. I recommend this book and another from the same publisher called Microsoft Access 97 Developer's Handbook.

The idea here is to create a reference to a Word Application object (the word document) and insert the required data into prepared bookmarks. I'm thinking it should be possible to use the open Data_Entry form as the source object but if not then I would add a Save button to the form, if it doesn't already have one, (use the wizard to one up) then add code just after the Do Menu Item instruction in the button's Click Event to run the following Public Procedure - modifed to suite your particular needs.

CUT AND PASTE THIS STRAIGHT INTO A STANDARD MODULE



Public Function OpenWordDoc(SourceObj As String, FileObj As String, SendToFltr As String)
' Open a Word document and insert text - used by menu command.

Dim dbs As Database, rstSource As Recordset


' Open a recordset based on the SourceObj string
Set dbs = CurrentDb()
Set rstSource = dbs.OpenRecordset(SourceObj)

' If no record is found display a message and exit.
If rstSource.RecordCount = 0 Then
MsgBox "No valid record was available."
Exit Function
End If

' Switch to MS-Word so it won't go away when you finish
On Error Resume Next
AppActivate "Microsoft Word"

' If Word isn't running start it and activate it
If Err Then
Shell "c:\PRogram Files\Microsoft Office\Office\" _
& "WinWord / Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If
On Error GoTo 0

' Get an Application object so you can automate Word
Set appWord = GetObject(, "Word.Application&quot

' Open a document based on the FileObj value. Turn off the
' automatic spell-checker and move to the first Bookmark
With appWord
.Documents.Add FileObj
.ActiveDocument.ShowSpellingErrors = False
.Selection.Goto wdGoToBookmark, Name:="AddressLine1"
.Selection.TypeText rstSource!Addr1
.Selection.Goto wdGoToBookmark, Name:="AddressLine2"
.Selection.TypeText rstSource!Addr2
.Selection.Goto wdGoToBookmark, Name:="AddressLine3"
.Selection.TypeText rstSource!Addr3
.Selection.Goto wdGoToBookmark, Name:="AddressLine4"
.Selection.TypeText rstSource!Town
.Selection.Goto wdGoToBookmark, Name:="AddressLine5"
.Selection.TypeText rstSource!County
End With


End Function

[\Color]

You can see that by setting up the bookmark placeholders on the Word document you can insert any table field from the source object.

You could use a Select Query containing the required data elements for a single record as the source object. Set an IDfield's criteria to =Forms!Your_Data_Entry_Formname!IDfield to restrict the selected records to the newly saved record.

EASIER STILL:

Use the same Select Query as the data-source for your existing Mail-Merge procedure. This will limit the mailmerge to a single record. Doing it this way will solve your immediate problem the most quickly and save you from messing about with the above code. Use the RunApp command to open a word document automatically, either by placing a button on the data-entry form to do this or incorporate the RunApp command into a Save button's click event.

Rod

 

[istuart]

Thanks Rod,

I tried both of your suggestions. The first, I'm afraid might be a little over my head, so alas, it did not work. Please specify what I should refer to for SourceObj and FileObj and I will try it again.

Then, I tried your second suggestion and created a macro for the mailmerge function. Specifically, I used the following Actions: RunApp and TransferText and then incorporated this into the SAVE button as a click event. Well, when I SAVE the record, MSWord is launched and the correct file (mailmerge document) opens. However, there are two distinct error messages and the actual mailmerge is not fully executed. Also, could I actually limit the mailmerge in a macro to just one record or would that require vb code?

Please let me know.

istuart