Complicated Project Involving a Word App and A query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need To develop a process that Takes a check box on a form ... and if
selected launches a word document that automatically mail merges in fields
from the requesting form .. then clears the checkbox after the word Doc is
launched / saved would be better.
 
Albert I have made a small code snippet that executes the Word Document
launch but need to be able to include multiple records and allow users to
track wether or not they have sent or created the mail merge document. Can
this code accomplish this

MY CODE SOURCE IS

Private Sub INVITE_TO_TEST_LETTER_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.Documents.Open "c:\ADMINTEMP\SOMELETTER.doc"
End Sub
 
You code does not show how WHAT records you are going to select..

How does the selection process occur?

Lets assume you have a DateEdit field (this would be updated when you edit a
record for today...).

So, at the end of the day, you would want to merge all customers into a word
document.

My code library would be:

dim strSql as string
dim strToday as string

strToday = "#" & format(date(),"mm/dd/yyyy") & "#"

strSql = "select * tblCustomer where EditDate = " & strToday

MergeAllWord strSql

So,my sample library code would work...at this point however, you need to
"process" those records that were sent to the word merge.

We could for example set a "done" flag.

currentdb.Execute "update tblCustomers set Done = True where EditDate = " &
strToday

The above would thus process the SAME records we just sent to the word
merge.

So, one needs to expand on how you plan to select those records to be sent
to the word merge, but as the above shows, once you come up with some
approach to selecting the records, it is both easy to send them to word..and
further easy to "set" some flag with a update query as above....

If you have the query already built..then the code would be:

MergeAllWord "myQueryName"

currentdb.Execute "update tbl........etc etc "


You would then have to make a update query based on how you defined the
above query to select names (as I said...how you do that is unknown..but the
above approach would work).
 
I have a parameter query that allows users to set the range of dates that
they would like to merge - I would like to get away from using that - but the
query SQL (pasted directly from MSACCESS) is
********************************************************
SELECT [Applicant Data].First_Name, [Applicant Data].Middle_Init, [Applicant
Data].Last_Name, [Applicant Data].Invite_to_Test, [Applicant
Data].Record_Created
FROM [Applicant Data]
WHERE ((([Applicant Data].Invite_to_Test)=True) AND (([Applicant
Data].Record_Created) Between [What Is The First Date MM/DD/YYYY] And [What
Is The Last Date MM/DD/YYYY]));
***********************************************************
the Record_Created field is a lot like your DateEdit field but is kicked off
by an onCurrent event from the Applicant Data Form. And If I do choose to
use your code - how does one implement it into thier form ? ... what is the
kick off event ?? - kind of new to this - sorry
 
Ok...what I would do is create a nice looking form that has the startDate,
and the EndDate.

You could be very simple...with just two text boxes...or quite fancy. Take a
look at the following screen shots (and note how many have a word merge
button on them).

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

So, lets just assume you create a nice little form that will prompt the user
for the start/end date. You put that button on your existing edit form...

the code to launch this cute prompt form would be:

me.Refresh ' must write the forms data to disk before we
' do any reports..or merging of data

docmd.OpenForm "MyCutePromptForm"


at this point, the user would see a nice little start/end data prompt form
(as I mention, jus put two text boxes on it...if you have time later..then
consider using calendar controls like my examples above...but lets keep this
simple for now..and assume a plane Jane un-bound form with the start and end
dates for a prompt...and button to merge to word.

Note that our sql query will have NO parmaters...as we will supply those in
code. So, here what we now have behind the merge buttion:

dim strStart as string
dm strEnd as string
dim strSql as string

strSTart = "#" & format(me.txtStart,"mm/dd/yyyy") & "#"
strEnd = "#" & format(me.TxtEnd,"mm/dd/yyyy") & "#"

strSql = "SELECT First_Name, Middle_Init, Last_Name" & _
", Invite_to_Test, ecord_Created FROM [Applicant Data] " & _
" WHERE (Invite_to_Test = True) AND " & _
"Record_Created Between " & strStart & " and " & strEnd

MergeAllWord strSql

' now udpate the reocrds....

strSql = "update [Applicant Data] set TestDone = true " & _
" where Record_Createed Between " & strStart & " and " &
strEnd

docmd.RunSql strSql
'currentDb.Execute strSql

When you get this working..change the runsql to the currentdb.Execute...as
that will NOT prompt the user to confirm the update....
 
Hey Albert -

Thanks for the update on how to do this . I made one of the cardinal
mistakes in coding . I showed a client something that I could not accomplish
on my own. So I come to you again to ask how to generate the calendar
controls that you had in your demonstration.

The mail merge worked wonderfully . but users are wanting to filter it down
to letters sent by individual representatives so I have to add another
"Letter_Rep" kind of criteria and add that into the SQL string.
 
I come to you again to ask how to generate the calendar
controls that you had in your demonstration.
Click to expand...

In those screen shots, I simply put in the calendar control that ships with
ms-access.

(however, be forewarned, that often MANY computers will NOT have this
component installed, and this opens up your application to breakage if you
move
it to another machine that does not have the control install.ed.).

So, you can do some testing to try this out. On a form in design mode, just
go insert->ActiveX control

Now, choose the Calendar control 11 (11 for access 2003, 10 for access 2002
etc.).

This calendar will thus appear on the form, and can be used in place of a
text box.

The mail merge worked wonderfully . but users are wanting to filter it
down
to letters sent by individual representatives so I have to add another
"Letter_Rep" kind of criteria and add that into the SQL string.
Click to expand...

Good ...you have this working!!! Excellent...

Lets continue to assume a start/end date...but now you also add Letter_Rep
criteria
(this could be a text box, or even a nice combo box to select from).

The code would thus become:

dim strStart as string
dm strEnd as string
dim strSql as string

strSTart = "#" & format(me.txtStart,"mm/dd/yyyy") & "#"
strEnd = "#" & format(me.TxtEnd,"mm/dd/yyyy") & "#"

strSql = "SELECT First_Name, Middle_Init, Last_Name" & _
", Invite_to_Test, ecord_Created FROM [Applicant Data] " & _
" WHERE (Invite_to_Test = True) AND " & _
"Record_Created Between " & strStart & " and " & strEnd

if isnull(me.txtMyRep) = false then

strSql = strSql & " and Letter_Rep = '" & me.txtMyRep & "'"

end if

MergeAllWord strSql

' now udpate the reocrds....

strSql = "update [Applicant Data] set TestDone = true " & _
" where Record_Createed Between " & strStart & " and " & strEnd & _
" and Letter_Rep = '" & me.txtMyRep & "'"
docmd.RunSql strSql
 
Albert -

Can I use pre-existing templates with MergeAllWord - or do I have to design
them with the GuiWordTemplate ?

Albert D.Kallal said:
I come to you again to ask how to generate the calendar
controls that you had in your demonstration.
Click to expand...

In those screen shots, I simply put in the calendar control that ships with
ms-access.

(however, be forewarned, that often MANY computers will NOT have this
component installed, and this opens up your application to breakage if you
move
it to another machine that does not have the control install.ed.).

So, you can do some testing to try this out. On a form in design mode, just
go insert->ActiveX control

Now, choose the Calendar control 11 (11 for access 2003, 10 for access 2002
etc.).

This calendar will thus appear on the form, and can be used in place of a
text box.

The mail merge worked wonderfully . but users are wanting to filter it
down
to letters sent by individual representatives so I have to add another
"Letter_Rep" kind of criteria and add that into the SQL string.
Click to expand...

Good ...you have this working!!! Excellent...

Lets continue to assume a start/end date...but now you also add Letter_Rep
criteria
(this could be a text box, or even a nice combo box to select from).

The code would thus become:

dim strStart as string
dm strEnd as string
dim strSql as string

strSTart = "#" & format(me.txtStart,"mm/dd/yyyy") & "#"
strEnd = "#" & format(me.TxtEnd,"mm/dd/yyyy") & "#"

strSql = "SELECT First_Name, Middle_Init, Last_Name" & _
", Invite_to_Test, ecord_Created FROM [Applicant Data] " & _
" WHERE (Invite_to_Test = True) AND " & _
"Record_Created Between " & strStart & " and " & strEnd

if isnull(me.txtMyRep) = false then

strSql = strSql & " and Letter_Rep = '" & me.txtMyRep & "'"

end if

MergeAllWord strSql

' now udpate the reocrds....

strSql = "update [Applicant Data] set TestDone = true " & _
" where Record_Createed Between " & strStart & " and " & strEnd & _
" and Letter_Rep = '" & me.txtMyRep & "'"
docmd.RunSql strSql


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
And also .. sorry to keep "one more thing-ing" you to death . But is there
any way to - based on the Template Chosen - update a check box field In the
Applicant Data table . We Have three or four letter templates located on a
network share
(hence the need to use existing templates) that we send out depending on
various criteria. One is the Invited To Test Letter - Which Has a Check box
control Called Invite_To_Test. Another is an Invite To Interview Letter -
Which has a check box control called Invite_To Interview . Another is No
Miinumum Qualifications Letter 1 & 2 which have no controls but need thier
own templates nonetheless.

Any ideas - btw I dont think I have to tell you this - but I am very
impressed with your knowledge and speed of response.

HelloWorld said:
Albert -

Can I use pre-existing templates with MergeAllWord - or do I have to design
them with the GuiWordTemplate ?

Albert D.Kallal said:
I come to you again to ask how to generate the calendar
controls that you had in your demonstration.
Click to expand...

In those screen shots, I simply put in the calendar control that ships with
ms-access.

(however, be forewarned, that often MANY computers will NOT have this
component installed, and this opens up your application to breakage if you
move
it to another machine that does not have the control install.ed.).

So, you can do some testing to try this out. On a form in design mode, just
go insert->ActiveX control

Now, choose the Calendar control 11 (11 for access 2003, 10 for access 2002
etc.).

This calendar will thus appear on the form, and can be used in place of a
text box.

The mail merge worked wonderfully . but users are wanting to filter it
down
to letters sent by individual representatives so I have to add another
"Letter_Rep" kind of criteria and add that into the SQL string.
Click to expand...

Good ...you have this working!!! Excellent...

Lets continue to assume a start/end date...but now you also add Letter_Rep
criteria
(this could be a text box, or even a nice combo box to select from).

The code would thus become:

dim strStart as string
dm strEnd as string
dim strSql as string

strSTart = "#" & format(me.txtStart,"mm/dd/yyyy") & "#"
strEnd = "#" & format(me.TxtEnd,"mm/dd/yyyy") & "#"

strSql = "SELECT First_Name, Middle_Init, Last_Name" & _
", Invite_to_Test, ecord_Created FROM [Applicant Data] " & _
" WHERE (Invite_to_Test = True) AND " & _
"Record_Created Between " & strStart & " and " & strEnd

if isnull(me.txtMyRep) = false then

strSql = strSql & " and Letter_Rep = '" & me.txtMyRep & "'"

end if

MergeAllWord strSql

' now udpate the reocrds....

strSql = "update [Applicant Data] set TestDone = true " & _
" where Record_Createed Between " & strStart & " and " & strEnd & _
" and Letter_Rep = '" & me.txtMyRep & "'"
docmd.RunSql strSql


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
 
HelloWorld said:
Albert -

Can I use pre-existing templates with MergeAllWord - or do I have to
design
them with the GuiWordTemplate ?
Click to expand...

Yes, you can. However, two things to remember.

1) For some strange reason, I choose the .doc extension for a template.
Really, the word standard is to use .dot. So, I made a real bad design
decision here. I should have make my software create .dot extensions. So,
if you have a existing document (or template), simply copy it to the "word"
dir, but REMEMBER to rename the extension from .dot to .doc. (my documents
are templates...but with a doc extension).

2) Make sure you hit the "modify template" button to actually "edit" the
template you just copied to the directory. This is a good idea anyway, since
it is very possible that "merge field" names are changed, or not correct.
Even the field names are correct, you still want to "modify" the
template, since what mdb, or data source is your current template document
attached to? (we don't know..so, at least modifying the template once means
you check the field names..and *most* important, when you hit "modify", my
code sets up the connection to the text file.

3) If the merge document has NO fields, when you hit "merge",
then the document will come up blank
(this is a word issue..and not my code). For some strange reason, when you
"execute" a word merge to a document with no merge fields...it don't work
(you wind up with a blank document).
..
So, in addition to the above to steps, you also need to ensure that last ONE
valid merge field exists in the document.

If you ensure the above 3 issues..then using existing documents
should not be a problem.

I think in my next update to that code, I going to use the .dot
extension..but what to do with the "existing" user base....
 
And also .. sorry to keep "one more thing-ing" you to death . But is there
any way to - based on the Template Chosen - update a check box field In
the
Applicant Data table . We Have three or four letter templates located on a
network share
(hence the need to use existing templates) that we send out depending on
various criteria. One is the Invited To Test Letter - Which Has a Check
box
control Called Invite_To_Test. Another is an Invite To Interview Letter -
Which has a check box control called Invite_To Interview . Another is No
Miinumum Qualifications Letter 1 & 2 which have no controls but need thier
own templates nonetheless.
Click to expand...

Hum, not really. However, you could create two merge buttons (or 3). One
merge button would ONLY be for invite letters.

So, the code for the "invite" letters could be:

me!invite_To_Test = True
MergeSingleWord "InviteLetters\"


The code for Interview would be

me!invite_To_Interview = True
MergeSingleWord "InterViewLetters\"

And so on.....

So, essentially you "group" the letters. And, of course, you have to assume
reasonable users, as the letter may not in fact be printed, or actually
sent...so, there is going to be some "user" responsibility here to ensure
the letter is sent, but at least the above would "check" the appropriate box
for you when the merge code is run.......
 
Back
Top