Solved

Pass parameters to Word from Access

Posted on 2004-10-15
3
574 Views
Last Modified: 2012-06-27
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
Comment
Question by:Faurot
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
JonoBB earned 125 total points
ID: 12319765
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12319788
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
 

Author Comment

by:Faurot
ID: 12369339
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question