Mail Merge Current Record Only

[Vonbare]

Hi,
Is there a way to merge only the current record to Word? I was going
to have access create a temp table to merge, but then realised that
would stuff up my autoref numbers (which I need).


I will only be merging one record at a time, and would love to hear any
advice.


I don't know VB so other instructions (or VB for dummy instructions)
would be most useful.


thanks!

 

[yeatmanj]

I have a database that opens a word template and inserts data into the template in named bookmark locations. I don't know if this will help you, but I have attached an example of the code I use. It is older code that I have not updated and uses DAO, but it can be converted to ADO or you could use SQL for the query and recordsets.

Public Function PGCertification()
    
    Dim dbs As Database, rstMerge As Recordset
    Dim rstFiltered As Recordset
    Dim intPages As Integer
    Dim AppWord As Word.Application
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    
    ' Create dynaset-type Recordset object.
    Set rstMerge = dbs.OpenRecordset("qryMergeDocs", dbOpenDynaset)
    
    ' Set filter condition.
    rstMerge.Filter = "FacID = '" & strFacID & "'"

    ' Create filtered dynaset-type Recordset object.
    Set rstFiltered = rstMerge.OpenRecordset
    
    On Error Resume Next
    
    'Get an Application object so you can automate Word.
    Set AppWord = CreateObject("Word.Application")

    With AppWord
        .Visible = True
        .Documents.Add "T:\Team6Merge\" & strMergeDoc & ".dot"
        .ActiveDocument.ShowSpellingErrors = False
        .Selection.Goto wdGoToBookmark, Name:="SiteName"
        .Selection.TypeText rstFiltered![Site Name]
        .Selection.Goto wdGoToBookmark, Name:="SiteAddress"
        .Selection.TypeText rstFiltered![Site Address]
        .Selection.Goto wdGoToBookmark, Name:="SiteCity"
        .Selection.TypeText rstFiltered![Site City]
        .Selection.Goto wdGoToBookmark, Name:="SiteCounty"
        .Selection.TypeText rstFiltered!County
        .Selection.Goto wdGoToBookmark, Name:="FacID"
        .Selection.TypeText rstFiltered!FacID
        .Selection.Goto wdGoToBookmark, Name:="StaffMember"
        .Selection.TypeText rstFiltered!Signature
    End With
    
    'Get the number of pages in the memo, ask the user whether to print it, and then tell Word to print it.
    intPages = AppWord.Selection.Information(wdNumberOfPagesInDocument)
    MsgBox "The document is complete, and has " & CStr(intPages) & " pages." & Chr(13) & "Please review and edit before printing.", vbOKOnly

rstMerge.Close
rstFiltered.Close
dbs.Close

End Function

 

[mtairhead]

First off, I recommend some development in VBA - It's pretty powerful stuff. I ran away from it forever, and it only got me lost. BUT... I know it's difficult to implement others' VBA into your own database when you don't even know what it means.. So:

If you're looking for a less-techie approach, why not build a query based off of the same table that the form in which you are viewing the record is based off of? Then, set the criteria in the query so that only the record with the autoref number that belongs to the record you are displaying in your form is pulled.

That way, all you have to do is go to the record in your form, shrink Access, open Word, and mail merge using that query as a data source.

The criteria for your query would look something like...
forms![yourformname]![fieldnamewithautorefnumber]

Andrew

 

[Vonbare]

thanks

Thanks Andrew - I will try that! fingers crossed. I would ideally like to have the less steps the better, but I think having the user type in the Autonumber might be the best way forward.

 

[yeatmanj]

Future Tip. If you are going to continue to work with access you will want to know some VBA (Visual Basic for Applications). VBA is not VB. If you are purchasing books, you want books that specify VBA or Access. VBA is limited and uses a few different commands than VB. I recommend VBA for dummies to start. Also, all the help files in the Visual Basic Editor in Access, Excel, Word, etc... Are the best thing that Microsoft has ever done and come in very handy even for a beginner.

 

[mtairhead]

The user should not have to input the autonumber. The query will look at the form, see the autonumber, and return only that record.

Andrew

 

[bkdraper]

creating, inserting bookmarks into a word doc then filling them with record data

I've been reading this post to see how to send info from a record into a word doc.
I have a kind of unique situation that is a little different than the suggestion made here for filling bookmarks on a word doc.
I currently have a form letter that is an access report. in addition to the letter wording, the detail section of the form letter lists a list of technicians that have not yet met a certain criteria based on a query that pulls records about these techs based on certain criteria. so it's like, the head of my form letter says something like, 'the following techs have not yet signed and returned there security screening:' and then the detail section lists all the records from the query which happens to be the names of the techs that havn't turned in a security screening and then the form letter footer finishes with more letter wording and a closing.
I would like to take this from an access report to a word doc with the fields merged into it so management can edit the letter without having to know how to crack open an access report in design view to change it.
question is: is there a way to programmatically insert new, name, and then fill bookmarks into a word doc to simulate the way my access report uses the detail section to cycle thru records in it's query. cuz I obviously couldnt just put a pre-defined number of bookmarks into my word doc. cuz the number of techs that will meet the criteria for the letter is an unkown at any given time. I may need just one bookmark, I may need 20. cant tell ahead of time.

thanks for any help
bkdraper.

 

[yeatmanj]

In theory you can use one bookmark to insert mulitple records, but I'm not sure how you'd go about doing that. Maybe someone here will have a better suggestion. One way might be to "string" fields together and insert at one bookmark location, but that may not look to pretty.

Here is another suggestion. I also use Excel, but in the past it was not easy to create a way to transfer information using Access to control Excel. What I had to do was to create my template in Excel and use a Query in Excel to pull the information. In that way you would also be able to put in a button that refreshed the query so the user could do it periodically without your help. Let me know if I should try to provide more details on this method.

 

[bkdraper]

Thank you very much for your reply.
I think I'm going to experiment with your first idea of using just one bookmark to hold all the techs returned by the query.
I'm thinking I'll add code to my form-letter button on my app that will open a recordset based on my query then cycle thru and add all the techs to a single string variable, appending a 'vbcrlf' to the end of the string after each tech is added to it, then transfer that string variable to the word doc. I'm hoping that the vbcrlf 's will cause all the techs to appear 1 to a line in the bookmark.

Thanks
bkdraper.

 

[yeatmanj]

I have had more than one record per bookmark by accident before. The results vary some. Some times it just strings them end to end, and sometimes it listed them with cr between each record. Good luck with it.

 

[bkdraper]

thanks again for your reply.
ran into a tiny speed bump.
what reference do I need t turn on in order to get the word.application type as an option when I dim.

thanks
bkdraper

 

[yeatmanj]

OLE Automation, Microsoft Word Object Library, and possibly DAO. My code was written back in Access 97, but it still works because I made sure to keep the libraries when I converted to newer versions.

 

[bkdraper]

had everything but the word object library
thanks again.

 

[bkdraper]

sweet!
It worked like a charm
thanks for all your help today.
-bkdraper

 

[jamjarr]

Access Bookmark Problem

Hello everyone.

I have searched for this problem and there is a lot of discussion about parsing the current record from a form to a Word document. The record source of the form is from a query.

I have tried lots of the code submitted in other threads but to no avail.

I have Access and Word 2003 with XP.

I can open word and the template file. I have created the Bookmark as described in word help. See attached.

Either I get Error Bookmark not defined or the bookmark is visible but not the parsed record. I have downloaded the accesstoword.mdb in another thread. That works fine but when I modified to suit and pasted into my database I get all sorts of problems with the code. I have combined a number of possible solutions I get some results with the file opening but no record.

The code that I am using is

Private Sub word_Click()
' Check for empty fields and unsaved record.

' Create a Word document from template.
Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True


'Dim WordApp As word.Application
Dim strTemplateLocation As String
' Specify location of template
strTemplateLocation = "C:\Downloads\mailmerge.dot"


On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo ErrHandler


WordApp.Visible = True
WordApp.WindowState = wdWindowStateMaximize
WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False

' Replace each bookmark with field contents.
With WordApp.Selection

objWord.ActiveDocument.Bookmarks("LeaseName").Select
'Goto what:=wdGoToBookmark, Name:="LeaseName"
' objWord.Selection.Text = Me![LeaseName]
objWord.Selection.Text = Forms!frmLeaseAdjustment!LeaseName


End With

DoEvents
WordApp.Activate

Set WordApp = Nothing
Exit Sub

ErrHandler:
Set WordApp = Nothing

End Sub

I have searched but to no complete solution.

Your help will be appreciated

Regards

 

[yeatmanj]

objWord.ActiveDocument.Bookmarks("LeaseName").Sele ct
'Goto what:=wdGoToBookmark, Name:="LeaseName"
' objWord.Selection.Text = Me![LeaseName]
objWord.Selection.Text = Forms!frmLeaseAdjustment!LeaseName

Is there a space in the word select from the first line in your code or is that a typo here?

In my earlier code example, to insert the record into the bookmark location I used selection.typetext. Like this:

.Selection.Goto wdGoToBookmark, Name:="SiteName"
.Selection.TypeText rstFiltered![Site Name]

You'll notice that I also didn't use "=" except in the name of the bookmark. Hope this helps.

 

[EMP]

I have Access and Word 2003 with XP.

............. I have downloaded the accesstoword.mdb in another thread. That works fine ...........

I believe you were referring to the example accesstoword_97.zip by Jon K in post #9 in the thread:

Access to Word Automation
http://www.access-programmers.co.uk/forums/showthread.php?t=40119


Jon's example works perfectly on my system of Access and Word 2000, too.

^