Forms (And Reports): Sizing Text Boxes

[Debris]

Hello,

Is there a rule-of-thumb or some technique to size text boxes / combo boxes
based on the font size and expected number of characters?

For instance, I have one text box where up to nine digits are entered, and I
use Tahoma 9pt. Through trial and error I've figured out a text box size,
but I'm tired of trying 1.5", 1", 0.75", and so on.

Not really looking for a means to programmatically auto-size the text box, I
just want to somehow eliminate the trial-and-error approach in form design.

This would be even more useful on a report, too...

Thanks,

D

 

[Allen Browne]

This is really, really, variable, D.

For example, some users enter all caps, so the space taken up by just 6
characters could be either:
WWWWWW
IIIIII
(Hope you're reading in a proportional font.)

It is time-consuming. I still find myself placing a label in the form/report
footer, and pasting sample text in to see how wide it needs to be.

Microsoft did try to address this in Access 2007, by providing Layout view.

 

[fredg]

Hello,

Is there a rule-of-thumb or some technique to size text boxes / combo boxes
based on the font size and expected number of characters?

For instance, I have one text box where up to nine digits are entered, and I
use Tahoma 9pt. Through trial and error I've figured out a text box size,
but I'm tired of trying 1.5", 1", 0.75", and so on.

Not really looking for a means to programmatically auto-size the text box, I
just want to somehow eliminate the trial-and-error approach in form design.

This would be even more useful on a report, too...

Thanks,

D

See what Stephen Leban's has available at:
http://www.lebans.com

 

[Debris]

Excellent point.

(No pun intended.)

I did figure out that I could specify the text box width in points, but that
brings up what you described below (10 characters x 8pt font = 1.11" turned
out to be way too wide!).

While working on another issue, I did discover that shading the box helps
figure out how much "white space" I'm wasting -- quite a bit, in my case...

Thanks, D

 

[Allen Browne]

I did figure out that I could specify the text box width in points, but
that
brings up what you described below (10 characters x 8pt font = 1.11"
turned
out to be way too wide!).

While working on another issue, I did discover that shading the box helps
figure out how much "white space" I'm wasting -- quite a bit, in my
case...

Okay, D, here's a bit of fun.

Open the Immediate Window (Ctrl+G), and enter:
? GetLongest("Table1")
substituting your table name. Yields the longest value present in the table,
for each field.

Function GetLongest(ByVal strTable As String)
'Purpose: Finding the longest actual data in each field, _
to help with form/report design.
'Output: To immediate window.
'Argument: Name a table or query in current database.
'Example: ? GetLongest("Table1")
Dim db As DAO.Database
Dim fld As DAO.Field
Dim rsFieldList As DAO.Recordset
Dim rsData As DAO.Recordset
Dim varResult As Variant
Dim strSql As String
Dim strField As String
Dim strOut As String
Dim iFieldCount As Integer
Dim bCancel As Boolean

'Initialize
Set db = CurrentDb
If strTable = vbNullString Then
Debug.Print "Supply a table or query name"
bCancel = True
Else
'Add square brackets around table name, unless supplied.
If Asc(strTable) <> 91 Then
strTable = "[" & strTable & "]"
End If
End If

If Not bCancel Then
'Get the fields (without data.)
strSql = "SELECT * FROM " & strTable & " WHERE (False);"
Set rsFieldList = db.OpenRecordset(strSql)

'Loop through the fields of the table.
For Each fld In rsFieldList.Fields
Select Case fld.Type
'Skip yes/no, memo (and hyperlink), OLE obj, _
Binary, multi-valued fields.
Case dbBoolean, dbMemo, dbLongBinary, dbBinary, Is > 100
strField = vbNullString
Case dbText
strField = "[" & fld.Name & "]"
strOut = strField & "(T" & fld.Size & ")"
Case Else
strField = "[" & fld.Name & "]"
strOut = strField
End Select

If strField <> vbNullString Then
'Get record with longest data in this field.
strSql = "SELECT TOP 1 " & strField & _
" FROM " & strTable & _
" ORDER BY Len(" & strField & ") DESC;"
Set rsData = db.OpenRecordset(strSql)
If rsData.RecordCount = 0 Then
Debug.Print "No data"
Exit For
Else
'Pad to 40 characters
If Len(strOut) < 40 Then
strOut = strOut & String(40 - Len(strOut), " ")
End If
'Print the field name and data.
Debug.Print strOut, rsData(0)
End If
rsData.Close
Set rsData = Nothing
iFieldCount = iFieldCount + 1
End If
Next

'Summary
Debug.Print iFieldCount & " of " & _
rsFieldList.Fields.Count & " fields listed."
rsFieldList.Close
End If

'Clean up
Set rsFieldList = Nothing
Set rsData = Nothing
Set fld = Nothing
Set rsFieldList = Nothing
Set db = Nothing
End Function