Problem with Mail Merge

[menstroy]

Hello, I am very new to VBA so my code may be very ugly.

I have is a form in Access that shows a record in a table and I have a button that loads word, and merges that document. I have is I have is the word document all configured, and I set the recordsource, and the query withen VBA. The first time I run the procedure everything works as expected. Howerver if i close the two word letters (the origional word letter, and the merged letter) and then run the procedure again it only loads the origional letter and does not run the merge part. From what I can tell for some reason the data is not being referess correctly... If I exit completely out of the project and restart access it works fine on the first letter I merge, but there after I get the same problem.

Here is my code I have... Keep in mind it works fine once, but the next time I call it the letter does not get merged, and also when I goto Tools-Mail Merge the Datasource Information is not in their (Even though it is the first time)

--------------------------------------------

Public Function loadword(db As String, CurrentID As String)

'****Opens the files database to retrieve the stored location of the word document
Dim tjDb As DAO.Database
Dim tjRst As DAO.Recordset

Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim sql4 As String
Dim sqlstring As String

Set tjDb = CurrentDb

Set tjRst = tjDb.OpenRecordset("SELECT files.[key], files.[filename] from files where (files.

= '" & db & "')", dbOpenDynaset)

tjRst.MoveFirst
temppath = tjRst![filename]


'*** Loads word

On Error Resume Next
Set wrdApp = GetObject(, "word.application&quot
If Err.Number <> 0 Then
Set wrdApp = CreateObject(&quot;word.application&quot
End If

wrdApp.Documents.Open temppath

wrdApp.Visible = True

sql1 = &quot;SELECT * FROM [&quot; & db
sql2 = &quot;] where [&quot; & db & &quot;].id&quot;
sql3 = &quot; = &quot;
sql4 = CurrentID & &quot;&quot;
sqlstring = sql1 & sql2 & sql3 & sql4

ActiveDocument.MailMerge.OpenDataSource Name:= _
&quot;C:\Personal Lines Letter Databases\Database.mdb&quot;, ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:=&quot;&quot;, PasswordTemplate:=&quot;&quot;, WritePasswordDocument:=&quot;&quot;, _
WritePasswordTemplate:=&quot;&quot;, Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
&quot;DSN=MS Access Database;DBQ=C:\Personal Lines Letter Databases\Database.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&quot; _
, SQLStatement:=sqlstring, SQLStatement1:=&quot;&quot;
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = &quot;&quot;
.MailSubject = &quot;&quot;
.SuppressBlankLines = True
.Execute Pause:=True
End With

Set wrdApp = Nothing
Set tjDb = Nothing
Set tjRst = Nothing

End Function

 

[PauloRico]

We do what you are trying to acheive at work. I have not got the code but the theory is a little different. It may be a little easier.

In Word

1. Export your query as a text file.
2. Create the mail merge document and point it to this file.
3. Create a Macro that runs the mail merge.
4. Save the document.

In Access

1. Create a function that outputs your query as a text file of the same name as your merge source in the above document.
2. Shell out to Word using the execute macro switch, naming the macro you created in Stage 3.

Hope this helps.