Is there a way to tell if contents of a field fit in a text box (1 Viewer)

[AndrewS]

Short version:
Is there a way to check if the contents of a field fit in a textbox which is bound to that field?
Or is it just a matter of estimating how many characters would normally be visible in the textbox, and checking the length of the field?

Long version:

I have a subform in continuous view, made to look like datasheet view.

The subform has two text boxes bound to fields "What" and "Comments" - it also has some date and currency fields, but they're not relevant to this question.

The subform has a footer that is not visible by default. The footer contains an unbound text box.

The GotFocus events of the two textboxes sets the footer to visible and binds the textbox on the footer to either "What" or "Comments" so that the user can see more of the text, and doesn't think they have to fit it on one line.

This works fine.

But what I would like to do is only show the footer if the contents of the fields are longer than can be displayed in the textbox on the Detail section of the form.

Is there a clever way to do this, or is it just simply a matter of estimating how many characters might usually be visible in the bound textbox, and testing if the field length is greater than that?

 

[isladogs]

I'm not absolutely clear what you are asking but perhaps this recent thread may be of interest: Vertical scrollbar in a textbox

 

[missinglinq]

Short answer...yes, you'd have to guess at how many characters could be entered in the Textbox of a given width.

Long answer...you could, with a little testing, tell exactly how many characters would fit...but only if you used a monospaced/fixed-pitch, fixed-width/non-proportional font, which is to say a font whose letters and characters each occupy the same amount of horizontal space! And the problem with these fonts is that, at least in my opinion, they look absolutely dorky!

Longer answer still...you might look into 'zooming' the Textbox, as needed!

You can do this with the Hotkey of

<Shift> + <F2>

or, in some event, such as the DoubleClick event of the Textbox, using the code

DoCmd.RunCommand acCmdZoomBox

Linq ;0)>

 

[MarkK]

You can also use the TextWidth and TextHeight methods of the Report object, but it's not super slick. If you create a blank report called 'rGetTextSize' with this code...

Option Compare Database
Option Explicit

Private m_height As Single
Private m_width As Single

Private m_font As String
Private m_size As Single
Private m_text As String

Property Get TextWidthFinal() As Single
    TextWidthFinal = m_width
End Property

Property Get TextHeightFinal() As Single
    TextHeightFinal = m_height
End Property

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Me.ScaleMode = 1                    'twips
    Me.FontName = m_font                'set the font
    Me.FontSize = m_size                'set the size
    m_width = Me.TextWidth(m_text)      'get width
    m_height = Me.TextHeight(m_text)    'get height
End Sub

Private Sub Report_Open(Cancel As Integer)
    Dim var
    
    var = Split(Me.OpenArgs, "|")
    
    m_font = var(0)
    m_size = var(1)
    m_text = var(2)
End Sub

Then you can run code like this...

Sub Test91264384()
    Const RN As String = "rGetTextSize"
    
    DoCmd.OpenReport RN, acViewPreview, , , acHidden, "Arial|12|This is a test"
    With Reports(RN)
        Debug.Print .TextHeightFinal; .TextWidthFinal
    End With
    DoCmd.Close acReport, RN
End Sub

Which is quick and dirty, but I wanted to see if it could be done, and it can. So we pass the font, the font size, and the string whose dimensions we want to know to the form as OpenArgs, and then read the Report's TextWidthFinal and TextHeightFinal properties.

You could easily make that a fair bit tidier, but if that is what you mean to do, that is how it could work.

hth
Mark

 

[isladogs]

Adding to Missinglinq's comments, using the Zoom box (Shift+F2) will give a result similar to this:

Or if you want more control over the layout, create a small popup form like this:

Add code to display this popup form when users click in the textbox

 

[AndrewS]

Thanks everyone.

I'd done a mockup using double-click to activate Zoom, but my couple of tame test users didn't really get it.

They found the full text appearing below much more intuitive, and liked that they could see the full text without having to do anything like double click.

I'd done the quick&dirty way of guessing how many characters might fit in by using lorem ipsum text and counting the characters on screen, and that worked well enough.

Mark's solution is genius and works nicely. But it has the real problem that it is noticeably slower - the footer takes almost a second to appear, as opposed to it appearing instantaneously by checking the LEN of the field.

Because of the display lag in Mark's solution, I've gone with the simpler number of characters option, but I'm going to keep Mark's solution handy.

Ridders' hidden/unhidden label in the other thread (indicating there's more text entered than is visible) he mentions is also a useful idea, though I don't think it's right for the particular form I'm working on now.

Thanks again everyone.

 

[sxschech]

Not sure if this is of use. I have this code to adjust col widths based on number of characters of a given font and point size. Was trial and error to get the count. You can also get a character count in design view when you click on the textbox as if you were going to resize it. The count appears in lower left of access window. Character count will adjust as you resize the object as well as if you change the font or point size.

    Const TWIPSTOINCHES = 1440
'    'TWIPS times (number of inches for 1 character at 8 point Calibri)
    Const TWIPSTOCHARWIDTH = TWIPSTOINCHES * 0.06
   
'    Me.txtTree.ColumnWidth = TWIPSTOCHARWIDTH * 5 'desired character width of 5 characters
    Me.txtSearchTerm.ColumnWidth = TWIPSTOCHARWIDTH * 25

Depending on how much text you have, you could also have the code adjust the point size, so if length is greater than 40 point size = 8 else point size = 10.