Send Data from Acces to Word and then sever the connection

[cluksha]

I need to create a function in Word / Access to allow the user to click a button from within Access and have the data from the selected recordset, prepopulate a word template and then have Word sever the connection so the user can then edit the word doc as desired and NOT, yes I said not, hav ethe access db info change in any way.

The data being exported from Access to Word will only be exported once and once the Word file is edited and printed, it will be saved as a static document for future reference.

Any good ideas and helpful hints?

Thanks!

Chris

Chris Luksha
Echo Web Services
Making Your Website Resound

http://www.echowebservices.com

 

[mmorancbt]

I would break this into appropriates subs and function but here is the idea...

=========================================================
Sub ButtonYourPress_onClick
Set conn = CurrentProject.Connection 'assuming your data is coming from a table within or attached to your Access project/database
strSQL = "Select * from tbl_I_Wante WHERE data=IWant"
Set rst=conn.Execute(strSQL)
If Not rst.eof Then 'read data into vars
strData = rst.fields("fieldname")
strData2 = rst.fields("otherdataIwant")

' create word app and doc

Dim wdApp As Word.Application 'Object 'Word.Application
Dim wdDoc As Word.Document 'Object 'Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err = 429 Then
Set wdApp = CreateObject("Word.Application")
End If
Set wdDoc = wdApp.Documents.Add(strTemplate) 'specify template here
wdApp.Visible = True
wdApp.Activate

Set wdDoc = Nothing
Set wdApp = Nothing
FindAndReplace(wdApp,"[texttofind]",strData)
FindAndReplace(repeat the above idea for all your data)

Else
msgbox "no data to write."
End If
End Sub


Sub FindAndReplace(inW, inS, inR)

inW.Selection.Find.ClearFormatting
inW.Selection.Find.Replacement.ClearFormatting
With inW.Selection.Find
.Wrap = wdFindContinue
.text = inS
.Replacement.text = inR
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
inW.Selection.Find.Execute Replace:=wdReplaceAll

End Sub
=========================================================

I'll be honest, I can't remember if you pass the Word application object or word document object to the findandreplace code. However, I use this exact set of functions in a few of my databases.

Also, if the text you are placing is too large, search and replace won't work. Create a modified function that does a search and upon finding the appropriate mark in the document, does a text insert.

Setting the Word Object in Access to nothing should not kill the word application. Your user will be left viewing the document as you want them to.

I hope this helps.

Matthew Moran