Back in 2004 the User anb001 had a problem with forcing a text box to show a vertical scroll bar.
The argument in 2004 seemed to focus on "Why" rather than "How".
I have the same problem, and as it happens the same reasons as anb001.
I want the User to be aware at a glance that there is more text than is currently visible in a "Notes" Textbox.
Other than a rather heated discussion about WHY anb001 wanted this facility, there was very little in the way of help.
Such options as were offered seemed to boil down to "Set the textbox to have no scrollbars in the Properties, then at run time count the characters to be displayed, and if you think there's too many to fit in the box, turn the vertical scrollbar on".
This is not a million miles away from "just set the textbox's scroll property to 'None', and at Form Load, set it to 'Vertical'".
I've tried this (and other variants), and they have absolutely no effect until the textbox has focus.
I've even tried switching focus to the textbox on Form_Current, which shows the scrollbar (even when the textbox is empty), but the bar just vanishes when any other control takes focus (and the text is highlighted, which looks ugly).
In the intervening 13 years, has anyone found a solution which causes a textbox to retain scrollbars regardless of which control has focus?
Access provides preconfigured forms, controls etc which users can reconfigure within limits. If you use something like .net then controls come are at a much more basic level and you have to do all the configuring yourself. Benefit for access is rapid development at a cost of say 5% of not being able to do something.
So the answer is no, you need to find another way.
In particular his grow/shrink solution for forms. If you don't want to grow/shrink, then you could adapt it to show/hide a label with a down arrow or text saying 'there is more than you can see' placed next to the control in question - or used to apply conditional formatting to the control itself.
Another one to investigate is his Hscroller example - you can use this to get to the scrollbar window (every form, every control, every feature - scrollbar, navigation bar etc is a window) and perhaps you can force it to be visible at all times.
'If field1 > 10 chars display first 10 + ellipsis
'if field1 has focus display full text with scroll
Private Sub field1_Enter()
If field1.ControlSource = "" Then field1.ControlSource = "field1"
End Sub
Private Sub field1_Exit(Cancel As Integer)
Form_Current
End Sub
Private Sub Form_Current()
If Len(field1) > 10 Then
field1.ControlSource = ""
field1 = Left(Recordset!field1, 10) & "..."
Else
field1.ControlSource = "field1"
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
field1.ScrollBars = 2
End Sub
It does work in continuous forms (but see note below)but of course not in datasheet view
I think its slightly more obvious if you modify the Form_Current code on these lines:
Code:
Private Sub Form_Current()
If Len(field1) > 10 Then
field1.ControlSource = ""
field1 = Left(Recordset!field1, 10) & ". . ." & _
vbNewLine & vbNewLine & "Click to view the full record"
Else
field1.ControlSource = "field1"
End If
End Sub
However, even better would be to have the extra line in a different colour e.g. RED as in the examples below
I did this with a hidden label (lbl1) with RED text over the lower part of the control
Code:
'If field1 > 20 chars display first 20 + ellipsis + show label with RED text
'if field1 has focus, display full text with scroll + hide label with RED text
Private Sub field1_Enter()
If Me.field1.ControlSource = "" Then
Me.field1.ControlSource = "field1"
Me.lbl1.visible=False
End Sub
Private Sub field1_Exit(Cancel As Integer)
Form_Current
End Sub
Private Sub Form_Current()
If Len(field1) > 20 Then
Me.field1.ControlSource = ""
Me.field1 = Left(Recordset!field1, 20) & ". . ."
Me.Lbl1.visible = True
Else
Me.field1.ControlSource = "field1"
Me.lbl1.visible=False
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.field1.ScrollBars = 2
End Sub
NOTE:
Changes to one record in a continuous form affect all records.
So if the red text is clicked it disappears for all records and all records shows the full field text
Of course, the question remains - is it really worth the effort of doing either of these solutions....?
After a fair bit of reading, at my skill level the only real option is to take up the suggestions from Static and ridders of having a Label saying something like
“Click for more ==>” next to the box, or possibly some code to change the colour and width of the Textbox’s border.
In either case I need to know if the textbox will “overflow”.
With thanks to all who’ve contributed various code snippets, ideas, suggested reading etc., and after lots of frustrating journeys up blind alleys, I’ve created the following code to try to create a generic solution which will identify if supplied text will fit within a given textbox.
The main routine is “funWillTextFit”.
This uses “funGetTextDims” to get the useable size of the text box.
Note:- It took ages before I discovered the significance of the “padding” properties in a text box!
However, there is still an issue with the perception of the text on screen. Often, a line of text may be visible even though technically it doesn’t completely fit height-wise.
I don’t know why this happens (my inexperience? screen rendering? formatting code deep in the bowels of Access?)
To try to deal with that, I have introduced a “Fuzzy” percentage value. If the “needed” size exceeds the available size by less than the “fuzzy” percentage, the function returns a True condition (i.e. the whole of the text is readable). In the example it’s set to 7.73, which works for me in the application I’m developing.
I’ve also included a test harness to show how the function may be invoked.
There is code in the test harness to allow you to adjust the size of the textbox, and the font of the text.
There are also MsgBoxs to identify what’s going on – remove or enhance these as you wish.
To run the test, create a new database. In it, create a Form called “frmTest”. Add a textbox control to “frmTest”, naming the control “tbxTest” .
Use “Alt+F11” (or the Visual Basic button in the Create/Macros and Code section of the ribbon) to get to the Visual Basic editor.
Paste the following code.
Hopefully the comments will explain what’s happening….
Code:
Option Compare Database
Option Explicit
Public Sub TestTextFit()
Dim booRes As Boolean
Dim ctlOnForm As Control
Dim frm As Form
Dim intFont As Integer
Dim sngH As Single
Dim sngW As Single
Dim strFont As String
Dim strTestTxt As String
'*
'** Set the details to be used.
'*
strTestTxt = "Writing lots of text to see if the vertical scroll bar on the control actually appears, or is it just one of those things which are supposed to work but in reality don't!"
strFont = "Calibri"
intFont = 11
sngH = funCmToTwip(2)
sngW = funCmToTwip(7)
'*
'** Show the Form, so the text can be seen.
'** The form needs to be in Form view.
'** (We close the Form if it was already open.)
'*
On Error Resume Next
DoCmd.Close acForm, "frmTest", acSaveNo
On Error GoTo 0
DoCmd.OpenForm "frmTest", acNormal
Set frm = Form_frmTest
'*
'** Resize the textbox.
'*
Set ctlOnForm = frm!tbxTest
With frm!tbxTest
.Properties("Width") = sngW
.Properties("Height") = sngH
.Properties("FontName") = strFont
.Properties("FontSize") = intFont
End With
frm!tbxTest.Value = strTestTxt
'*
'** Get the result.
'*
booRes = funWillTextFit(ctlOnForm, _
strTestTxt)
'*
'** Tell User the result.
'*
Call MsgBox(strTestTxt & vbCrLf & _
vbCrLf & _
booRes)
'*
'** Dismiss the Form.
'*
' DoCmd.Close acForm, "frmTest", acSaveNo
End Sub 'TestTextFit
Public Function funWillTextFit(ctl As Control, _
ByVal str As String) As Boolean
Const csngFuzzy As Single = 7.73
Dim intLinesAvail As Integer
Dim intLinesInteger As Integer
Dim intLinesRequired As Integer
Dim sngCtlHeight As Single
Dim sngCtlWidth As Single
Dim sngDiff As Single
Dim sngLinesAvail As Single
Dim sngLinesRequired As Single
Dim sngPcnt As Single
Dim sngTextHeight As Single
Dim sngTextWidth As Single
'*
'** Get Control's dimensions
'** (in Twips).
'*
sngCtlHeight = ctl.Height - (ctl.TopPadding + ctl.BottomPadding)
sngCtlWidth = ctl.Width - (ctl.LeftPadding + ctl.RightPadding)
'*
'** Get the supplied text's dimensions
'** as it would be if placed in the
'** control using the control's
'** current dimensions and font (in Twips).
'** 1 cm = approx 567 twips.
'*
sngTextWidth = funGetTextDims(ctl, str)
sngTextHeight = funGetTextDims(ctl, str, True)
'*
'** Number of lines required is
'** text width / ctl width, rounded up.
'*
sngLinesRequired = sngTextWidth / sngCtlWidth
intLinesRequired = Round(sngLinesRequired + 0.5, 0)
sngLinesAvail = sngCtlHeight / sngTextHeight
intLinesAvail = Round(sngLinesAvail - 0.5, 0)
MsgBox "we need to fit " & _
Round(sngLinesRequired, 5) & _
" (" & intLinesRequired & ")" & _
vbCrLf & _
"lines of text into a " & _
Round(sngLinesAvail, 5) & _
" (" & intLinesAvail & ")" & _
" line box"
sngDiff = sngLinesRequired - sngLinesAvail
sngPcnt = (sngDiff / sngLinesAvail) * 100
'*
'** Number of lines available is
'** text height / ctl height, rounded down.
'*
Call MsgBox("Lines Req'd: " & _
sngLinesRequired & _
vbCrLf & _
"Lines Avail: " & _
sngLinesAvail & _
vbCrLf & _
"Diff pcnt: " & _
Round(sngPcnt, 2), _
vbInformation, _
"DIAGNOSTIC")
If sngLinesRequired > sngLinesAvail Then
If sngPcnt < csngFuzzy Then
funWillTextFit = True
Else
funWillTextFit = False
End If
Else
funWillTextFit = True
End If
End Function 'funWillTextFit
Public Function funGetTextDims(ctl As Control, _
ByVal strTest As String, _
Optional ByVal booHeight As Boolean = False) As Long
Dim lngH As Long
Dim lngW As Long
'*
'** Initialize WizHook
'*
WizHook.Key = 51488399
'*
'** Get the width (lngW) and height
'** (lngH) of the string in twips, based
'** on the font settings of the control
'** using the largely undocumented
'** WizHook function TwipsFromFont.
'*
With ctl
WizHook.TwipsFromFont .FontName, _
.FontSize, _
.FontWeight, _
.FontItalic, _
.FontUnderline, _
0, _
strTest, _
0, _
lngW, _
lngH
End With
If booHeight Then
funGetTextDims = lngH
Else
funGetTextDims = lngW
End If
End Function 'funGetTextDims
Public Function funTwipToCm(sngT As Single) As Single
funTwipToCm = sngT / 567
End Function 'funTwipToCm
Public Function funCmToTwip(sngC As Single) As Single
funCmToTwip = sngC * 567
End Function 'funCmToTwip
1. Its not really a matter of skill level.
The standard Access textbox simply does NOT allow the scroll bars to be visible unless it has the focus
This can only be overcome by:
a) doing a kludge such as that suggested by Static & myself
b) the very clever (but complex) code by Stephen Lebans
c) by using an alternative such as the ActiveX control
If you want to use a), I would suggest you set a number of characters (or lines) to be visible.
This is much easier than using a % and will avoid the issue of part of a line being visible at the bottom of the textbox.
Static used 10 characters, I used 20.
Choose your own number
Whilst I wish the functionality you want was available, it isn't.
Solutions involving wizhook, twips etc seem way over the top
Is it really worth doing this sort of kludge on every textbox with a memo/long text field throughout your database?
Suggest instead you add this to the MS Access suggestions forum on the MS website. You never know, they may like it & do it....
P.S. I'm impressed you managed to find out the wizhook key!
I only know it from the author of the V-Tools addin.
He found it out by 'brute force' testing!
The other option if the data doesn't need to be edited is to bind the form to a report and allow the variable sections to grow to display all the text.
I use this for a continuous "form" to display ongoing notes on service calls, without the restriction of a specific size of textbox.
I know it's "over the top" ridders, but I've spent my professional I.T. life writing applications for seriously non-I.T. specialists.
I'm old fashioned (or is that just 'old'? - is 66 old?) - I still believe it's often worth going the extra mile to give Users the extra inch (no sniggering please!)
I use a great code library manager called MZ-Tools (no I'm not flogging it - just Google it if you're interested), so once I've got a working routine I stick it in the Library and can therefore quickly install it in a "subroutines" module which all of my Office VBA code tends to contain. So in this case, if I want to pay special attention to a textbox I can just call a Function to see if text will overflow, then decide what appropriate action to take.
OK, it took a couple of weeks to develop this code, but once it's in my MZ-Tools library I can redeploy it in a couple of seconds.
Thanks again to all who've taken the time to reply - I posted the full code in this forum to try to help others by showing another approach to a potential solution.
Oh, and by the way, the simple character count solution is a good solution but would need to be recalculated if during development you decided to change the font face or font size. And how many times have we all had the "Oh bums!" moment on realising we forgot to make an appropriate adjustment following a change?
The Wizhook solution uses the textbox's run-time font attributes to determine the space used, so if you change anything during development you don't need to recalculate the textbox's capacity.
You sound very much like me .. once I get my teeth into a problem, I rarely let go until I've solved it to my satisfaction.
I'm old fashioned (or is that just 'old'? - is 66 old?) - I still believe it's often worth going the extra mile to give Users the extra inch (no sniggering please!)
Yes, 66 is absolutely ancient or at least it will be till May 2018 when I hit 66 in which case it will seem young again ....
And at our age, that extra inch isn't always so easy to achieve!
I did look at MZ-Tools a while ago but, as its no longer free, I removed it again
However, like you, I have my own code repository where, in theory, I can find useful code quickly when I need it again.
the simple character count solution is a good solution but would need to be recalculated if during development you decided to change the font face or font size.
I just stick to the same style throughout with Calibri 11 for every form.
It used to be Tahoma until I got bored with that ....
The Wizhook solution uses the textbox's run-time font attributes to determine the space used, so if you change anything during development you don't need to recalculate the textbox's capacity.
Ah - I didn't know that
I came across Wizhook when I was developing my own database full SQL search facility.
This is used to search all database components for specified strings in table fields, query SQL, form/report/standard & class modules and if required, replace with a different string
Very useful for tidying up old databases when my naming convention left something to be desired!
Once I'd got that far, I thought I'd tackle macros although I don't use them myself. That was much trickier.
Someone on this forum thought I was reinventing the wheel (thanks Galaxiom!) and mentioned V-Tools which is free and comes with full program code.
The author (Skrol) used Wizhook to extract macro code into a readable form.
However, he couldn't find a way of then replacing the macro 'text' using Wizhook
So I investigated and eventually found a way BUT not using Wizhook (which I've never used again)
I was happy - and Skrol was interested in how I'd done it.
Does anyone else care - probably not!
P.S. How is Yorkshire these days. I lived in York for almost 10 years a long time ago....
Trouble with undocumented/hidden functions is that they generally aren't supported so you've no guarantee that they'll work in future versions.
I'd use the code CJ_London linked to that uses the Windows API. The fact that's it's been up so long and had a few revisions also means it should be well tested and bug free.
I am trying to employ the exact same solution as ridder suggested for being able to see that a form field has more text without requiring focus.
However, I keep getting an error. I have a continuous form called Members with a subform called MemberDetailsSubform which contains a Textbox called Details.
I have used your code. I keep getting a variable not defined error. Is it possible the error has something to do with referencing the subform incorrectly? I have pasted my code into yours below.
I also tried using the On Current and On Open Events in the subform instead of the parent form but still got an error.
On My Parent Form called Members I have:
Private Sub Form_Current()
If Len(Details) > 20 Then
Me.Details.ControlSource = ""
Me.Details = Left(Recordset!Details, 20) & ". . ."
Me.FullRecordLabel.Visible = True
Else
Me.Details.ControlSource = "Details"
Me.FullRecordLabel.Visible = False
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.Details.ScrollBars = 2
End Sub
On my Subform called MemberDetailsSubform in my TextBox called Details, I have:
Private Sub Details_Enter()
If Me.Details.ControlSource = "" Then
Me.Details.ControlSource = "Details"
Me.FullRecordLabel.Visible = False
End Sub
Private Sub Details_Exit(Cancel As Integer)
Form_Current
End Sub
Thank you for any guideance you can give. I am using MS Access 2016 btw.
Thanks for your help Ridders. Sorry for the slow reply....your email was in my spam filter.
I am trying to employ Ridders solution for a continous form with a label that goes visible/invisible based on the number of characters to show me that there is additional information in a long field text box without requiring focus. Unfortunately, I am getting an error.
Would anyone be able to help me solve this problem? I have attached a scrubbed, compacted, and compressed version of the database. It does open to a form with navigation disabled, but the shift while opening key is still enabled to get to design view.
You have a missing End If in the following event of your subfform:
Code:
Private Sub Details_Enter()
If Me.Details.ControlSource = "" Then
Me.Details.ControlSource = "Details"
Me.Lbl1.Visible = False
[COLOR="Red"][B] End If[/B][/COLOR] <== Add this line
End Sub
Try adding that and see whether it now works as you expect.
I can't see how to load the members form and subform from the startup form so can't test it properly
I made that change but I am still getting the same error. When they open the db, it opens to Dashboard form. From there they will click on Member List button, then the Member Log button for any of the Members.
If I navigate in that method, when I click on Member Log button (of any Member from the List), I am getting a No Current Record error with the debugger opening and highlighted on the third line.
Code:
Private Sub Form_Current()
If Len(Details) > 120 Then
Me.Details.ControlSource = ""
Me.Details = Left(Recordset!Details, 120) & ". . ."
Me.Lbl1.Visible = True
Else
Me.Details.ControlSource = "Details"
Me.Lbl1.Visible = False
End If
End Sub
Also, I forgot to tell you before that I am on Access 365 32 bit. (Side Note: The db worked perfectly (without this current form effort) on all of my computers until I installed it on a machine with Access 64 bit. I tried everything including defining the global variable for 64 bit, etc with no luck. I found a google article that said just uninstall the 64 bit and reinstall the 32 bit. As soon as I did that, it worked there as well.)
I've seen that method recommended many times but I want the user on a continuous form to be able to know which boxes have additional text (at a glance) and without requiring focus on the specific box.
One slight change & that part then opens OK
Change recordset to rst in the line triggering the error
Code:
Private Sub Form_Current()
If Len(Details) > 120 Then
Me.Details.ControlSource = ""
Me.Details = Left([B][COLOR="Red"]rst![/COLOR][/B]Details, 120) & ". . ."
Me.Lbl1.Visible = True
Else
Me.Details.ControlSource = "Details"
Me.Lbl1.Visible = False
End If
End Sub
However other errors then occur on your stripped down db.Try this in your full version & see if it works
@Cronk
I also use the zoombox or my own popup form with additional functionality.
However the reason tif provided was the main point of this thread in the first place
I made that change but now I am getting a variable not defined error. I can dim an rst varaiable as a recordset but I am not sure how to then define the variable so the lines return correctly.
Attached is the full database (only data has been scrubbed) if it helps.
