[AccessD] Memo field parsing

Arthur Fuller artful at rogers.com
Sat Aug 13 21:36:54 CDT 2005


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
  =====================================

    ----- Original Message ----- 
    From: Gregg 
    To: Access Developers discussion and problem solving 
    Cc: Robert Penn 
    Sent: Thursday, August 04, 2005 04:19
    Subject: Re: [AccessD] Memo field parsing


    Thanks Charlotte!  I give it a try.

      ----- Original Message ----- 
      From: Charlotte
Foust<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.com>> 
      To: Access Developers discussion and problem
solving<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.
com>> 
      Sent: Wednesday, August 03, 2005 5:33 PM
      Subject: RE: [AccessD] Memo field parsing

      No, get the textwidth of the control and see if the textwidth of the
text is greater than that.  Handle the situation where it is not and
everything fits in the first control.  Otherwise get the text that fits
within the control textwidth and use instrRev to find the last space in that
substring.  Break it there.  

    At that point, you also know the index of the starting point for the
rest of the string, so all you need to do for the next page is use the
Mid(text, index) to put the rest of it into the second page.

    Charlotte Foust

      -----Original Message-----
      From: Gregg [mailto:greggs at msn.com] 
      Sent: Wednesday, August 03, 2005 11:34 AM
      To: Access Developers discussion and problem solving
      Subject: Re: [AccessD] Memo field parsing


      So, you're saying I need to loop through all the characters until I
find
      the last whole word that will fit on the first line, the same for the
      second etc. until I determine what text will fit in the first page
text
      field and use what's left for the 2nd page?
        ----- Original Message ----- 
        From: Charlotte
Foust<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.com<ma
ilto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.com>>> 
        To: Access Developers discussion and problem
 
solving<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.
com<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>
>> 
        Sent: Wednesday, August 03, 2005 11:02 AM
        Subject: RE: [AccessD] Memo field parsing


        You can't do it by simply counting characters anyhow.  You have to
use
        the TextWidth property, which can be expressed as
        reportname.TextWidth(stringvariable) and compare that to the
textwidth
        of the control ( reportname.TextWidth(controlobject) or to the
maximum
        length you want to display.  You'll also want to allow a padding
      factor
        to avoid overrunning the  borders of the control and be sure and
check
        for carriage returns because they confuse the issue.

        If you want to display 3 "lines", you'll have to break the memo
field
        into the first three chunks that will fit into the textwidth of the
        textbox you're using to display the text on that page.

        Charlotte Foust


        -----Original Message-----
        From: Gregg [mailto:greggs at msn.com] 
        Sent: Wednesday, August 03, 2005 8:43 AM
        To: Access Developers discussion and problem solving
        Subject: Re: [AccessD] Memo field parsing


        Charlotte

        Physical space I think because I don't know how to get from one to
the
        other.  The number of characters is going to be dependant on the how
        many characters are lost to word wrapping isn't it?  Is there a way
to
        calculate the number of characters that will appear in the first
three
        lines?  If I knew that, I could do it.
          ----- Original Message ----- 
          From: Charlotte
 
Foust<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.co<mai
lto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.co<mailto:cfous
t at infostatsystems.com<mailto:cfoust at infostatsystems.co<mailto:cfoust at infosta
tsystems.com<mailto:cfoust at infostatsystems.co>>
      m>> 
          To: Access Developers discussion and problem
       
 
solving<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvis<mai
lto:accessd at databaseadvisors.com<mailto:accessd at databaseadvis<mailto:accessd
@databaseadvisors.com<mailto:accessd at databaseadvis<mailto:accessd at databasead
visors.com<mailto:accessd at databaseadvis>>
      ors.com>> 
          Sent: Wednesday, August 03, 2005 10:28 AM
          Subject: RE: [AccessD] Memo field parsing


          Define "first three lines".   Is that a particular number of
        characters
          or a physical space?

          Charlotte Foust


          -----Original Message-----
          From: Gregg [mailto:greggs at msn.com] 
          Sent: Wednesday, August 03, 2005 7:32 AM
          To: Access Developers discussion and problem solving
          Subject: [AccessD] Memo field parsing

          I would like to parse a memo field in a A97 report such that the
first three lines is on the first page and whatever is left is on the next.
          Any ideas?

          Gregg Steinbrenner
          -- 
          AccessD mailing list
  -- 
  AccessD mailing list
  AccessD at databaseadvisors.com<mailto:AccessD at databaseadvisors.com>
 
http://databaseadvisors.com/mailman/listinfo/accessd<http://databaseadvisors
.com/mailman/listinfo/accessd>
  Website: http://www.databaseadvisors.com<http://www.databaseadvisors.com/>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list