How do I change the body of an access report based on user selection?

[Rob]

O.k. Here's the deal. My office is swampped with memos going out and
most of them are standard except for the body of each memo. Im talking
over 100 memos that are almost identical excpet for the body. I have
set up the database so that the recipent title and address etc change
based on the users selection. For example if the user selects
recipient A from a list then A's address and information are
automatically filled in on the memo. My question is how do I do the
same thing with the body of the memo based on the type the user
selects. The memos are so similar (except for the body) ideally Id
like to have one "memo report" in access which changes the body based
on the users selction of memo type. I think this is to big to do using
a table and Id like a little more freedom to change text so I need to
do it in VB but dont really know how. I started a module and was
planning on using Select Case and just putting the text there but its
been a long time since Ive used that method. Any thoughts? Thanks in
advance!

Rob

 

[KML]

Rob,

In your current setup, are they using a form to select the recipients?
Is this how they will select the type of memo?

What format do they want the memos to be output in?
Word?
Access Report?

 

[Rob]

Im using a form right now so they can select the recipient. They have
a drop down box to pick the office which focuses the second drop down
to show only the people in that office. They will be selecting the
type of memo from a form as well. I would like the output to an access
report as opposed to doing a mail merge.

 

[John Vinson]

O.k. Here's the deal. My office is swampped with memos going out and
most of them are standard except for the body of each memo. Im talking
over 100 memos that are almost identical excpet for the body. I have
set up the database so that the recipent title and address etc change
based on the users selection. For example if the user selects
recipient A from a list then A's address and information are
automatically filled in on the memo. My question is how do I do the
same thing with the body of the memo based on the type the user
selects. The memos are so similar (except for the body) ideally Id
like to have one "memo report" in access which changes the body based
on the users selction of memo type. I think this is to big to do using
a table and Id like a little more freedom to change text so I need to
do it in VB but dont really know how. I started a module and was
planning on using Select Case and just putting the text there but its
been a long time since Ive used that method. Any thoughts? Thanks in
advance!

Rob

Would it make sense to have the body of the memo in a Memo field in
the table? Or will the user be typing the text of the memo (or part of
it perhaps) separately for each memo sent? If the latter, you could
launch the Report from a Form, and have a textbox on the Report
directly reference an unbound textbox on the Form where the user can
type the body.

John W. Vinson[MVP]

 

[Rob]

I actually tried that sucessfully but chose to type the code in VB
because I need to have calculated fields within the body. I actually
have it working in VB now I just need to know how to replace some of
the standard text with calculated fields within VB. I have the field
named in the query but Im not sure how to reference that field in VB.
The users (ideally) will not have to type anything for the body. They
will just select the office, recipient, and subject. The rest will be
filled in automatically.

 

[John Vinson]

I actually tried that sucessfully but chose to type the code in VB
because I need to have calculated fields within the body. I actually
have it working in VB now I just need to know how to replace some of
the standard text with calculated fields within VB. I have the field
named in the query but Im not sure how to reference that field in VB.
The users (ideally) will not have to type anything for the body. They
will just select the office, recipient, and subject. The rest will be
filled in automatically.

I'd suggest using string variable; extract the boilerplate text from
the form control or from a Recordset, and use the Replace() function
to replace "placeholders". I have no clue what these memos or
calculations look like so I can't give precise examples, but if (say)
you have a boilerplate text with

....The interest rate for this loan will be <IntRate>, for a monthly
payment of $<Payment>...

you could use statements like

strMemoText = Replace(strMemoText, <IntRate>, Format(curRate,
Percent))

and so on.

John W. Vinson[MVP]