Merge form data to word template 3

[colinmitton]

I'm setting up a business database and I've got the structure working the way I require, I can search / create / view / etc...

I now need to be able to send a letter to the business I've selected on a form. This is an individual business so its not a list. My plan is to have a button which opens a word template and merges in the business information from the form.

I'm looking at my books and they all seem to focus on merge lists from a generic 'Select record.* from Table' statement but I need to select the current record in a form?

I'm only just starting to learn Access 2010 for the first time and getting a little lost.

 

[PHV]

Use a WHERE clause in your SELECT statement.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Thanks for that I should really have thought of that!!!

Just So I'm not running in circles! I'm thinking about doing it the following way....

Dim strWhere As Variant

    strWhere = Null

    strWhere = "[3rdPartyBusinessName] = '" & [3rdPartyBusinessName] & "'"

    Dim wdApp As New Word.Application
    wdApp.Documents.Add
    wdApp.Visible = True

    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:="TABLE tbl_3rd_P_Business", _
        SQLStatement:="SELECT 3rdPartyBusinessName.* FROM 3rdPartyBusinessName Where strWhere;"

Thanks

 

[PHV]

I'd replace this:
SQLStatement:="SELECT 3rdPartyBusinessName.* FROM 3rdPartyBusinessName Where strWhere;"
with this:
SQLStatement:="SELECT 3rdPartyBusinessName.* FROM 3rdPartyBusinessName Where " & strWhere

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Thanks...

 

[kjv1611]

Can I ask why you're setting Dim strWhere As Variant
??

If you're building a SQL string, you simply need set it as a string - uses less memory, the string variable is not as flexible as a variant, but it's more efficient.

So, I really don't see any reason for that line to not be:

Dim strWhere As String

 

[colinmitton]

Thanks for that, I'm following a few things from books and some code I used previously worked with the 'As Variant' so I just kept it in. I will change it over.

 

[colinmitton]

So close.....

My code (with your help) works almost spot on!

Private Sub Cmd_merge_to_word_Click()
Dim strWhere As String
Dim wdApp As New Word.Application

    strWhere = "[3rdPartyBusinessName] = '" & [3rdPartyBusinessName] & "'"

    wdApp.Documents.Open "C:\2010\Word\Merge\3rd_P_Merge_Prop.dotx"
    wdApp.Visible = True

    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Colin\Documents\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strWhere
    wdApp.ActiveDocument.MailMerge.Execute

    Set wdApp = Nothing

End Sub

Only 2 issues...
1st The code open up the merge file first then creates a second document with the merge information in.
2nd how do I get the Word document with the merged infomation in to the front of the screen (the active window?)

Thanks

 

[kjv1611]

Maybe you can just add something to the extent:
wdApp.Documents.Open

Near the end of your code... before Set wdApp = Nothing

It sounds like it's opening the template, then creating a new file off the template.

I've not tried doing it in Word, myself, but in Excel, you want to create a new file from the template file rather than opening it. Perhaps you can use a "New" or "Create" rather than "Open" command to begin with?

 

[colinmitton]

KJV1611

Thanks I've tried that now but it did not work.

I'm now trying to rack my brain as I'm not a programmer by any stretch but in a previous excel macro I wrote (again with help from Tek-Tips) I declared two excel workbooks (current book and a new file I created) so I could work on each simply.

I'm not sure how to do it in this environment though. I've tried to do something around the following with my code but I'm guessing I've missed something obvious! (I've been playing around a bit so have commented out a few things!)

Private Sub Cmd_merge_to_word_Click()
Dim strWhere As String
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document


    strWhere = "[3rdPartyBusinessName] = '" & [3rdPartyBusinessName] & "'"

    wdApp.Documents.Add "H:\My Documents\Access\Merge Prop.dotx"
    wdApp.Visible = True

    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\My Documents\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strWhere
    wdApp.ActiveDocument.MailMerge.Execute

'    Set wdMMDoc = wdApp.ActiveDocument
'    wdMMDoc.Activate

    Set wdApp = Nothing
    Set wdMMDoc = Nothing
End Sub

 

[colinmitton]

Got it!!!

Here's my code if anyone wants to know and any suggesdtions to clean it up are most welcome.

Private Sub Cmd_merge_to_word_Click()
Dim strWhere As String
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document
' Get the Info for the where statement on the SQLStatement
    strWhere = "[3rdPartyBusinessName] = '" & [3rdPartyBusinessName] & "'"
' Open the merge document and give it a name!
Set wdMMDoc = wdApp.Documents.Add("H:\My Documents\Access\Merge Prop.dotx")
    wdApp.Visible = True
' Connect to datasource and run sqlstatement to pick the info from the form
    wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\My Documents\Access\DFA v2.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
        , SQLStatement:="SELECT * FROM `qry_3rd_P_address_merge` Where " & strWhere
' Run the merge
    wdMMDoc.MailMerge.Execute
' Close the Merge Document
    wdMMDoc.Close False
' Bring the merged letter to the front
    wdApp.Activate
' tidy up
    Set wdApp = Nothing
    Set wdMMDoc = Nothing
End Sub

Thanks for the suggestions PHV and KJV1611

 

[kjv1611]

Yeah, that's what I was talking about - the .Add command, I just couldn't remember what it was called. Basically that works the same as if you had a template file and double-clicked on it.

Glad it worked out

 

[colinmitton]

Typical!

I've come across a little glitch by searching on the business name some have ' in there names and I get an error! so on second thoughts I would use the unique ID (which is the index & Primary key).

When I adjusted the code around I know get a issue when it tries to open the DB as it says the DB is already open? The merge will then not happen.

Do I have to declare my strWhere differently?

Dim strWhere As String

Bit confused...

 

[PHV]

strWhere = "[3rdPartyBusinessName] = '" & [!]Replace([/!][3rdPartyBusinessName][!], "'", "''")[/!] & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Thank's PHV, thats solved the ' problem, just as a side question if in the future I plan to use the index / primary key is it possible?

 

[kjv1611]

PHV,

That is just plain cool. I should have known, but didn't realize that you could use a function pretty much straight inline with setting a variable to a value in that manner. That's a neat way to shorten what I'd of done in probably 2 lines.