[AccessD] storing last item on the page

    You have not given any reason as to why you wish to avoid array based solution. Apparently, you are keen to implement collection based solution as an alternative.

    In your second post, describing the unsuccessful attempt to work out collection based solution, you stated  "I'm clueless -- I have no idea what's happening here." It is observed that the following factors are contributing to the problem:

    1 - You have landed into an interesting pitfall typical of collections. Whenever a collection's Add method is used, you have to be careful as to what exactly is being added. If you use the syntax  col.Add Me.MyControl, it becomes a collection of control objects. For making it a collection of control contents, you have to use Value property of the control.

    2 - Page Footer is the appropriate place to grab a value from last record of detail section and add it to the collection. You are wrongly using Page Header for this purpose.

    3 - You are using Page Footer to assign a value (from collection) to txtHeaderLast. In view of the nature of forward time flow during report execution, the effect of such assignment materializes only on the next page. This defeats the very purpose of building a collection of last values in forced first pass of formatting. The proper place for making such an assignment is Page Header (preferably its print event, as by then, building up of collection during prior formatting pass, is complete).

    Sample code in report's module, as given below, demonstrates collection based solution. All the four controls (TxtHeaderFirst, TxtHeaderLast, TxtFooterFirst, TxtFooterLast) are unbound. In the sample code, "Title" is the name of control in detail section whose first and last values are required to be displayed in page header as well as footer. You can substitute the name of actual control in your report, suitably.

A.D.Tejpal
------------

Code in report's module
'==================================
' Declarations section
Private col As New Collection
'---------------------------------------------------

Private Sub PageFooterSection_Format(Cancel _
                    As Integer, FormatCount As Integer)
    
    Me.TxtFooterLast = Me.Title
    
    If Me.Pages = 0 Then
        col.Add Me.Title.Value, CStr(Me.Page)   ' (A)
    End If
    
    ' Caution - There is a potential pitfall here.
    '                In statement (A), while adding items to
    '                collection, Value property of the control
    '                in question MUST be used. Otherwise,
    '                it will become a collection of control
    '                objects (not the contents as intended),
    '                leading to weird results.
End Sub
'---------------------------------------------------

Private Sub PageHeaderSection_Format(Cancel _
                    As Integer, FormatCount As Integer)
    Me.TxtHeaderFirst = Me.Title
    Me.TxtFooterFirst = Me.Title
End Sub
'---------------------------------------------------

Private Sub PageHeaderSection_Print(Cancel _
                        As Integer, PrintCount As Integer)
    Me.TxtHeaderLast = col(CStr(Me.Page))
End Sub
'---------------------------------------------------

Private Sub Report_Close()
    Set col = Nothing
End Sub
'==================================

  ----- Original Message ----- 
  From: Susan Harkins 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, November 07, 2007 18:11
  Subject: Re: [AccessD] storing last item on the page


  I'm not convinced an array is necessary -- I've seen that solution. Have you 
  seen the collection solution I tried and posted late last night?

  Susan H.