Recreate old-style parameter query mail merge

[TourOperator]

Hi,

I am trying to create VBA code (I have very little experience of VBA) to recreate a parameter query mail merge, which Access 2013 cannot achieve with the controls provided.

I think that I need to assign a variable to the value of a control on my form then assign that variable to the relevant criteria section of my query.

I have this so far:

DIM RefNo as String
DIM IDNo as String
RefNo = Me.[Reference Number].Value
IDNo = Me.Text582.Value
DoCmd.OpenQuery “SORTER”
‘How do you paste into criteria?

From there I would like to do a Word Mail Merge by having VBA open a document in Word, then activate the Merge to a New Document instruction.

Any help would be much appreciated.

Cheers.

 

[GinaWhipp]

To use a parameter query like that you have to build the Statement and put the Criteria in via VBA, i.e.

 strSQL = "SELECT blah, blah, blah " & _
               "FROM blah, blah, blah " & _
               "WHERE (((YourTable.YourFieldInTable)= " & Me.YourControlOnFom & "))"

Then use the strSQL for the Word Merge.

 

[TourOperator]

Thanks, Gina. I really appreciate your advice. You are saying forget about my existing query and create one using SQL, and use that as the basis of a Word Merge. Great, it's good to be put on the right track. I will read up on using VBA (I presume it can be an array) in Word Merge.

Cheers,

Peter

 

[GinaWhipp]

I wouldn't presume and Array but yes that is what I am saying. If you get stuck, we are here. Also have a look at...

Super Easy Word Merge by Albert Kallal...
http://www.kallal.ca/msaccess/msaccess.html

Why reinvent the wheel?

 

[TourOperator]

Thanks, Gina

I will work on the SQL and then figure out the Word Merge in VBA. Ideally I'd like a button that takes you right through to the completed merge. I see there are some posts on this topic, so I'll read up. Albert Kallal's article is for mdb and I'm using a accdb. Also my query is based on several tables so I'm not sure I could use his method directly from the form.

I appreciate your help. It is very kind.

Peter

 

[GinaWhipp]

I use Albert's in an .ACCDB with no changes, so don't let that stop you. And, his code is a starting point...

Remember, we're here if you run into issues!

 

[TourOperator]

Hi Gina,

I had a go at the VBA, but the Test.docx is not referring to the strSQL as a Data Source. Here's my code:

Private Sub TestButton_Click()
Dim strSQL As String
strSQL = "SELECT [Lead Name]" & _
               "FROM Green Book " & _
               "WHERE ((([Green Book].[Reference Number])= " & Me.[Reference Number] & "))"

Dim wrdApp As Object
Set wrdApp = GetObject(, "Word.Application")
wrdApp.Visible = True
wrdApp.Documents.Open "F:\Destinations\Merge Documents\Test.docx"
Set wrdApp = Nothing

' open the MERGE
ActiveDocument.MailMerge.OpenDataSource Name:=strSQL, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:="", SubType:=wdMergeSubTypeAccess

' do the MERGE
With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
End With

End Sub

Any ideas?

Peter

 

[GinaWhipp]

Hmm, before we do anything else... does your strSQL actually return any data? You can put breakpoints in the code to check.

 

[TourOperator]

I added square brackets around the table name and a space before 'FROM'.

By using Debug.Print strSQL I can see the SELECT query. Seems to be OK.

How do I see the returned value ('Lead Name') rather than the SELECT query itself, if it is indeed returning one? I tried MsgBox strSQL, but that only shows the SELECT query.

 

[TourOperator]

When I run this now, it gets as far as the 'open the MERGE' section then an error message says 'Object required'. Does this mean there is no value in strSQL or is the ActiveDocument.MailMerge.OpenDataSource configured wrongly?

 

[GinaWhipp]

Sounds to me like Word is required, like it can't find Word. Did you review Albert's code? When I look at it seems like you are missing some lines...

 

[TourOperator]

Hi Gina,

Word opens OK (with my text.docx), but the code runs into a problem with the 'ActiveDocument.MailMerge.OpenDataSource' section. I get 'runtime error 424 object required'.

Albert's code is a bit complex for me. I think that it creates a mail merge with info from a single form, but my queries are from several tables so I won't have a single form for them.

Thank you for your help so far with this. I am a fan of databases, but I find that sometimes I need to bang my head on a wall for a week to find a solution to a problem!

 

[GinaWhipp]

Word may open but I'm not sure it's seeing Word. Hmm, just to be sure have you put in the Breakpoints to make sure the query is returning something.

That said, you can use a query which joins several tables with Albert's code.

 

[TourOperator]

Hi, I haven't given up!

I am trying a new tack. The MailMerge.OpenDataSource method seems to allow you to specify the SQL statement so I have altered the code like this:

Private Sub TestButton_Click()

' Opens Word Document
Dim wrdApp As Object
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
wrdApp.Documents.Open "F:\Destinations\Merge Documents\Test.docx"
Set wrdApp = Nothing

' open the MERGE
ActiveDocument.MailMerge.OpenDataSource Name:="F:\Databases\Pettitts 205.accdb", ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="SELECT [Lead Name]" & " FROM [Green Book]" & " WHERE (([Green Book].[Reference Number]) = " & Me.[Reference Number] & ")", SQLStatement1:="", SubType:=""

' do the MERGE
With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
End With

End Sub

But I am still getting 'Runtime error 424 - Object required' on the ActiveDocument.MailMerge.....line.

I don't really know how to use breakpoints to see if the query is returning anything.

 

[TourOperator]

Hi Gina,

I have tried (and failed) to create my own VBA project.

I have applied Albert's code to my problem and found it very useful (as you told me it would be!). It works with queries based on multiple tables and I find the MergeNo Prompts to be excellent. Just a couple on questions on the code:

Can it be altered to create a Directory? This would probably have to be something in the VBA code for the button that alters the MailMerge.MainDocumentType from 0 to 3 (wdDirectory).

Also, how can I Maximize the Word window at the end of the process (it opens as a half-size window)?

Thank you, as always, for your help.

Peter

 

[GinaWhipp]

Sorry for delay... *day* job got in the way!

Hmm, have never done it but I would say yes you can add code to create a directory. However, I would not amend the code I would write that on the Command Button to check for a directory and if not found create it and then put the line to create the Mail Merge.

To open Word maximized try...

wrdApp.Maximize