Albert Kallal Mail Merge from a query

[magicdds-]

I am using Albert Kallal's mail merge to merge data from my database to
letters in Word. The problem is that when I click on a button on my form to
open the mail merge form, the mail merge form uses the table that the form is
based on to get mergefields.

If I click on a button on the form which opens a second form, If form #2 is
based on the query that I want to use to merge, I can have a button on form#2
that opens the mail merge form. The the mail merge form uses the query that
form#2 uses as the record source as the source for the merge fields.

I would like to use the query that form #2 has as the record source, as the
source for the merge fields. I was wondering if there was a way to click on a
button on form #1 (which uses a table as it's record source) to open the mail
merge form and use the query as the source for the mergefields, without
opening form #2?

Thanks
Mark

 

[Albert D. Kallal]

yes, you can specify any query you want, and NOT use the forms reocrdset.

Just go:

MergeAllWord "name of query or sql goes here"

Additional features are outlined here:

http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html

So, just go

MergeAllWord "query2"

Just use the same query that form 2 users (there is no need to open the
form, since above shows you can specify the sql (or query) for the data
source...

 

[magicdds-]

Albert,
I first tried

MergeAllWord "LetterQuery"

and got error message:

No Data was created for this merge
Make sure the sql is correct
sql was
LetterQuery

When I opened LetterQuery, there was a line of data which was from the
Access form that was open.

I also tried

MergeSingleWord "LetterQuery"

But that just tried to look for and store letters in a new directory called
"LetterQuery".

Any ideas on what else I could try?
Thanks,
Mark

 

[Albert D. Kallal]

Albert,
I first tried

MergeAllWord "LetterQuery"

When you go the query desinger, can you open up letterQuery...does it work
fine without any other forms open?

Perahps try the sql such as:

MergeAllWord "select * from LetterQuery"

LetterQuery should be free of forms expresisons etc. It should be clean sql.

If you need conditions, then go:

dim strSql as string
strSql = "select * from LetterQuery where City = 'Edmonton'"
MergeAllWord strSql

 

[magicdds-]

Albert,

I tried both options without any success.

What does seen to work is I modified the module "MergeSingleWord" as shown
below.

The form "LetterMergeButton" does nothing, but it is based on the Query
"LetterQuery" which is the query that has the data I want to use.
I open the form, MakeMergeText, and then close the form.
I can't seem to get anything else to work.

Dim frmF As Form
Dim strDirPath As String ' full path name to working dir

DoCmd.OpenForm "LetterMergeButton"

Set frmF = Screen.ActiveForm
frmF.Refresh

strDirPath = DirToPath(strDir, bolFullPath)

' output our simple merge file

If MakeMergeText(frmF, strMergeDataFile) Then
DoCmd.OpenForm "GuiWordTemplate", , , , , , strDirPath & "~" &
strOutPutDoc
End If

DoCmd.Close acForm, "LetterMergeButton"

End Function

I would, however, like to thank you for providing us with this wonderful
code. This really makes life easier when trying to merge Access data into
Word documents.

Thanks so much,
Mark

 

[Albert D. Kallal]

Albert,

I tried both options without any success.

Very strange.

could you post the simple code behind the button that does not work. (it
should be only 2-3 lines of code).

As mentioned, you don't need to open that 2nd form, but just supply the
query name (or sql) to

MergeAllWord "your sql goes here"

I assume all you code compiles (in code go debug->compile).

Anyway, it looks like you have a workaround, but I am stumped as to what the
bug/problem here is, and would have like to fixed it....

 

[magicdds-]

After a few tweeks, I got it to work!

Here is the trick

1) Like you said, get rid of conditions in the query
2) Use MergeAllWord, Not MergeSingleWord
3) The code behind the button should be:

Dim strSql As String
strSql = "select * from LetterQuery where PatientID = " & Me!PatientID
MergeAllWord strSql

The trick is, use

strSql = "select * from LetterQuery where PatientID = " & Me!PatientID

and not

strSql = "select * from LetterQuery where PatientID = Me!PatientID"

Thanks again for your help
Mark

 

[Albert D. Kallal]

Excellent...thanks for the follow up.....

 

[magicdds-]

Now I have a new challenge!

I have been trying to use the MergeNoPrompts part of your code.
I have a table called DAYFILE which holds records containing data for
letters to be printed at a later time (when convenient for the user).

I was wondering if you had a solution to this next kink in the puzzle.

The first field in the table is called LETTERNAME.
Each record has all the Merge Fields but the first field is LETTERNAME.
When clicking on a button on a form, the following code runs:

Dim strSQL As String
strSQL = "select * from DayFile"
MergeNoPrompts DLookup("LetterName", "Dayfile"), "C:\Documents and _ &
Settings\Mark\Desktop\NewGen\Word\", True, , strSQL

With that, a letter is created in Word for each record in the table DAYFILE.
However, the letter is the same for each record. That is, the letter that is
merged is the LETTERNAME specified in the first record.

Is there a way to get each letter to be the letter specified in the field
LETTERNAME for each record?

Thanks,
Mark

 

[Albert D. Kallal]

Now I have a new challenge!

Actually in this scenario that you are outlining, I would actually come back
and ask how did you set what letter name the users supposed to get? And, at
what point in the program or user interaction did this setting of the letter
occur?

The reason why I'm asking the above is, instead of having one big processing
loop that goes thought the "dayfile", you could simply Call the
mergeNoPrmpts WHEN your code adds a record to the dayfile (or, more
importantly when you actually set what letter going to be sent out) . That
way, you have all the documetns created and no "extra" process to be run at
the end of the day. It also not clear if dayfile is to be the data source
for the word merge, or if datafile ONLY has the "id" + letter name for the
user?

If the data source is different than what you could do, is when you write
out the dayfile, you would call MergeNoPrompts, and the add the actual
document name (with full path name) named that merge no prmopts created to
your day file table. What this would mean is that you could actually merge
document from different forms and even different data sources.

for the time being, let's ignore the above suggestion an approach.

We can simply write some code that would process the day file as you need.

dim rstDayFile as dao.RecordSet
dim strSql as string
dim strSqlforWord as string
dim strDirPath as string

strDirPath = "C:\Documents and _ &
Settings\Mark\Desktop\NewGen\Word\

strSql = "select * from DayFile order by id"
set rstDayFile = currentdb.OpenReocrdSet(strSql)

do while rstDayFile.Eof = false
strSqlForWord = "select * from dayFile where id = " & rstDayFile!id
MergeNoPrompts rstDayFile!LetterName, strdirPath, True, , strSQL
rstDayFile.Movenext
loop
rstDayFile.close

Notice how the above loop has to actually specify the correct SQL, and
select the one record that we need. I'm assuming that you have a primary
key, or auto number field of ID in this list.

I've also never tested this code in a loop, and my spider sense tells me
there's going to be some order of printing problems because of the way word
prints documents out. I know how to fix this problem, but I guess you can
give the above a try and see how well it works. Note that the above code is
what we call air code, and I simply typed it as I wrote this message. So the
above is not tested, but it should give you the general idea as to what you
need to do.

 

[magicdds-]

This is so cool!
It works great.

I just had to change

MergeNoPrompts rstDayFile!LetterName, strdirPath, True, , strSQL

to

MergeNoPrompts rstDayFile!LetterName, strdirPath, True, , strSqlForWord


Thanks so much for your help.
Mark

 

[magicdds-]

Thanks for your help so far. I am running into one more problem.
I created another folder in the same directory where the WORD folder in
located. While the WORD folder contains the letters and I created ENVELOPE
folder to contain envelopes to go along with each letter. The envelope should
only get printed if a check box on the form MAIN is checked.
I added code in the event procedure behind the Command Button cmdMerge in
the "GuiWordTemplate" form. Here it is:

Private Sub cmdMerge_Click()

' merge the doc
If IsNull(Me.lstFiles) = False Then

Call RidesMergeWord(strDirPath & Me.lstFiles & ".doc", strDirPath,
strOutDocName)

' added for envelopes
If forms!Main!envelope = -1 then

If IsNull(Me.OpenArgs) = True Then
strDirPath = DirToPath("envelope\", False)

Else
strDirPath = strDField(Me.OpenArgs, "~", 1)
strOutDocName = strDField(Me.OpenArgs, "~", 2)
End If

Call RidesMergeWord(strDirPath & Me.lstFiles & ".doc", strDirPath,
strOutDocName)

End If

' end of code for envelope

DoCmd.Close acForm, Me.Name

Else

MsgBox "You need to select a Word document", vbExclamation, "Word Merge"

End If

End Sub


When I tested for Me.OpenArgs, it is Null
What is happening when I click on the Command Button, I get 2 copies of the
letter from the WORD directory and nothing from the ENVELOPE directory. What
I want is the letter from the WORD directory, and the envelope (with the same
file name as the letter) from the ENVELOPE directory, to open in two separate
instances of Word windows.

I can't understand, since I changed the path to the ENVELOPE directory in
the code above, what I am doing wrong. Could you see if you could figure it
iut?

Thanks again,
Mark

 

[Debba]

Hi, I am also trying to word merge from a form.
I have a combo box form that is on the query where the user chooses either
'friend', 'business', or 'vendor'.
the query then gives all the records that the user selected in the combo box
and displays them on a form.
MergeSingleWord works except it only gives the first record.
MergeAllWord produces the error message

No Data was created for this merge
Make sure the sql is correct
sql was

my form or query name

Help! please

 

[Albert D. Kallal]

When you use mergeAll word, you have to specify a query or table.

That query or table MUST NOT have any parameters. So, for your code you can
go:

dim strSql as string

strSql = "select * from MyTable where Type = '" & me.cboBtype & "'"
MergeAllWord strSql

So:
In the above change MyTable with the name of the table you have the data in.
Change Type to the name of the column in the table that represents the type
(friend, business, vendor)
Change cboBtype in the above to the name of your combo box.

Again, REMOVE ALL parameters from the query if you not using the table name
in the above