Memo field
-
Hi
I need to limit a memo field to a certain number of lines, currently 15.
Has anybody any ideas how I might do this. That is, when the 15th line has been used, the memo field will not allow an entry in line 16.
Thanks
Paul
-
You'll have to loop through the text and count how many vbCrlF are in it. You'll have to code this in the keypress or something like that... and start counting if the key is return... but that would not always work because of the paste...I think it would be simpler to allow for only a certain amount of characters... say 750 (50 per line) and only check for the length of the field.
-
Hi Remi
Thanks for the reply. The problem is that the number of characters can vary from 1 per line to none to a paragraph of continuous text.
Paul
-
then you'll have to scan the text everytime the user types a character and count how many lines there are... but that can cause problems if it's a realy large memo and that the pc is slow.
-
Depending on exactly what and why you want to do this, how about designing your input form with 15 textboxes stacked vertically, edge to edge, picking up the keypress event and moving to the next when the key is a CR?
(I'm a VB guy, not Access, so apologies if this isn't feasible in Access!)
-
Hi
Yep I'm also a VB-er but started life in Access... so yes you can capture keypress events.
Paul, just out of interest, can you explain why you need limit to 15 lines but aren't bothered about how many characters in each..
Sam
-
Alternatively, you could create a child table with a foreign key id field and a single text field, with 15 records for a given id representing the 15 lines you want to allow. This might make it easier to allow for the removal of a specific line. VB behind the form could determine whether or not additional records could be added.
-
That's why I love this site... so many simple solutions that make your life easier. Thanx for everyone's input (even if I didn't start the thread).
-
Hi
I've just managed to get back on to this, so apologies for not replying immediately. And I couldn't agree more about how helpful people are on this site.
I need to limit the number of lines to fit currently chosen paper sizes for printed reports (these may change in the future). A great many records have already been written, so some of the good solutions given would be difficult to use. Of course, as you realise, specifications always change after a database is in use.
The memo field could contain something like:
Target 1
Paragraph of text.....................
Comment1
comment2
Paragraph of text.
_______________
I can't quite get my head around the way the code would look to limit to 15 lines.
Thanks to everybody
Paul
-
OK how about this then -
Write some code in vb that structures the text the way you want, then populates a temporary table with the results. Then report from that table. (I'm assuming you're using Access reports - basically I think the solution to this depends on the reporting tool you're using)
pg
-
Hi and thanks.
Yes I am using Access 2000. The report consists of many fields, but the only one that needs to be limited is the memo. I'm not quite sure how copying to another table may help.
Paul
-
Hi Paul
I was looking at your reply about dealing with the existing data - in order to format this onto a report, I meant you could get the block of data from the original table, process it in code into the required number of lines (however 'lines' are defined) and store the results of this processing in another table, from where the report could easily pull it. In other words, you're using VB to do the string manipulation - which VB is good at - rather than trying to do it in SQL, or within the report itself. As far as inputing new data, personally I'd go for the stacked text boxes, maybe with the child table suggested byPeter Laube.
HTH
pg
-
An alternative, if this is just for reporting purposes, is, in Access, in the detail_format event, to change the font size depending upon how much data is in the memo. In the particular circumstance I had to do this, I tried both counting crlfs and characters, and found that if it was long, it was really long, so I split the data into two columns, and put half in each, but only when the data was more than 300 characters long.
Sample from working report, and DATA_SIZE is a field in the query that does a len(data_field), "Data" is the memo field, and the "Data" is written to the text field "txtDescription" and if necessary, "txtDescription2" (the "Data" field is on the report sized very small and not visible):
'----code begins below this line from an Access 2002 report on a SQL 2000 database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim sTemp As String
Dim iPos As Long
Dim crCount As Integer
Dim rpt As Report
On Error GoTo NoData
ResizeData:
Set rpt = Me
Select Case Me.DATA_SIZE
Case Is = 0
rpt.Controls("txtDescription") = ""
Exit Sub
Case Is < 300
rpt.Controls("txtDescription").fontsize = 10
rpt.Controls("txtDescription2").fontsize = 10
Case Is <= 1000
rpt.Controls("txtDescription").fontsize = 8
rpt.Controls("txtDescription2").fontsize = 8
Case Else
rpt.Controls("txtDescription").fontsize = 7
rpt.Controls("txtDescription2").fontsize = 7
End Select
sTemp = Nz(Left(CStr(Me.[Data]), CInt(Me.DATA_SIZE)), "")
' Look for first CR
iPos = InStr(sTemp, Chr(10))
' If found, replace with CrLf
If iPos <> 0 Then
sTemp = Left(sTemp, iPos - 1) & vbCrLf & Mid(sTemp, iPos + 1)
End If
crCount = 0
' Loop thru description and replace all CRs with CrLfs
Do While iPos <> 0
iPos = InStr(iPos + 2, sTemp, Chr(10))
If iPos <> 0 Then
crCount = crCount + 1
sTemp = Left(sTemp, iPos - 1) & vbCrLf & Mid(sTemp, iPos + 1)
End If
Loop
If Len(sTemp) > 6000 Then
sTemp = Left(sTemp, 6000 - 16) & " < Cont'd >..."
End If
If Len(sTemp) > 6000 Or crCount > 20 Then
'find nearest space
Dim Nearest As Integer
Nearest = InStr(CInt(Len(sTemp) / 2), sTemp, " ")
If Nearest > 2800 Then
Nearest = InStr(2800, sTemp, " ")
End If
txtDescription = Left(sTemp, Nearest)
txtDescription2 = Right(sTemp, Len(sTemp) - Len(txtDescription))
txtDescription.Width = Me.Width / 2
txtDescription.Left = 0
txtDescription2.Width = Me.Width / 2
txtDescription2.Left = Me.txtDescription.Width
txtDescription.Visible = True
txtDescription2.Visible = True
If crCount > 40 Or Len(txtDescription) > 2000 Then
PageBreak200.Visible = True
lblCont.Visible = True
Else
PageBreak200.Visible = False
lblCont.Visible = False
End If
Else
PageBreak200.Visible = False
lblCont.Visible = False
' Set Description
txtDescription = sTemp
txtDescription.Width = Me.Width
txtDescription2 = ""
End If
Exit Sub
NoData:
If Err.Number = 94 Then Exit Sub
End Sub
-
Seems to me that none of the above approaches will work properly. I think you need to investigate the DrawTextEx API function, which is not a trivial job. If you pass this function your text rectangle specs, it can return the the new text height. Please see the documentation at the MSDN site. See the notes on DT_CALCRECT; Here is a little bit to get you started from the freeware program API Guide 3.7:
Declare Function DrawTextEx Lib "user32" Alias "DrawTextExA" (ByVal hDC As Long, ByVal lpsz As String, ByVal n As Long, lpRect As RECT, ByVal un As Long, lpDrawTextParams As DRAWTEXTPARAMS) As Long
· hdc
Identifies the device context to draw in.
· lpchText
Points to the string to draw. The string must be null-terminated if the cchText parameter is -1.
· cchText
Specifies the length, in characters, of the string specified by the lpchText parameter. If the string is null-terminated, this parameter can be -1 to calculate the length.
· lprc
Points to a RECT structure that contains the rectangle, in logical coordinates, in which the text is to be formatted.
· dwDTFormat
Specifies formatting options. This parameter can be one or more of these values:
DT_BOTTOM
Justifies the text to the bottom of the rectangle. This value must be combined with DT_SINGLELINE.
DT_CALCRECT
Determines the width and height of the rectangle. If there are multiple lines of text, DrawTextEx uses the width of the rectangle pointed to by the lprc parameter and extends the base of the rectangle to bound the last line of text. If there is only one line of text, DrawTextEx modifies the right side of the rectangle so that it bounds the last character in the line. In either case, DrawTextEx returns the height of the formatted text, but does not draw the text.
DT_CENTER
Centers text horizontally in the rectangle.
DT_EDITCONTROL
Duplicates the text-displaying characteristics of a multiline edit control. Specifically, the average character width is calculated in the same manner as for an edit control, and the function does not display a partially visible last line.
DT_END_ELLIPSIS or DT_PATH_ELLIPSIS
Replaces part of the given string with ellipses, if necessary, so that the result fits in the specified rectangle. The given string is not modified unless the DT_MODIFYSTRING flag is specified.
You can specify DT_END_ELLIPSIS to replace characters at the end of the string, or DT_PATH_ELLIPSIS to replace characters in the middle of the string. If the string contains backslash (\) characters, DT_PATH_ELLIPSIS preserves as much as possible of the text after the last backslash.
DT_EXPANDTABS
Expands tab characters. The default number of characters per tab is eight.
DT_EXTERNALLEADING
Includes the font external leading in line height. Normally, external leading is not included in the height of a line of text.
DT_LEFT
Aligns text to the left.
DT_MODIFYSTRING
Modifies the given string to match the displayed text. This flag has no effect unless the DT_END_ELLIPSIS or DT_PATH_ELLIPSIS flag is specified.
DT_NOCLIP
Draws without clipping. DrawTextEx is somewhat faster when DT_NOCLIP is used.
DT_NOPREFIX
Turns off processing of prefix characters. Normally, DrawTextEx interprets the ampersand (&
mnemonic-prefix character as a directive to underscore the character that follows, and the double ampersand (&& mnemonic-prefix characters as a directive to print a single ampersand. By specifying DT_NOPREFIX, this processing is turned off.DT_RIGHT
Aligns text to the right.
DT_RTLREADING
Layout in right to left reading order for bi-directional text when the font selected into the hdc is a Hebrew or Arabic font. The default reading order for all text is left to right.
DT_SINGLELINE
Displays text on a single line only. Carriage returns and linefeeds do not break the line.
DT_TABSTOP
Sets tab stops. The DRAWTEXTPARAMS structure pointed to by the lpDTParams parameter specifies the number of average character widths per tab stop.
DT_TOP
Top justifies text (single line only).
DT_VCENTER
Centers text vertically (single line only).
DT_WORDBREAK
Breaks words. Lines are automatically broken between words if a word extends past the edge of the rectangle specified by the lprc parameter. A carriage return-linefeed sequence also breaks the line.
· dwDTParams
Points to a DRAWTEXTPARAMS structure that specifies additional formatting options. This parameter can be NULL.
HTH,
Rich
-
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply