Refer to field value within text in a text box on a report (1 Viewer)

DSLNVL · Jul 30, 2011

Does anyone know how to do this?

I have a table which holds the text for letters in its fields. I have a form where the user can edit this text and add 'letters' (records).

I then have a form where the user can select the 'letter' he/she wishes to use and on a report template have the letter created with all the necessary text and address details etc entered.

This all works fine, but I really want to be able to insert fields into the text of the letter without having to play around with text boxes in the report and so create a report for each letter and deny the user from adding his/her own letters and the ability to edit the text freely.

So for example in the table where the text for a letter is, it might be something like:

Thank you for you recent correspondence dated {MostRecentCorrespondence} we will .....

And when the report is created in the report's related text box, it would be the above but with the field value of Me.MostRecentCorrespondence.

I specifically want to use reports rather than word templates as the report will be used to create a pdf often and automatically added to emails afterwards etc and of course to allow the user to manage their letters and choose what fields of those allowed to be entered and where.

I hope the above makes sense, please do ask if not.

I was thinking that maybe some sort of find and replace might work but from what I read you can't do that with field values.
Then I was thinking along the lines of making the text in the table holding the letter text the control source for the report text box, but that didn't work though maybe I was doing it wrong.

Any ideas would be most welcome!

boblarson · Aug 1, 2011

I do it using a set of placeholders. So, I have a defined set of place holders in a table for text I want to insert like this:

ReplaceFields

{lt_name}|[lt_name],{PeriodYear}|[PeriodYear],{ExpectDate}|[FullExpectDate],{emailaddress}|MyEmailAddress@domain.com,{contactname}|John Smith,{contactphone}|(000) 000-0000

And then in the code I do this:

Code:

' if there are replace fields available then replace in the message
' before inserting to the SQL table
    If Not IsNull(rstLocation!ReplaceFields) Then
        ' does the first split for the comma delimited values
        varSplit = Split(rstLocation!ReplaceFields, ",", , vbTextCompare)
        For intCount = 0 To UBound(varSplit)
            ' splits the values again to get the value to replace
            ' and the value to replace with.
            varSplit2 = Split(varSplit(intCount), "|", , vbTextCompare)
            ' checks to see if the replacement has square brackets.
            ' if so, it uses it as a field and if not as a literal text string
            If Left(varSplit2(1), 1) = "[" Then
                ' performs the replacement
                strMessage = Replace(strMessage, varSplit2(0), Nz(.Fields(varSplit2(1)).Value), , , vbTextCompare)
            Else
                strMessage = Replace(strMessage, varSplit2(0), varSplit2(1), , , vbTextCompare)
            End If
        Next
    End If

And in the memo field in the table, I put this for the text:

sMessage field in the table

Code:

"<HTML>
<HEAD>
<TITLE>Letter for late tax return</TITLE>
</HEAD>
<BODY>
{lt_name}<BR><BR>
<P>
The <b>{PeriodYear}</b> Tax Return deadline for <b>{lt_name}</b> is as follows:<BR>
<b>Tax Returns are due</b> to Our Business by <b>{ExpectDate}</b>
</P>
<P> 
Our business respectfully requests that all required reports be delivered by the deadlines noted above.<br><br>
Financial penalties for late delivery of required reports are provided in the partnership agreement for every day the reports are delivered after the required deadline.
</P> 
Draft or final reports may be emailed to <b>{emailaddress}</b>.  For any questions, <b>about the {PeriodYear} Tax Returns</b>, please contact <b>{contactname}</b> at {contactphone}.
</BODY>
</HTML>"

Hope that helps.

DSLNVL · Aug 2, 2011

Thank you for your reply, good to know it is possible but I think I may be messing up the method.

I've created a table called ReplaceFields and titled a column ReplaceFieldTexts where I entered {ClientName}|[Text18]. I then went to the form opened a letter and in design view for the report, I then added a non-visible text box called Text18 which has the info I want displayed as it's control source (I thought I'd try this all with just one field until I'd got the method right). On the on load event I entered your code and I tried to customize (though wasn't quite sure exactly how but made a stab at it) and what I entered is below. When I go to open the report it says there's an error at the bit saying .Fields?

Dim StrText10 As String

StrText10 = Me.Text10

' if there are replace fields available then replace in the message
' before inserting to the SQL table
If Not IsNull(rstReplaceFields!ReplaceFieldTexts) Then
' does the first split for the comma delimited values
varSplit = Split(rstReplaceFields!ReplaceFieldTexts, ",", , vbTextCompare)
For intCount = 0 To UBound(varSplit)
' splits the values again to get the value to replace
' and the value to replace with.
varSplit2 = Split(varSplit(intCount), "|", , vbTextCompare)
' checks to see if the replacement has square brackets.
' if so, it uses it as a field and if not as a literal text string
If Left(varSplit2(1), 1) = "[" Then
' performs the replacement
StrText10 = Replace(StrText10, varSplit2(0), Nz(.Fields(varSplit2(1)).Value), , , vbTextCompare)
Else
StrText10 = Replace(StrText10, varSplit2(0), varSplit2(1), , , vbTextCompare)
End If
Next
End If

I imagine I'm not putting things in the right place, if you could point out where I'm going wrong that would be great!

(Text10 is the text box on the report that gets filled with the body text of the letter selected from the form which links to the table where all the different letter texts are held)

boblarson · Aug 3, 2011

You need to open a recordset first (rstReplaceFieelds). Also, I may not be able to check back in for a few days. I am swamped at work.

Code:

    Dim StrText10 As String
    Dim rstReplaceFields As DAO.Recordset
    Dim varSplit As Variant
    Dim varSplit2 As Variant
    Dim intCount As Integer
    Set rstReplaceFields = CurrentDb.OpenRecordset("Select * From ReplaceFields", dbOpenForwardOnly)
    StrText10 = Me.Text10
    With rstReplaceFields
        ' if there are replace fields available then replace in the message
        ' before inserting to the SQL table
        If .RecordCount > 0 Then
            ' does the first split for the comma delimited values
            varSplit = Split(!ReplaceFieldTexts, ",", , vbTextCompare)
            For intCount = 0 To UBound(varSplit)
                ' splits the values again to get the value to replace
                ' and the value to replace with.
                varSplit2 = Split(varSplit(intCount), "|", , vbTextCompare)
                ' checks to see if the replacement has square brackets.
                ' if so, it uses it as a field and if not as a literal text string
                If Left(varSplit2(1), 1) = "[" Then
                    ' performs the replacement
                    StrText10 = Replace(StrText10, varSplit2(0), Nz(.Fields(varSplit2(1)).Value), , , vbTextCompare)
                Else
                    StrText10 = Replace(StrText10, varSplit2(0), varSplit2(1), , , vbTextCompare)
                End If
            Next
        End If
    End With

DSLNVL · Aug 5, 2011

Many thanks for that,

I've put it in the on load section of the report but alas nothing happens - not even an error report for me to research. Any ideas as to what I might be doing wrong?

Actually just noticed I've put curly brackets instead of square brackets for the bit referring to the field I want inserted - now getting an error on this line though

StrText10 = Replace(StrText10, varSplit2(0), Nz(.Fields(varSplit2(1)).Value), , , vbTextCompare)

On the .Value bit when I hover the cursor it says Item not found in this collection but it's definitely there on the report showing the correct value???

boblarson · Aug 8, 2011

It can't go in the load event of the report. It would need to be in the On FORMAT event of the section where the items you want to put the text in for exist.

Not sure about the error. If you can post a copy of the database it might be easier to diagnose the problem.

DSLNVL · Aug 8, 2011

Hi Bob,

I tried it in the on format event for the detail section of the report, but the error (3265 I think it was) - the same one as before keeps coming up, the code seems to do the splitting no problem, it must be something I'm doing with text box I'm referring to on the report as it says can't find value in collection.

Anyhow I won't take up any more of your time as actually whilst I'm sure you would look at my database and fix whatever I've done wrong (and I really appreciate that you would do that and am very grateful) I have found a work around as a result of researching around this code and the replace function from your code. Not as easy to update subsequently but I'm so happy I have found a solution!

In the text box I'm doing the replacing in, I put this as the control source. [Forms]![SLTC]![Body] is where I'm getting the text for the report's main text box:

=Replace([Forms]![SLTC]![Body],"{OA}",[Text29])

And each time I want to add another replace possibility I use the previous replace function as the base for the next one like so:

=Replace(Replace([Forms]![SLTC]![Body],"{OA}",[Text29]),"{EM}",[Text30])

My final version is quite long!

Once again though thank you as I'd never have got to this solution without your solution and my problems in putting it in the right place!

Refer to field value within text in a text box on a report (1 Viewer)

DSLNVL

New member

boblarson

Smeghead

DSLNVL

New member

boblarson

Smeghead

DSLNVL

New member

boblarson

Smeghead

DSLNVL

New member

Similar threads

Users who are viewing this thread