vary report label caption using form & table info

[Guest]

I'm trying to set up a report template as a form letter that can be used in
multiple situations where the bulk of the letter is always going to be the
same/use same fields but the intro and 2nd paragraph will change depending
on the letter choice. In addition, if the letter references a single patient
the letter will be one pg long; if the TO party has multiple patients, the
patient detail will appear on a 2nd page. I have a form for the user to
select the variables of who is sending the letter and which letter they want
to use. Command button on the form opens the report "template. " Report
data source is a select query which pulls a data set filtered by the form
choices & a summary query. (Could change this to a make table.) The letter
text is in the report group header (group set by user/letter/provider);
visibilty of the 2 subreports is driven by summary query linked into the
select query.

The group header has several text boxes (date/addr/sig) and a label control.
The label caption has 3 placeholders. For each record in the datasource I
want to replace the placeholders based on the SQ/table fields for that
record, skipping if empty. So letter body can be personalized.

I've seen several posts referring to pulling data in from the form, and I
did get a function to work using form data to populate the report and replace
the label caption placeholders. But it only works for the record immediately
visible on the form. What if I want to print 50 letters?

Q1: Can I produce more than one letter at a time by adding a loop to the
outside of my replace code to work through the form recordset?
Q2: Do I have to use the form as my data source or can I reference the
SQ/table data set instead?

In my first test report I was trying this with a label in the detail
section. Works when source has only one record. Report Detail section
OnFormat called:

Function FillOutFromMap(LetterName)
Dim f As Form, D As Database, R As Recordset, c As Control, SQLtext As
String
Set f = Forms![frmletter]
Set D = DBEngine.Workspaces(0).Databases(0)
SQLtext = "Select * from [zsysLetterMap] where [ltrname] = '" &
LetterName & "';"
Set R = D.OpenRecordset(SQLtext)
Set c = Reports![ltrrefund]!lblIntro
Do Until R.EOF
c.Caption = Replace(c.Caption, R![Token], f(R![replacement]))
R.MoveNext
Loop
End Function

Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As Long
Dim EndToken As Long
Dim Front As String
Dim Back As String

If InStr(Block, Token) Then
StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)
If Len(ReplaceText) > 0 Then Replace = Front & ReplaceText & Back
Else
Replace = Front & Back
End If
End Function

If I have more records, the Replace moves on to record #2 and tries to add
the first field of rec #2 to the caption, but of course the placeholder no
longer exists. Is the problem that I havent set up my Do until loop &
R.Movenext properly? I want to run the Replace() several times for each
record.

Q3: Will I create new problems when I move this from the Detail section to
the GroupHeader section? The Detail section is now holding my subreport
choices.

 

[Guest]

since you have received no reply in a day; I will add my 2 cents - but it
does not answer your questions.

You are far along with a coded method. I would have simply used the
embedded Mail Merge function within Word. It can be based on the results of
an Access query and easily handle 50 letters non-stop.

It is solid and works well. One creates a Word template for the letter and
a template for the label/envelope....which would be driven off the same query.

It would not pragmatically work for both single page/single recipient and
also two page/multirecipients. You would probably have to do a run of these
separately using separate query and separate template.

One needs no coding to implement this at all......
--
NTC

I'm trying to set up a report template as a form letter that can be used in
multiple situations where the bulk of the letter is always going to be the
same/use same fields but the intro and 2nd paragraph will change depending
on the letter choice. In addition, if the letter references a single patient
the letter will be one pg long; if the TO party has multiple patients, the
patient detail will appear on a 2nd page. I have a form for the user to
select the variables of who is sending the letter and which letter they want
to use. Command button on the form opens the report "template. " Report
data source is a select query which pulls a data set filtered by the form
choices & a summary query. (Could change this to a make table.) The letter
text is in the report group header (group set by user/letter/provider);
visibilty of the 2 subreports is driven by summary query linked into the
select query.

The group header has several text boxes (date/addr/sig) and a label control.
The label caption has 3 placeholders. For each record in the datasource I
want to replace the placeholders based on the SQ/table fields for that
record, skipping if empty. So letter body can be personalized.

I've seen several posts referring to pulling data in from the form, and I
did get a function to work using form data to populate the report and replace
the label caption placeholders. But it only works for the record immediately
visible on the form. What if I want to print 50 letters?

Q1: Can I produce more than one letter at a time by adding a loop to the
outside of my replace code to work through the form recordset?
Q2: Do I have to use the form as my data source or can I reference the
SQ/table data set instead?

In my first test report I was trying this with a label in the detail
section. Works when source has only one record. Report Detail section
OnFormat called:

Function FillOutFromMap(LetterName)
Dim f As Form, D As Database, R As Recordset, c As Control, SQLtext As
String
Set f = Forms![frmletter]
Set D = DBEngine.Workspaces(0).Databases(0)
SQLtext = "Select * from [zsysLetterMap] where [ltrname] = '" &
LetterName & "';"
Set R = D.OpenRecordset(SQLtext)
Set c = Reports![ltrrefund]!lblIntro
Do Until R.EOF
c.Caption = Replace(c.Caption, R![Token], f(R![replacement]))
R.MoveNext
Loop
End Function

Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As Long
Dim EndToken As Long
Dim Front As String
Dim Back As String

If InStr(Block, Token) Then
StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)
If Len(ReplaceText) > 0 Then Replace = Front & ReplaceText & Back
Else
Replace = Front & Back
End If
End Function

If I have more records, the Replace moves on to record #2 and tries to add
the first field of rec #2 to the caption, but of course the placeholder no
longer exists. Is the problem that I havent set up my Do until loop &
R.Movenext properly? I want to run the Replace() several times for each
record.

Q3: Will I create new problems when I move this from the Detail section to
the GroupHeader section? The Detail section is now holding my subreport
choices.