Capture Values Of Unbound Text Box And Display In Footer

[haus14580]

I have a report in which the Detail section is NOT even visible. The report displays a footer section with various sums, counts, etc. grouped by each manager.

I have an unbound text box n the detail section that uses an IIF statement and returns the "Issue Number" of that record if the criteria are met. This text box works fine.

What I want to do is capture in a string variable all of the values this text box returns for this manager, insert a comma between values,

str= str + ", " + [txtIssueNum]

and put the resulting string in a text box in the footer for each manager. The resulting string would be a list of the Issues this manage has due within 45 days (from the IIF text box in the detail), such as;

Due within 45 days for Manager XXXXXX: 3
P182945, N1048576, X12345

I can get the count, but I can't seen to capture the string of the Issue Numbers. In VBA the str variable gets set to "" with each record.

Ideas welcome.

 

[CJ_London]

don't think you can contatenate values from controls in that way, suggest look at the concatrelated function here and use in your footer control

http://allenbrowne.com/func-concat.html

 

[haus14580]

Thanks. Yea, I wasn't getting anywhere so I ended up solving this by writing a query that returns all appropriate records, then creating a report based on that query, then inserting that report as a sub-report in the group footer for each manager, and linking the sub-report on the Manager field. Works OK, but I would have thought there would be a simpler solution using VBA.

 

[CJ_London]

but I would have thought there would be a simpler solution using VBA.

there is - the concatrelated function I suggested

 

[pbaldy]

Piqued my curiosity. It can be done with code on the report using:

a report level string variable
code in the group header clearing it
code in the detail section building it
code in the group footer setting a textbox to it

My simplistic but successful test code:

Option Compare Database
Option Explicit

Dim strInvoices As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  strInvoices = strInvoices & Me.AR_key & ","
End Sub

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
  Me.Text65 = strInvoices
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  strInvoices = ""
End Sub