Get a named Field Value from Report via function call in Report Control source.

[lameid]

I want to pass a report object to a function, and get a field I parse out of a string and pull it's value as if it is a value in the record source for the current record... Basically substituting a value for a field value...

I am passing report as .... ByRef rpt As Access.Report
I can't figure out how to find the field value for say field "x" in the recordsource... This seemed intuitive but clearly I am not getting to the recourdsource... rpt.Fields("x").Value

Oh I see now.... I am calling function in control source... I need to pass the current record somehow instead to parse it out? My brain hurts on this one.

The below function is what I am trying to call from my control source... I am just trying to avoid having to manually apply nested replaces in the report controls.
It represents a maintenance issue. Any clever ideas are appreciated. For reasons that are too long to go into, replacing embedded field names is the correct approach.

Function fnReplaceFields(ByRef rpt As Access.Report, ByRef Source As String) As String
  'Expected Call in control source: fnReplaceFields([Report], <FieldWhoseValueWIllBeReplaced>)
  Dim strValues() As String
  Dim i As Long
  Dim lngUpperArray As Long
  Dim strReturn As String
  Dim strField As String
  Dim lngPos As Long
  
  strValues = Split(Source, "[")

  lngUpperArray = UBound(strValues)
  
  strReturn = Source
  
  For i = 0 To lngUpperArray Step 1
    
    lngPos = InStr(1, strValues(i), "]")
    If lngPos > 0 Then
      strField = Left(strValues(i), lngPos - 1)
      If InStr(1, strReturn, "[" & strField & "]") > 0 Then
        strReturn = Replace(strReturn, "[" & strField & "]", Nz(rpt.Fields(strField).Value, "")) '[red]NZ Paremeter Failes[/red]
      End If
    End If
  Next i
  fnReplaceFields = strReturn
End Function

 

[dhookom]

Have you ever tried adding a breakpoint and/or debug.print? Do you know what the value of strField is?

What is a typical value of Source? You stated "FieldWhoseValueWIllBeReplaced" which points to a single value but you use Split to make an array which doesn't make sense to me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

The issue is the below fails. It is not available.

I think fundamentally it is not... all the columns would have to be in bound report controls and the value picked up from the control value.

That leaves parsing the record source for replacements in the record source SQL... Doable but a total hassle.

rpt.Fields(strField).Value

 

[dhookom]

I don't believe a report has a .Fields property. Does your code compile?

Have you ever tried a simple code like:

Private Sub Report_Open(Cancel As Integer)
    Debug.Print Me.Fields(1).Name
End Sub

When I try this, I get an error message.

You have fed us a lot of code without suggesting what you are attempting to accomplish. This looks a bit like a mail merge type of report.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016