Solved

Text Field "Overflow" (Needs to be scrolled) indicator

Posted on 2006-06-09
9
601 Views
Last Modified: 2011-10-03
Is there a way to detect whether a vertically scrollable text field on a form has text that is not shown (i.e., you need to scroll to see it?)

I've got a situation in which a lot of these fields will be visually scanned, and I would like to be able to perform a test and show/hide a little red rectangle next to the lower right corner of the field if there are lines that need to be scrolled to.

I cleverly built the subform these text fields are on so it is exactly 3 lines high, which effectively hides any 4th line completely. I could make the form a little taller, so that a half line would show...but some kind of test and the little red indicator would be more certain.

Any guidance on this would be appreciated.

Thanks!
0
Comment
Question by:codequest
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
You can use a recordcount function and find the number of records that might appear on the subform, then subtract the number of lines you can display, and if the diff is >0 then your box is red.....something like that?
J
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
I think I see what you've done...although I don't know why you would want to do it that way. But if your textbox is 1" wide, that means it can display about 15 characters, multiply that times 4 if your box is that tall, and that's how many characters you can display before you need to scroll. So in light of that, each string would have to be character counted and divided by 60 or so to find your "TRUE" for your box.

WHERE LEN(YourVarcharColumnName) > 60
j
0
 
LVL 2

Author Comment

by:codequest
Comment Utility
Thanks for the input.  I was hoping there might be a way besides character count, because I use a lot of line returns in the text field.   If the calc is too complicated I'll just make the text field a little taller so it shows a partial line.

I'll leave this out there a bit and see if there are any other suggestions.

Good prompt; 'preciate it.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Well playing with this...this works with the forms On_Current event. You'll need to do the whole focus thing for each control. Maybe a loop to get all of them with one loop.

Me.Text4.SetFocus    '<-------Your text box
If Len(Me.Text4.Text) > 10 Then
Me.Box7.Visible = True    '<-------------Your red box
Else
Me.Box7.Visible = False
End If

Laterz
J
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 34

Accepted Solution

by:
jefftwilley earned 250 total points
Comment Utility
Chr(13) is Carriage Return
Chr(10) is Line feed

Me.Text4.SetFocus    '<-------Your text box
strVal = Me.Text4.Text
If Len(strVal) > 10 Or InStr(strVal, Chr(13)) Or InStr(strVal, Chr(10)) Then  '<---looks for line feed or CR
Me.Box7.Visible = True    '<-------------Your red box
Else
Me.Box7.Visible = False
End If
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 125 total points
Comment Utility
Hello codequest

If you don't want to count characters – which is widely inaccurate, you will have to ask Windows to draw the text for you, and then study the resulting size... Lebans is good with that sort of things, and you can study his code:

NEW  AutoSizeTextBox.zip Ver 3.0 is a database containing functions to automatically resize a TextBox control to fit the current fields contents.
http://www.lebans.com/autosize_textbox.htm

The code can easily be tweaked so that is draws in a fixed width, and you can examine the resulting text height to count the lines it will require. However, the scroll bar is missing from his sample. He shows elsewhere on his site how to obtain the scrollbar width (set by the user preferences).

All in all, it would require a few hours of intense head-scratching, but it can be done.

(°v°)
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 125 total points
Comment Utility
Hello codequest,

I found the code below on one of the public forums last year, and kept it for potential future use. According to the postee, it provides two different procs that auto-scroll the most recently added line into view.  It is supposed to be particularly useful for displaying logging information in a RichTextBox or memo control. I would try the Windows API Auto Function first.  If that doesn't work for you, then try the second one.  Hope this helps.  

<<<Herfried K. Wagner [MVP]>>>
If you want to scroll to the end of the text, there are two ways to
accomplish it:
\\\
Private Const WM_VSCROLL As Int32 = &H115
Private Const SB_BOTTOM As Int32 = 7


Private Declare Auto Function SendMessage Lib "user32.dll" ( _
    ByVal hwnd As IntPtr, _
    ByVal wMsg As Int32, _
    ByVal wParam As Int32, _
    ByVal lParam As Int32 _
) As Int32
______________________________________________________________

Private Sub AddLine(ByVal Destination As RichTextBox, ByVal Text As String)
    With Destination
        .AppendText(Text & ControlChars.NewLine)
        SendMessage(Destination.Handle, WM_VSCROLL, SB_BOTTOM, 0)
    End With
End Sub
///
0
 
LVL 2

Author Comment

by:codequest
Comment Utility
Thanks for inputs.   I'll ponder these...though I'm seeing that if it's easy it may not be accurate, and if it's more accurate it's not very easy...so I may decide it's not worth pursuing.   I appreciate the info, though.
0
 
LVL 58

Expert Comment

by:harfang
Comment Utility
Hello codequest

Appears you accepted too quickly... ;)

I had some time and your problem was intriguing, so I did as I suggested. Here is the result, I think it does exactly what you need: check for "overflow" to give some visual clue on your form.

'-------------------------------------------------------------------------------
' Module        basTextboxOverflow
' Copyright     Markus Fischer, Geneva, 2006
' Author        Markus G Fischer - www.harfang.ch
' EE            www.experts-exchange.com/M_94825.html (°v°)
' Created       Jun-2006
' Credits       Inspired by a module by Stephen Lebans, to be found at
'               http://www.lebans.com/autosize_textbox.htm
' Distribution  free if proper credit given
'-------------------------------------------------------------------------------
'
' Purpose
' ¯¯¯¯¯¯¯
' Implements a single function:
'
'   TextBoxOverflow(ptxtBox As TextBox) As Boolean
'
'       ptxtBox:    TextBox, normally multi-line memo control
'
'       Returns:    True if the text in the control does not fit
'                   i.e. it overflows, the scrollbar is needed
'
' Usage
' ¯¯¯¯¯
' Call for example in form events:
'
'   Private Sub Form_Current()
'       txtMemo_AfterUpdate
'   End Sub
'
'   Private Sub txtMemo_AfterUpdate()
'       lblContinued.Visible = TextBoxOverflow(Me.txtMemo)
'   End Sub
'
Option Compare Database
Option Explicit

' WinAPI Constants, types and declares...

' Get, release and examine a display device context:
Private Declare Function GetDC Lib "user32" _
    (ByVal hWnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
    (ByVal hWnd As Long, ByVal hDC As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
    (ByVal hDC As Long, ByVal nIndex As Long) As Long

' needed subfunctions for the logical screen resolution
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90

' logical font information:
Private Const LF_FACESIZE = 32
Private Type LOGFONT
    lfHeight As Long
    lfWidth As Long
    lfEscapement As Long
    lfOrientation As Long
    lfWeight As Long
    lfItalic As Byte
    lfUnderline As Byte
    lfStrikeOut As Byte
    lfCharSet As Byte
    lfOutPrecision As Byte
    lfClipPrecision As Byte
    lfQuality As Byte
    lfPitchAndFamily As Byte
    lfFaceName As String * LF_FACESIZE
End Type

' Creation of a font, setting and delting font objects
Private Declare Function CreateFontIndirectA Lib "gdi32" _
    (lpLogFont As LOGFONT) As Long
Private Declare Function SelectObject Lib "gdi32" _
    (ByVal hDC As Long, ByVal hObject As Long) As Long
Private Declare Function DeleteObject Lib "gdi32" _
    (ByVal hObject As Long) As Long

' windows metrics informations (for scrollbar width)
Private Type NONCLIENTMETRICS
    cbSize As Long
    iBorderWidth As Long
    iScrollWidth As Long
    iScrollHeight As Long
    iCaptionWidth As Long
    iCaptionHeight As Long
    lfCaptionFont As LOGFONT
    iSMCaptionWidth As Long
    iSMCaptionHeight As Long
    lfSMCaptionFont As LOGFONT
    iMenuWidth As Long
    iMenuHeight As Long
    lfMenuFont As LOGFONT
    lfStatusFont As LOGFONT
    lfMessageFont As LOGFONT
End Type

' call for the metrics above
Private Declare Function SystemParametersInfoA Lib "user32" ( _
    ByVal uAction As Long, _
    ByVal uParam As Long, _
    ByRef lpvParam As Any, _
    ByVal fuWinIni As Long _
    ) As Long
' needed subfunction:
Private Const SPI_GETNONCLIENTMETRICS& = 41&

' Standard rectangle information
Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

' Draw text in a given DC on a given rectangle...
Private Declare Function DrawText Lib "user32" Alias "DrawTextA" ( _
    ByVal hDC As Long, _
    ByVal lpStr As String, _
    ByVal nCount As Long, _
    lpRect As RECT, _
    ByVal wFormat As Long _
    ) As Long

' needed arguments for wFormat
Public Const DT_WORDBREAK = &H10
Public Const DT_CALCRECT = &H400

' Only exported function:
'
Function TextBoxOverflow(ptxtBox As TextBox) As Boolean
'
' Simply returns True if the textbox .Value cannot be displayed using
' the current .Width, .Height, and .Scrollbars.
'
' Adjusts the actual metrics for control border style, and gets the width
' of the scrollbar if it is activated.
'
    Dim rectCtl     As RECT         ' the control's rectangle
    Dim hWnd        As Long         ' form's window
    Dim hDC         As Long         ' device context
    Dim dblTPPX     As Double       ' twips per pixel X
    Dim dblTPPY     As Double       ' twops per pixel Y
    Dim lfNew       As LOGFONT      ' font information
    Dim hNewF       As Long         ' new font structure pointer
    Dim hOldF       As Long         ' old font structure pointer
    Dim ncmWin      As NONCLIENTMETRICS
    Dim lngScrollW  As Long         ' width of the scrollbar
    Dim lngBorderX  As Long         ' border (horizontally)
    Dim lngBorderY  As Long         ' border (vertically)

    ' False if control is null
    If IsNull(ptxtBox) Then Exit Function
   
    ' Get logical screen resolution
    hDC = GetDC(0&)
    dblTPPX = 1440 / GetDeviceCaps(hDC, LOGPIXELSX)
    dblTPPY = 1440 / GetDeviceCaps(hDC, LOGPIXELSX)
    ReleaseDC 0&, hDC
   
    ' guesstimate border widths
    With ptxtBox
        lngBorderX = 2
        lngBorderY = 2
        If .SpecialEffect >= 1 And .SpecialEffect <= 3 Then
            ' raised, sunken on chiseled
            lngBorderX = lngBorderX + 2
            lngBorderY = lngBorderY + 2
        ElseIf .BorderWidth > 0 And (.SpecialEffect = 4 Or .BorderStyle) Then
            lngBorderX = lngBorderX + .BorderWidth * 1440 / 72 / dblTPPX
            lngBorderY = lngBorderY + .BorderWidth * 1440 / 72 / dblTPPY
        End If
    End With
   
    ' get default scrollbar width (if needed)
    If ptxtBox.ScrollBars Then
        ncmWin.cbSize = 340
        SystemParametersInfoA SPI_GETNONCLIENTMETRICS, 0&, ncmWin, 0&
        lngScrollW = ncmWin.iScrollWidth
    End If
   
    ' prepare font information from control
    With lfNew
        .lfHeight = -(ptxtBox.FontSize * 1440 / 72 / dblTPPY)
        .lfWeight = ptxtBox.FontWeight
        .lfItalic = ptxtBox.FontItalic
        .lfUnderline = ptxtBox.FontUnderline
        .lfFaceName = ptxtBox.FontName & vbNullChar
    End With
   
    ' get width of control in pixel
    rectCtl.Right = ptxtBox.Width / dblTPPX - lngBorderX - lngScrollW
   
    ' get form's device display context, create font and set
    hDC = GetDC(ptxtBox.Parent.hWnd)
    hNewF = CreateFontIndirectA(lfNew)
    SelectObject hDC, hNewF
   
    ' draw the text in the available width
    DrawText hDC, ptxtBox.Value, -1, rectCtl, DT_CALCRECT + DT_WORDBREAK
    ' return true if there is not enough vertical room...
    TextBoxOverflow = ptxtBox.Height / dblTPPY < rectCtl.Bottom + lngBorderY
   
    ' restore prior font, delete our temp font.
    SelectObject hDC, hOldF
    DeleteObject hNewF
   
End Function

'-------------------------------------------------------------------------------

(°v°)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now