×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

CODE

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, "")) 'NZ Paremeter Failes
      End If
    End If
  Next i
  fnReplaceFields = strReturn
End Function 

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

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

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

(OP)
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.

CODE

rpt.Fields(strField).Value 

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

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

Have you ever tried a simple code like:

CODE --> vba

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close