[AccessD] storing last item on the page

John,

    That is exactly the point. The requirement in this particular case was "point of time value as per flow of report execution". Hence the need for ensuring a collection of the then value held by the control and not the control object itself.

    Susan's objective was to store page-wise contents of TxtFooterLast (located in page footer) in a collection. With her original code, each element of this collection happened to become a pointer to the text box TxtFooterLast itself, even though Debug.Print statement did show the correct point of time value (as Value happens to be the default property of a text box control).

    For example, on a three page report, where TxtFooterLast displays "A", "B", "C" respectively on the three pages, the actual collection became:

    TxtFooterLast, TxtFooterLast, TxtFooterLast
    (instead of intended   "A", "B", "C"  as per Debug.Print statement)

Best wishes,
A.D.Tejpal
------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, November 09, 2007 17:46
  Subject: Re: [AccessD] storing last item on the page


  A.D.

  An array can store a control just as easily.  And yes, you need to be aware
  of what you are storing, since if you store a control a number of things can
  occur that can be confusing.  For example, if you store a control, the value
  of the control can change between when you stored that control and when you
  use it, and in fact can continue to change as long as the collection is
  stored.  OTOH, if you store the control's VALUE then that value is captured
  in time, and while the control may continue to change, it's value back when
  it was stored will still be available.  It really depends on what you need
  to do, but you definitely need to keep this in mind.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com 
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
  Sent: Friday, November 09, 2007 2:49 AM
  To: Access Developers discussion and problem solving
  Cc: A.D.TEJPAL
  Subject: Re: [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.