auto add info from access into word (ms works related)

I am currently in the process of moving our database over from MS Works 4.5 to Access 2003. The old system, designed some 11 years ago, is completely incompatible with office as you well know. My problem is the old system allowed the user to select a client row off of the spreadsheet in works, and it auto input their information into a document in works. For example, the spread sheet contained such fields as FirstName, LastName, Suffix, Address, CityStateZip. Then the various documents would look like the following:

To: <<FirstName>> <<LastName>> <<Suffix>>

Dear <<FirstName>>, we are pleased to inform you that you have finished <<Coursename>> on <<coursedate>>.

So on and so forth, you get the idea. The problem is when I've tried to do it using mail merge, it just wants to put in an entire table, or have to select each table each time. Is there any way to let it auto input certain fields from certain tables based on selected unique user id's i have created? I'd like to be able to select the user ID within the word docs I have that mimic the old works docs, but only put in that user's data. Also, it will be broken up throughout the document as one might figure. I can e-mail a blank copy of the database and some dummy letters to anyone who has a solution. Thanks much.


  • what you want to use is automation, to programmatically control word.
    this example is vb6 but should convert easily to access with minor tweaks. you'll need a word document with mail merge fields (on the word menu-- view, toolbars, add mail-merge toolbar then insert word field [word 2002])

    Option Explicit

    'microsoft word 10.0 object library
    Dim WordApp As Word.Application
    Dim Doc As Object

    'merge db records to word doc
    Private Sub WordMerge()
    Dim sCON As String

    sCON = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & appPath & cDB & ";" & _
    "Persist Security Info=False;"

    With WordApp.ActiveDocument.MailMerge
    .MainDocumentType = wdNotAMergeDocument
    .MainDocumentType = wdFormLetters

    .OpenDataSource appPath & cDB, False, False, True, False, False, , , , , , sCON, "SELECT * FROM `tblMerge`", "", wdMergeSubTypeAccess

    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    End With
    End Sub

    Private Sub cmdMerge_Click()
    ButtonEnable 3
    End Sub

    Private Sub cmdOpen_Click()
    'show word
    WordApp.Application.Visible = True
    'open word template file
    WordApp.Documents.Open appPath & cWordFile
    ButtonEnable 2
    End Sub

    Private Sub cmdSave_Click()
    Dim FileSaveAs As String

    On Error Resume Next

    'SaveAs filename
    FileSaveAs = Left(cWordFile, InStr(cWordFile, ".") - 1)
    FileSaveAs = FileSaveAs & "_" & Format(Date, "mmddyy") & ".doc"

    'save as revised document
    WordApp.Documents.Item("form letters1").SaveAs appPath & FileSaveAs
    'WordApp.Documents.Item(1).SaveAs appPath & FileSaveAs

    'close document(s)
    WordApp.Documents.Item(2).Close False
    WordApp.Documents.Item(1).Close False
    'hide word
    WordApp.Visible = False
    ButtonEnable 1
    End Sub

    Private Sub ButtonEnable(ByVal x As Integer)
    Select Case x
    Case 1
    Me.cmdOpen.Enabled = True
    Me.cmdMerge.Enabled = False
    Me.cmdSave.Enabled = False
    Case 2
    Me.cmdOpen.Enabled = False
    Me.cmdMerge.Enabled = True
    Me.cmdSave.Enabled = False
    Case 3
    Me.cmdOpen.Enabled = False
    Me.cmdMerge.Enabled = False
    Me.cmdSave.Enabled = True
    End Select
    End Sub

    Private Sub Form_Load()
    'init word object
    Set WordApp = New Word.Application
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    'quit word
    WordApp.Quit False
    Set WordApp = Nothing
    End Sub

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion