Report, replace by Recordsource Field name value...

[lameid]

I am trying to make a reporting tool where the users can easily replace with a field value by putting square brackets around a field name to be replaced.

So say I had a table, x

Field name: Salutation
Datum: Hello World

Now lets say my Record source included x.Salutation.

Lets say another Field, y had the rich text value "<div>Sincerely, </div><div>[Salutation]</div>"

I would like to make my control source...

=fnReplace

And end up with the returned value
"<div>Sincerely, </div><div>Hello World</div>"

I had reservations about finding the value but decided to give it a go with a function in the report but I did not see a clever solution...
Is there anything that will work without tanking the performance? Or do I just need to embed all the replaces in the control source (an interesting alternative coding task)?

Public Function fnReplace(FixIt As String) As String
  
  Dim strField As String
  Dim lngStartPos As Long
  
  
  While InStr(1, FixIt, "[")
    
    lngStartPos = InStr(1, FixIt, "[")
        
    strField = Mid(FixIt, lngStartPos)
    strField = Left(strField, InStr(1, strField, "]"))
    
    FixIt = Replace(FixIt, strField, Me.Controls(strField).Value) 'This doesn't work as there is no control but what is the right collection or method...
    
  Wend
  
  fnReplace = FixIt
End Function

 

[Darrylles]

If I understand you correctly:

You can't pull the value to be edited from the report, you must first edit the value, then pass it to the report.
So, what is the report data source?
Intercept the data source, and amend that.

ATB,

Darrylle

 

[lameid]

I see your point... But the replacements are potentially on a per record basis. It happens that the report is run on a per record basis so not dismissing out of hand but it is run iteratively possibly thousands of times... The overhead seems like it would be enormous as you would have the same issue. Basically you would have to find all the cases of things with replacements, and insert those into the record source sql... interesting... We'll call that plan C as reorganizing the structure of the system to do that without excessive overhead is non-trivial (the joys of design flaws that predate your hire date).

Plan A is just manually monkeying with the report and inserting the replacements in the control source... Looking for a good Plan B similar to the path I started to go down.

 

[lameid]

Value will contain bracket delimited field names that need replaced by values of those fields in the recordsource.

The split handles breaking on the square brackets for determining the embedded replacements.

I am sucessfully determining those embedded fieldnames.

What is giving me trouble is accessing the named field value that is in the report's recordsource.

Purely a timing and /or syntax issue. I don't think what I want is available unless every field is in a bound countrol.