Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Pass parameters to Word from Access

What is the best way to pass information to Word from Access?

I have several values (string, date, numeric) used in an Access program.  I would like to use these same values as the basis for a letter in Word.  The control should remain with Access after the Word document is closed.

Can these values be provided to Word as parameters in the Access command that opens the Word document?

Or is there a better way to do this?

Thanks, Lyle
0
Faurot
Asked:
Faurot
  • 2
1 Solution
 
JonoBBCommented:
You can use bookmarks in word, but once the data hasbeen populated, you lose the bookmark for some reason.

Therefore, I use form fields and give each formfield the same name as the field from access.

Here is my full code, which should get you started. Let me know if you need any assistance.

Option Compare Database
Const DOC_PATH As String = "\\Full\Pathto\YourWord\Document\"
Const DOC_NAME As String = "Standard Offer Letter" 'Name of your word document, saved as a Word Template
Option Explicit

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If

'Set up the Word variables
    With appWord
        Set doc = .Documents(DOC_NAME)
        If Err = 0 Then
            If MsgBox("Do you want to save the current document " _
                & "before updating the data?", vbYesNo) = vbYes Then
                    .Dialogs(wdDialogFileSaveAs).Show
            End If
        doc.Close False
        End If
   
        On Error GoTo ErrorHandler
       
        Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
        Set rsModel = New ADODB.Recordset
       
        strSQLModel = "SELECT tblModelUnits.*, tblModelSite.ProjectName" & _
                    " FROM tblModelUnits " & _
                    " INNER JOIN tblModelSite ON tblModelUnits.ModelID = tblModelSite.ModelID" & _
                    " WHERE tblModelUnits.ModelID =" & Me.txtModelID & ";"
   
        rsModel.Open strSQLModel, CurrentProject.Connection, _
                adOpenStatic, adLockReadOnly
       
       
            With doc
            'Populate word
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
             'Etc
           End With
   
       
        .Visible = True
        .Activate
       
    End With

'Tidy up
    Set rsModel = Nothing
    Set rsContactDetails = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub

ErrorHandler:
MsgBox Err & Err.Description
End Sub
0
 
JonoBBCommented:
By the way, this uses ADO recordsets, so you will need to define a reference in access vba: Tools>References and then make sure that Microsoft Active X Data Objects 2.X is selected
0
 
FaurotAuthor Commented:
JonoBB,

Thanks.  This is probably the best way to do this and I may try it later.  Right now I needed to finish something up so I just did this another way, not as good as the way you suggested, but its done, on schedule, and works ok.

Thanks, Lyle
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now