Automating variable text in Word through Access

[khurley]

Hey there
I have created a database in Access which utilizes mail merge to create and print letters in Word automatically. The user doesn't even see Word open. I have the case now where some text in the Letter may become 'variable' based on certain criteria.

Whats the best way to do this? Find and Replace? Autotext? I realize there must be some human intervention to flag what text i need. Should I do it on the Access Form end or via Word? I may also have the case where 2 or pieces of variable text may need to go on the same letter.

HELP!

Thanks in advance!

 

[rookery]

What I do is use Bookmarks. Go into Word, set up various bookmarks (Insert->Bookmark) and then in your code tell Access to first find the bookmark in Word and then replace it with the relevant text. e.g.

Dim WordApp as Word.Application
Dim Adressee as String

Adressee = "67 Acacia Avenue"

WordApp.Selection.GoTo what:=wdGoToBookmark, Name:="Name1"
WordApp.Selection.TypeText Adressee

Good hunting!

 

[khurley]

OK.... i have multiple reasons which can be put into that bookmark. sometimes theres 1 reason sometimes 3 sometimes 6.... any ideas for a good way for my user to "check off" which reasons they need and have that combinations of reasons be placed in the bookmark? EX. If they need reasons 1, 3 and 4 maybe I can make check boxes for them and then send those thress reasons to the bookmark? any idea how to code something like that?

Thanks so much for helpin me....!
Kat

 

[rookery]

What exactly is a Reason? I assume it's info contained in a Text Box? If that is the case you could use checkboxes and code something like this:

Dim varInfo as String

varInfo = ""

For X = 1 To 6
If Me("CheckBox" & X) = True Then 'has been selected
varInfo = varInfo & Me("TextBox" & X)
End if
Next X

WordApp.Selection.GoTo what:=wdGoToBookmark, Name:="Name1"
WordApp.Selection.TypeText varInfo


Obviously the important thing here is to make sure that Checkbox1 relates to Textbox1 etc. The above example will concatenate all of the info in the TextBoxes. If you'd like to seperate them out with something like a Carraige Return you could use something like the following:

varInfo = varInfo & vbNewLine & Me("TextBox" & X)

but I'm not sure what exactly you're doing. I'm far from the most experienced on here so perhaps others will have different ideas.

Good hunting!

BTW any relation to Liz!?

 

[khurley]

Well the letter i am working on is a conditional letter for the renewal of an insurance policy. its a template letter which states that your policy will be renewed with the following condition(s) (I called them reasons before in this thread)
The conditions might be terrorism exclusion, blanket coverage, flood, etc ... i think i have 6 or 8 of them. the letter might need to have 1 of the 8 as a reason or there are some that will have multiple reasons. So i guess if i create a checkk box for each reason i can place each one on there with a carriage return after each in the same book mark?? do i just create normal checkbox's in my form (and reference them in the code? Its scary but this is starting to all make sense!!

And no relation to Liz I am sad to say..... or Bobby (the basketball player)

K

 

[rookery]

Yes that's how I would do it. A tad crude perhaps but it works and that's the bottom line. I'm glad that it's starting to come together. Let us know how you get on!

 

[khurley]

OK.... One more question....
My module for printing looks like this:

Public Function PrintLetters(FileName As String, Sqlcall As String, QueryName As String, Optional ByVal Copies As Integer) As Boolean
'On Error GoTo Error_Trap

Dim objWord As Word.Application, objWordM As Word.Document
Dim Count As Single, db As Database, rs As DAO.Recordset
Dim QdefSQL As QueryDef

Set db = CurrentDb
Set rs = db.OpenRecordset(Sqlcall)
If rs.EOF Then
PrintLetters = False
Exit Function
End If
While Not rs.EOF
Count = Count + 1.3
rs.MoveNext
Wend

If Count > 30 Then Count = 30
rs.Close

Set objWord = CreateObject("Word.application&quot

objWord.Visible = False

Set objWordM = GetObject("C:\Documents and Settings\jmkxhurl\My Documents\" & FileName)
objWordM.Application.Visible = False
objWordM.MailMerge.Destination = wdSendToNewDocument
objWordM.MailMerge.Execute

objWordM.Application.ActiveDocument.PrintOut , , , , , , , IIf(Copies = 0, 1, Copies)

objWordM.Close wdDoNotSaveChanges
Set objWordM = Nothing
objWord.Quit wdDoNotSaveChanges
Set objWord = Nothing
PrintLetters = True


Exit Function

My question is, using the logic provided in this thread to read the checkboxes, how do i get this print process to process to stop, read the check boxes of a record, insert the correct reasons, then move to the next record, perform that same logic and then print them all? Is it done from the access side or done from the Word side? I am trying to make it work but so far to no avail in this same module.

The module code i provided currently just mail merges in the name and address and policy number of who the letter is supposed to go to and then prints them. and it works great. But now i have to add this logic in for each individual letter that might be printed.

What do you think?!

Kathy

 

[rookery]

A couple of questions:

(i) I thought you were only printing the one letter at a time. I take it this isnt always true?

(ii) Are the 'reasons' for each client saved in a table or are pointers to a 'reasons' table saved?

 

[khurley]

Answers
(1)
I print any amount of letters on any given day. Depending on how many meet the criteria for sending.

(2)
I am working on creating a macro or something that could see which reason check boxes are checked by the user and then concatanate those together in a memo field. That way i can just pull the memo field into the bookmark from the datasource.

I am just not sure how to code yon macro... I think i basically want to code something that can do the following
There are 8 possible reasons.

If reason1chkbox= true
then put the reason 1 verbage in a memo field.
else
if reason2chkbox=true
then concatanate reason 2 ito memo field else
if resaon3 chkbox etc etc

move to next record and do it all again until the end of the recordset.

then the memo field will be populated with the correct reasons and i can just do my mail merge then.

sound logical? What do you think?

 

[rookery]

Afraid dont know the first thing about Macros. What I'd like to know is when you scroll thru' your clients how do you keep track of what reasons are set for each one of them.

e.g. Customer A has reasons 1, 2, 3 set.
Customer B has reasons 3 and 4 set. etc, etc

Am I along the right lines? This is the key.

 

[khurley]

i have a Form that the user will check off a checkbox for which reasons they need.

So that "flag" is in the table. Thats why i need Some way to:
1) go to record 1 , read the flags, concatanate the appropriate reasons in the memo field.
2) go to next record and do it again until end of records.
3) now i have all the records with the new memo field which has all the corret reasons for each record and i can perform the mail merge....

 

[rookery]

OK. This is a fairly crude way of doing it:

strInfo = ""

Set db = CurrentDb
Set rs = db.OpenRecordset(Sqlcall)
If rs.EOF Then
PrintLetters = False
Exit Function
End If
While Not rs.EOF
If rs!Check1 = True
Then strInfo = strInfo & rs!Text1
End If
If rs!Check2 = True
Then strInfo = strInfo & rs!Text2
End If
...etc, etc.
Count = Count + 1.3
rs.MoveNext
Wend

Its crude because it would be better if there was a loop there rather than a series of If...Then loops. Sorry but I'm pretty rushed right now so perhaps someone else may have a better solution but this way should work.

 

[khurley]

hey there.... thanks so much for your help. i finally got it working..... fo rthose who might find it useful here it is.

i set up all my reasons up top underneath the dim statements and put checkboxes on my form.

While Not rs.EOF
Renew1Count = Renew1Count + 1

If rs!Reason1 = True Then
strinfo = reasontext1
End If

If rs!Reason2 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext2
End If
If rs!Reason2 = True And strinfo = &quot;&quot; Then
strinfo = reasontext2
End If


If rs!Reason3 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext3
End If
If rs!Reason3 = True And strinfo = &quot;&quot; Then
strinfo = reasontext3
End If

If rs!Reason4 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext4
End If
If rs!Reason4 = True And strinfo = &quot;&quot; Then
strinfo = reasontext4
End If


If rs!Reason5 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext5
End If
If rs!Reason5 = True And strinfo = &quot;&quot; Then
strinfo = reasontext5
End If


If rs!Reason6 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext6
End If
If rs!Reason6 = True And strinfo = &quot;&quot; Then
strinfo = reasontext6
End If


If rs!reason7 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext7
End If
If rs!reason7 = True And strinfo = &quot;&quot; Then
strinfo = reasontext7
End If


If rs!reason8 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & reasontext8
End If
If rs!reason8 = True And strinfo = &quot;&quot; Then
strinfo = reasontext8
End If

If rs!reason9 = True And strinfo <> &quot;&quot; Then
strinfo = strinfo & vbNewLine & vbNewLine & rs!memoreason9
End If
If rs!reason9 = True And strinfo = &quot;&quot; Then
strinfo = rs!memoreason9
End If

rs.Edit
rs!totalreason = strinfo
rs.Update
strinfo = &quot;&quot;
rs.MoveNext
Wend
rs.MoveFirst
End If


Thanks Rookery for all your help!

 

[rookery]

No worries Kay. Glad you got it working.