Form letter to multiple recipients with different body of text

#1 (**permalink**) 28-Jul-2006, 08:38 AM

So, for my next roadbump...

I'm having to send form letters every day, to multiple recipients.
Recipients are selected based on a date/daterange on a record. So far
so good.

I created a report which works as I want and displays things properly.
So far so good.

Where the problem begins, however, is that the letter should have a
different body of text, depending on the status of the recipient's
application (i.e. different letter if their application is approved, or
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13588-form-letter-multiple-recipients-different-body.html
declined, and if it's declined, different again depending on the reason
the application is declined).

I *could* create as many reports as I need letters, but this
complicates things in many ways. First, if you need to change the text
of the form letters (which happens periodically), you have to edit the
various reports. Then, you have to run each report based on the
date/daterange.

What I was thinking of doing is putting the letterbody in a table,
associated with an application status/declined explanation. Then when
the report is run, have it select the right text from the lettertext
table depending on the status, so the user can enter a date/daterange
on a form and click a button, and each record on the report would show
with the appropriate text/details.

I was able to do something that kinda worked, using a mixture of iif
and dlookup directly in a textfield in the report. But I'm concerned
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
about performance, and it seems rather unwieldy to do several nested
iif and dlookups.

I doing an if/else series of statements in code, and assigning the
value of the txtbox based on the result of these statements, but it
tells me I can't assign a value to the txtbox.

I'm not sure what to do next. Any idea?

#2 (**permalink**) 28-Jul-2006, 08:38 AM

Consider using a recordset instead of DLookup:

Function FastLookup(strFieldName As String, _
strTableName As String, _
strWhere As String) As Variant
'Arvin Meyer 4/9/1997

Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

strSQL = "SELECT " & strFieldName & " FROM " & _
strTableName & " WHERE " & strWhere & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount <> 0 Then
FastLookup = rst(strFieldName)
Else
FastLookup = Null
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

wrote in message
news:1153366397.282083.39010@m79g2000cwm.googlegro ups.com...
> So, for my next roadbump...
>
> I'm having to send form letters every day, to multiple recipients.
> Recipients are selected based on a date/daterange on a record. So far
> so good.
>
> I created a report which works as I want and displays things properly.
> So far so good.
>
> Where the problem begins, however, is that the letter should have a
> different body of text, depending on the status of the recipient's
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
> application (i.e. different letter if their application is approved, or
> declined, and if it's declined, different again depending on the reason
> the application is declined).
>
> I *could* create as many reports as I need letters, but this
> complicates things in many ways. First, if you need to change the text
> of the form letters (which happens periodically), you have to edit the
> various reports. Then, you have to run each report based on the
> date/daterange.
>
> What I was thinking of doing is putting the letterbody in a table,
> associated with an application status/declined explanation. Then when
> the report is run, have it select the right text from the lettertext
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
> table depending on the status, so the user can enter a date/daterange
> on a form and click a button, and each record on the report would show
> with the appropriate text/details.
>
> I was able to do something that kinda worked, using a mixture of iif
> and dlookup directly in a textfield in the report. But I'm concerned
> about performance, and it seems rather unwieldy to do several nested
> iif and dlookups.
>
> I doing an if/else series of statements in code, and assigning the
> value of the txtbox based on the result of these statements, but it
> tells me I can't assign a value to the txtbox.
>
> I'm not sure what to do next. Any idea?
>

#3 (**permalink**) 28-Jul-2006, 08:38 AM

Thanks Arvin,

I would have considered it if I had known (or knew) how do to so. This
is a bit beyond my meagre abilities.

I'm having a problem using your suggested function.

I've pasted it in a new module. Now, from my reading of this, I should
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
pass the strFieldName, strTableName and strWhere when calling the
function, as such:

FastLookup ("fldLetterText", "tblLetterText", "fldLetterTextID=2")

Which I called like this:
me.txtLetterText = FastLookup ("fldLetterText", "tblLetterText",
"fldLetterTextID=2")

This gives me a compile error expected variable or procedure, not
module.

So I figure, ok, the function shouldn't live in a module. I trashed
the module, put the function in the report's window.

This then gave me a compile error user-defined type not defined, and
highlights "dim rst as dao.recordset".

I suspect I'm missing something obvious, but as the nose in the middle
of my face, I cant' see it

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588

Thanks for any further assistance you might be able to provide.

#4 (**permalink**) 28-Jul-2006, 08:38 AM

I'm not sure what you are doing wrong. The code goes in a module (I use a
Standard module because I call it from everywhere). I just tested it again,
I tested the code directly from the newsgroup posting, to be sure I hadn't
missed something, and it works fine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
http://www.mvps.org/access

wrote in message
news:1153439734.542137.17680@s13g2000cwa.googlegro ups.com...
> Thanks Arvin,
>
> I would have considered it if I had known (or knew) how do to so. This
> is a bit beyond my meagre abilities.
>
> I'm having a problem using your suggested function.
>
> I've pasted it in a new module. Now, from my reading of this, I should
> pass the strFieldName, strTableName and strWhere when calling the
> function, as such:
>
> FastLookup ("fldLetterText", "tblLetterText", "fldLetterTextID=2")
>
> Which I called like this:
> me.txtLetterText = FastLookup ("fldLetterText", "tblLetterText",
> "fldLetterTextID=2")
>
> This gives me a compile error expected variable or procedure, not
> module.
>
> So I figure, ok, the function shouldn't live in a module. I trashed
> the module, put the function in the report's window.
>
> This then gave me a compile error user-defined type not defined, and
> highlights "dim rst as dao.recordset".
>
> I suspect I'm missing something obvious, but as the nose in the middle
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
> of my face, I cant' see it

>
> Thanks for any further assistance you might be able to provide.
>

#5 (**permalink**) 28-Jul-2006, 08:40 AM

> I'm not sure what you are doing wrong. The code goes in a module (I use a
> Standard module because I call it from everywhere). I just tested it again,
> I tested the code directly from the newsgroup posting, to be sure I hadn't
> missed something, and it works fine.

I don't know either. Hmmm.

I'll try it in a cleaned up/simplified DB.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588

/me scratches head.

#6 (**permalink**) 28-Jul-2006, 08:40 AM

Got it! And it works. Thanks Arvin, this is brilliant.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13588
I dug in some, and finally figured out that for some reason the MS DAO
3.6 Object Library wasnt' "ticked" in the references... Did that, and
now it works like a charm. Still fiddling to do to create the WHERE
statement, but that shouldn't be that complicated.

Cheers, I *much* appreciate your assistance.