A.D.Tejpal
adtp at touchtelindia.net
Thu Aug 4 14:57:38 CDT 2005
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>
To: Access Developers discussion and problem solving<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>>
To: Access Developers discussion and problem
solving<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>
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>
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