VBA: How go I get the total width from all controls in an MS-Access form?

Question

Hi,

This is probably very basic stuff, but please bear in mind I am completely new to these things.

I am working on a procedure for my Access datasheet forms that will:

1. Adjust the width of each column to fit content
2. Sum the total width of all columns and subtract it from the size of the window's width
3. Adjust the width of one of the columns to fit the remaining space

This is the code that adjusts the width of each column to fit content (which works fine):

Dim Ctrl As Control
Dim Path As String
Dim ClmWidth As Integer

'Adjust column width to fit content
For Each Ctrl In Me.Controls
    If TypeOf Ctrl Is TextBox Then
        Path = Ctrl.Name
        Me(Path).ColumnWidth = -2
    End If
Next Ctrl

How should I write the code so I get the total width of all columns?

Thanks a lot!

Stefan

Solution

---

This is the code that makes an Access datasheet go from this:

To this:

Sub AdjustColumnWidth(frm As Form, clm As String)

On Error GoTo HandleError

Dim intWindowWidth As Integer   ' Window width property
Dim ctrl As Control             ' Control
Dim intCtrlWidth As Integer     ' Control width property
Dim intCtrlSum As Integer       ' Control width property sum
Dim intCtrlAdj As Integer       ' Control width property remaining after substracted intCtrSum

'Adjust column width to standard width
For Each ctrl In frm.Controls
    If TypeOf ctrl Is TextBox Or TypeOf ctrl Is CheckBox Or TypeOf ctrl Is ComboBox Then
        Path = ctrl.Name
        frm(Path).ColumnWidth = 1500
    End If
Next ctrl

'Get total column width
For Each ctrl In frm.Controls
    If TypeOf ctrl Is TextBox Or TypeOf ctrl Is CheckBox Or TypeOf ctrl Is ComboBox Then
        Path = ctrl.Name
        intCtrlWidth = frm(Path).ColumnWidth
        If Path <> clm Then
            intCtrlSum = intCtrlSum + intCtrlWidth
        End If
    End If
Next ctrl

'Adjust column to fit window
intWindowWidth = frm.WindowWidth - 270
intCtrlAdj = intWindowWidth - intCtrlSum
frm.Width = intWindowWidth
frm(clm).ColumnWidth = intCtrlAdj

Debug.Print "Totalt (Ctrl): " & intCtrlSum
Debug.Print "Totalt (Window): " & intWindowWidth
Debug.Print "Totalt (Remaining): " & intCtrlAdj
Debug.Print "clm : " & clm

HandleError:
GeneralErrorHandler Err.Number, Err.Description
Exit Sub

End Sub

Code to call procedure:

Private Sub Form_Load()

Call AdjustColumnWidth(Me, "txtDescription")

End Sub

Answer 1

I would just loop through the forms controls collection like you have done before and add up the widths by storing it in a variable. You can even tack it into your existing loop so you are not looping twice, just do something like

sTotal_width=sTotal_width + Ctrl.Width