[AccessD] Memo field parsing

Gregg greggs at msn.com
Wed Aug 10 14:32:30 CDT 2005


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<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<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<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.co<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.co<mailto:cfoust at infostatsystems.com<mailto:cfoust at infostatsystems.co>>
      m>> 
          To: Access Developers discussion and problem
       
      solving<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvis<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvis<mailto:accessd at databaseadvisors.com<mailto:accessd at databaseadvis<mailto:accessd at databaseadvisors.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/>



More information about the AccessD mailing list