From Access to Word with conditions

  • Thread starter Thread starter MAC
  • Start date Start date
M

MAC

Hello:

I am trying to merge fields from all records in an Access database to Word
document (with the merge fields already in it). It works well, but I need
to tell Access to do the merge ALL that meet two condtions: 1) if a field
named "done" is UNchecked and 2) if the field "Letter1" is CHECKED (the same
will continue for Letter2, Letter3 & Letter 4). How & where do I place
these conditions? The event I placed in a button is:

Private Sub Command1_Click()
Dim App As Word.Application
Dim Doc As Word.Document

Set App = New Word.Application
Set Doc = App.Documents.Open("C:\NAME OF FOLDER\Letter1.doc")

App.Visible = True
App.WindowState = wdWindowStateMaximize

With Doc.MailMerge
.Execute (done = False)
Doc.Close False
End With

End Sub

Any help will be appreciated!
 
Just build a query with the conditions in it. Then, use the query for the
source.

You could also grab my merge code. My library would then you do the
following:

dim strSql as string

strSql = "select * from tblCustomers where (Done = false) and (letter1 =
true)"

mergeAllWord (strSql)

The above is all you need with my merge sample. If you want, try downloading
my sample merge. Give the sample a try (it comes with data). If you like
it..then you can read the instructions on how to use the code in your
application.

However, you don't really need my example....just built a query and use that
for the datasouce in the word doc...

My sample can be found at:

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html
 
Base the merge doc on a query that includes all the
fields that you want and contains the conditions you need.

SELECT * From tblYourTable WHERE tblYourTable.Done =
False AND tblYourTable.Letter = "yourLetterNumber"

I'm not sure that the syntax is correct but you get the
idea.

Jesse Avilés
monk @ coqui . net
Reply to the newsgroups.
 
Albert, thanks for your help! I'd love to use your code, but I wouldn't
know how to adapt it to what I need (I just don't know enough to do it). I
need, with the click of one button, to merge all records with the field
"done" = False and the field Letter1 = True, then continue with "done" =
False and Letter2 = True, and so on. I prepared a Word doc for each type of
letter (1, 2, 3, 4).

At this point, I'm still in the stage of testing the conditions so it merges
to Letter1. I don't know why it's not working. It's still merging all
records without reading the conditions (regardless whether "done" is True or
not). This is the event I have on a button:

Private Sub Command1_Click()
Dim strSql As String

strSql = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"

Dim App As Word.Application
Dim Doc As Word.Document

Set App = New Word.Application
Set Doc = App.Documents.Open("FOLDER WITH WORD FILE FOR LETTER1 WITH
MERGE FIELDS")

App.Visible = True
App.WindowState = wdWindowStateMaximize

With Doc.MailMerge
.Execute
Doc.Close False
End With

End Sub

Any help would be greatly appeciated. For now, thanks for being
resourceful!

M. Costa
 
I need, with the click of one button, to merge all records with the field
"done" = False and the field Letter1 = True, then continue with "done" =
False and Letter2 = True, and so on. I prepared a Word doc for each type of
letter (1, 2, 3, 4).
Click to expand...

Since you have 4 special cases...then I would break this problem down into 4
separate buttons.

Button one code could thus be:

dim strSql as string

strSql = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"

MergeAllWord (strSql)

You then repeat the above code for 4 buttons. (or, even better would be to
put a combo box on the screen, and let the user select which letter number
(1 to 4), and then change the button code. That way..you don't use 4
buttons..but have 1 button..and combo box. However, lets just do one thing
at a time.
At this point, I'm still in the stage of testing the conditions so it merges
to Letter1. I don't know why it's not working.
Click to expand...

Your code as written does nothing with strSql. You are setting the value of
strSql..but then do nothing with it. So, dump your strSql...as it is not
being used. In fact, why not just save 4 queries with the conditions you
want. Then attach the 4 letters to each of those quires.

For example..you have:
Private Sub Command1_Click()
Dim strSql As String

strSql = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"
Click to expand...

Where does word, and where does your code know to use the above? As
mentioned, several responders (included me) has simply stated

Why don't you just put the conditions in the query? And then use the query
as the data source for the merge document? I think you are up to about 10
people who have suggested to you do this. Why have you not tried this
suggestion?

So, you a few choices here:

+) use my sample merge code...and create the 4 buttons (or one button..and
the combo box).

+) Use your code example..but complete dump the sql part in your code..and
simply place the conditions in some queries. (queryLetter1, qryLetter2, etc.
etc). Those queues will have the conditions already built in..and thus you
don't need, or have to set the sql conditions in code. (this is what most
responders have suggested to you, and I am still not sure why don't try this
idea?).

+) Modify your code..and have it set the datasouce of the word document. The
command to do that is:

WordDoc.MailMerge.OpenDataSource _
Name:=strSaveDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""

The above code was lifted from my merge code. You will still have to work on
the above to change the above sql statement etc.

It just seems to me that the easy way is to simply build a query with the
conditions built in (this eliminates any need to write or use sql in your
code). However, if those condistions are going to change a lot..then yes..I
would use my example above code.
 
I apologize, Albert.

It did work! I'd forgotten I had set up the merge fields in Word for the
table, not the query. It did work for all the letters. 4 different
instances of Word popped up with fields merged. Thank you so much.

I'd have one more questions, if you don't mind:
I have an update query that will check all the false done fields to true.
How do I run it right after the merge procedure?

Thank SO MUCH for your help, Albert & the rest of you!

MC
 
MAC said:
I apologize, Albert.

It did work! I'd forgotten I had set up the merge fields in Word for the
table, not the query. It did work for all the letters. 4 different
instances of Word popped up with fields merged. Thank you so much.

I'd have one more questions, if you don't mind:
I have an update query that will check all the false done fields to true.
How do I run it right after the merge procedure?
Click to expand...

You can do the above...but you might consider making it a extra step. (word
might get stuck..the printer might run out of paper etc.).

So, perhaps some type of "did it work" type question might be integrated
into your application. This way the user can answer yes, or no..and have an
ability to run it again. Since, once you set the flags...you can't run it a
gain. So, perahps you might make this a two step process.

However either way..you likely should create some update queries in the
query builder.

To run the update, you can make 4 update queries..and simply run them from
code.

Currentdb.Execute "qrySetLetters1"

(I am assuming you will make a query for each of the 4 letters).

In the query builder, you would make the above query, and set the conditions
for letter1 = true, and done = false. Set the update values also (Letter
updates to false, and done to true). All of this can be done in the query
builder, and then you can use the currentdb.Execute to run the query.

I presume, that you want to set letter = False, and done = true!

You can also do this in-line with code also, and not use the query builder

Something like:

strSql = "update test set letter1 = false, Done = true" & _
" where letter1 = True and Done = False"

currentdb.Execute strSql
Thank SO MUCH for your help, Albert & the rest of you!
Click to expand...

You are most welcome!
 
Albert, thank you for all your help.

I think it was the way I expressed myself in my last posting: it is working
now. I was already using a query to colonize the Word template (following
your instructions), but I'd forgotten to rename it. It's working fine now.

In fact, I placed all the merge instructions on one button and it opens 4
instances of Word, one for each template, therefore, with 4 types of
letters, depending on what boxes are checked. And it's working beautifully.
I work at an International Students office staffed mostly volunteers with
little, or no training. Your help was much appreciated! Having one button
doing everything is JUST perfect.

Now, I have an update query that will set the "done" fields in all merged
records to True (so that only new records with "done" = false merge to Word
next time). How do I write a code to run this query without the
confirmation/warning? The query I have is:

Dim stDocName As String

stDocName = "Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

I know it's basic, but how do I do it?

Again, thank you for everything, and I'm speaking for myself and all the
international students here.

Mac
 
Hi guys, it's me again:



I'm having problems with the Word merge. If there are no letters to be
merged, say, for "Letter1", the corresponding template loses its connection
with my query. I end up having to "fix" it, by reconnecting it to the
source (my query). Then it works well again, but the same happened when
retested with no Letter1 checked. So I decided to put a "if" condition for
it to merge. If "Letter1" field is not checked, it will give me a message
and move on to the next query for Letter2 & merge to the "Letter 2" Word
template, that is, if there are Letter2's to be merged. If not, a message
saying there are no "Letters 2's" and move on to "Letter 3", and so on.



The problem is that it is not reading the conditions, that is, it gives me
the message of "no letters" for all types of letters, regardless, without
performing the merge even when a type of letter is selected in the database.
Am I placing the "ifs" and "else's" in the wrong place? Does it need to be
"refreshed to accept new queries each time? Please help! I appreciate any
light on this, and sorry for being so "basic".



Here's what I have:





Private Sub Command1_Click()

Dim strSql As String

Dim strSq2 As String

Dim strSq3 As String

Dim Update As String



strSq1 = "SELECT * From test WHERE test.Letter1 = true AND test.done =
False"

If Letter1 = True And done = False Then



[My Merge to Word Procedure Here)



Update = "update test set letter1 = True, Done = true where letter1 = True
and Done = False"



CurrentDb.Execute Update



Else



MsgBox ("There are no Letters Type I?")



strSq2 = "SELECT * From test WHERE test.Letter2 = true AND test.done =
False"

If Letter2 = True And done = False Then



[My Merge to Word Procedure Here)



Else

MsgBox ("No one requested Letters 2 types. Don't you wonder why?")



strSq3 = "SELECT * From test WHERE test.Letter3 = true AND test.done =
False"

If Letter3 = True And done = False Then

[My Merge to Word Procedure Here)



Else

'MsgBox ("No one requested Letters 3 types. Don't you wonder why?")



End If

End If

End If

End Sub
 
Back
Top