Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Form letter to multiple recipients with different body of text

Discussion in 'Information Technology' started by vavroom@gmail.com, Jul 28, 2006.

  1. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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
    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
    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. Loading...


  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    <vavroom@gmail.com> wrote in message
    news:1153366397.282083.39010@m79g2000cwm.googlegroups.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
    > 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
    > 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?
    >
     
  4. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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
    of my face, I cant' see it :(

    Thanks for any further assistance you might be able to provide.
     
  5. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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
    http://www.mvps.org/access

    <vavroom@gmail.com> wrote in message
    news:1153439734.542137.17680@s13g2000cwa.googlegroups.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
    > of my face, I cant' see it :(
    >
    > Thanks for any further assistance you might be able to provide.
    >
     
  6. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    > 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.

    /me scratches head.
     
  7. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

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

    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.
     

Share This Page