[AccessD] Memo field parsing

    Thanks Arthur! - for your kind words. So nice of you.

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

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: 'Access Developers discussion and problem solving' 
  Sent: Sunday, August 14, 2005 08:06
  Subject: RE: [AccessD] Memo field parsing


  What a nice and beautifully explained piece of code, A.D. My compliments!

  Arthur

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gregg
  Sent: August 10, 2005 3:33 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Memo field parsing

  Worked like a charm A.D.  thanks again!


    ----- Original Message ----- 
    From: A.D.Tejpal<mailto:adtp at touchtelindia.net> 
    To: Access Developers discussion and problem
  solving<mailto:accessd at databaseadvisors.com> 
    Sent: Thursday, August 04, 2005 2:57 PM
    Subject: Re: [AccessD] Memo field parsing


    Gregg,

        For sake of demonstration, let the source table named T_Memo consist
  of two fields, named ID (primary key - Autonumber) and Mem (memo type). 

        Create another table, named T_MemRef, with just one field named Ref
  (number type). Populate this table with just two records, with values 1 and
  2 respectively. Query Q_Memo as given below, should be used as record source
  for your report. It involves Cartesian join between tables T_Memo and
  T_MemRef. 

        In detail section of your report, drag Mem and Ref from the field
  list, so as to create bound controls named Mem and Ref. Make these controls
  hidden, set their CanGrow property to "No" and position the same
  un-obtrusively. Insert an unbound text box named TxtMemo. This is the text
  box that would actually display the contents of memo field. Set the width
  and fonts as desired. Set the CanGrow property of this control as well as
  the detail section as a whole, to "Yes".

        In VBA window, put the code given below, in Detail_Format event of the
  report. Make sure that the item "On Format" in Event tab of properties
  dialog box shows [Event Procedure] against it.

        Now, when you run the report, first three lines of the memo field will
  get displayed on first page and the balance portion on the next page.
  Similar pattern would get repeated for all records.
        
    Best wishes,
    A.D.Tejpal
    --------------

    Source Query for the report (Q_Memo)
    =====================================
    SELECT T_Memo.ID, T_MemRef.Ref, T_Memo.Mem
    FROM T_Memo, T_MemRef
    ORDER BY T_Memo.ID, T_MemRef.Ref;
    =====================================

    Code in Report's Module
    =====================================
    Private Sub Detail_Format(Cancel As Integer, _
                                            FormatCount As Integer)
        Dim Wd As Long, Rw As Single, Mgn As Long
        Dim LenMem As Long, Txt As String, Lft As Long
        Dim LastSpace As Long, MaxRowsFirstPart
        Dim Mpf As Single
        
        MaxRowsFirstPart = 3
        LenMem = Len(Mem)
        If LenMem > 0 Then
        Else
            Exit Sub
        End If
        
        With Me
            ' Set the scale mode to twips
            .ScaleMode = 1
            ' Set Font values for report to match those of memo field.
            .FontName = TxtMemo.FontName
            .FontSize = TxtMemo.FontSize
            If TxtMemo.FontBold <> 0 Then
                .FontBold = True
            Else
                .FontBold = False
            End If
        End With
        
        ' Allow for necessary margin within a text box
        ' (Between its border and the content)
        Mgn = 170     ' This value may need fine tuning for
                                ' best results
        ' Get effective width of control meant for
        ' displaying memo field
        Wd = TxtMemo.Width - Mgn
        ' Get the number of characters so as to make-up the
        ' required number of rows for first part of memo field.
        For Lft = 1 To LenMem
            Txt = Left(Mem, Lft)
            LastSpace = IIf(Right(Txt, 1) = " ", Lft, LastSpace)
            Rw = TextWidth(Txt) / Wd
            If Rw > MaxRowsFirstPart Then
                Lft = Lft - 1
                Exit For
            End If
        Next
        Debug.Print Lft; ", "; LastSpace
        
        ' Get Multiplying factor for acceptable max location
        ' of last space.
        Mpf = (0.8 + (MaxRowsFirstPart - 1)) / MaxRowsFirstPart
        
        ' Shorten the first part to nearest space
        ' (if within 20 % of the end of line)
        If LastSpace > Mpf * Lft And LastSpace < Lft Then
            Lft = LastSpace
        End If
        
        ' Populate the unbound text box with appropriate
        ' part of memo field.
        If Ref = 1 Then
            TxtMemo = Left(Mem, Lft)
            ' Force New Page - Afer section, only if memo
            ' field contents not yet finished
            If LenMem > Lft Then
                Detail.ForceNewPage = 2
            Else
                Detail.ForceNewPage = 0   ' No break
            End If
        Else
            TxtMemo = Mid(Mem, Lft + 1)
            ' Force New Page - Afer section
            Detail.ForceNewPage = 2
        End If
    End Sub
    =====================================