ACCESS VBA: SOLUTIONS OF THE PROBLEM THAT AUTOMATIC COLUMN WIDTH ADJUSTMENT DOES NOT WORK WHEN USING USER-DEFINED FUNCTIONS IN THE CONTROL SOURCE.

Oct 13, 2020 · 6 min read · Access VBA ·

Overview

Help me improve my English!

As you probably see, I'm not a native English speaker.
If you find an English expression that feel incorrect or awkward, please let me know.

Message box of Disqus is under the article.
Or my E-Mail is here.

ABOUT THIS ARTICLE

When using a datasheet type form in MSAccess,
in case we assign a user defined function which returns a string to the control source of textbox,
the problem happens that the Automatic adjustment of column width of the form doesn't work as expected.

For example, if a long string is returned from user defined function, the string doesn't fit in the field and is cut off.

As seen below, I have researched the solution.

TL;DR

The timing of calling use defined function is later than the execution of 'Resize' procedure of the Form,
So automatic adjustment for target Textbox by following process is not working.

1Private Sub Form_Resize()
2    
3    '...code
4
5    ctl.ColumnWidth = -2
6    
7    '...code
8
9End Sub

Therefore, one of the following solutions must take place.

Create a function where Control.ColumnWidth = -2 is added to the conventional process of user defined function.
Assign the length explicitly when run Resize.

MAIN

PREMISE

There is a table named T_01 like:
T_01

Suppose a following form named F_01, integrating above table, will be displayed.
F_01

Under these conditions, it is desired that the column widths of each column ID, _Name, and size are automatically adjusted.
Also, I would like to add a new 'size tyep' text column, and store the string "This is 'X' type for he/she's size." which indicates the size symbol of S, M, or L for each value of size. The column is desired to be adjusted, too.

VERIFICATION ENVIRONMENT

Microsoft Access 2019

IMPLEMENT

I've assigned a column width for each textbox in Resize procedure of subform.
For automatic adjustment, ColumnWidth property is to be assign to -2.

 1Private Sub Form_Resize()
 2    
 3    'Const
 4    Const FUNC_NAME As String = "Form_Resize"
 5    
 6    'Variable
 7    Dim ctl As Access.Control
 8    
 9    On Error GoTo ErrorHandler
10    
11    For Each ctl In Me.Controls
12        If ctl.ControlType = acTextBox Then
13            ctl.ColumnWidth = -2
14        End If
15    Next
16    
17ExitHandler:
18
19    Exit Sub
20    
21ErrorHandler:
22
23    MsgBox "An error has occurred and the macro will be terminated." & _
24           vbLf & _
25           "Func Name:" & FUNC_NAME & _
26           vbLf & _
27           "Error No." & Err.Number & vbNewLine & _
28           Err.Description, vbCritical, TOOL_NAME
29        
30    GoTo ExitHandler
31        
32End Sub

Also, to the column of size type text, I would like to assign S, M, and L size symbol for each range of size value.

So I've assigned following formula to the control source of the textbox and created the user defined function getSizeType.

1# control source formula
2=getSizeType([size])

 1'******************************************************************************************
 2'*Function      :get size type
 3'*arg(1)        :size number
 4'*return        :size type
 5'******************************************************************************************
 6Public Function getSizeType(ByVal sizeNum As Long) As String
 7    
 8    'Const
 9    Const FUNC_NAME As String = "getSizeType"
10    
11    'Variable
12    Dim rtn As String
13    
14    On Error GoTo ErrorHandler
15    
16    getSizeType = ""
17    
18    '-169      :S
19    '170-175   :M
20    '-176      :L
21    Select Case True
22    Case sizeNum < 169
23        rtn = "This is S type for he/she's size."
24    Case 176 < sizeNum
25        rtn = "This is L type for he/she's size."
26    Case Else
27        rtn = "This is M type for he/she's size."
28    End Select
29
30    getSizeType = rtn
31    
32ExitHandler:
33
34    Exit Function
35    
36ErrorHandler:
37
38    MsgBox "An error has occurred and the macro will be terminated." & _
39           vbLf & _
40           "Func Name:" & FUNC_NAME & _
41           vbLf & _
42           "Error No." & Err.Number & vbNewLine & _
43           Err.Description, vbCritical, TOOL_NAME
44        
45    GoTo ExitHandler
46        
47End Function

TROUBLE

CUTOFF OF SCREEN

The column widths of the existing fields in the table, ID, _Name, and size, are adjusted correctly.
But column width of size type is cut off.

As its width is too long, I expected it would be adjusted in Form_Resize(), but not working.

cutoff

CAUSE: THE ORDER OF CALLING FUNCTIONS

When debugging that procedure,
it turned out that the calling order of functions was (1) subform Form_Resize → (2) parent form Form_Resize → (3) getSizeType.

SOLUTION

1. MODIFICATION OF USER DEFINED FUNCTION

Since Form_Resize() cannot handle it, it is necessary to add Control.ColumnWidth = -2 to the processing of the user defined function.

So I've created a new function getSizeTypeForSubF01Tb() which wraps getSizeType(), and for comparison, created a new text box 'size type ver2' with having another control source.

1# control source formula
2=getSizeTypeForSubF01Tb([size],"txtSizeTypeVer2")

 1'******************************************************************************************
 2'*Function      :get size type for subF01
 3'*arg(1)        :size number
 4'*arg(2)        :control name
 5'*return        :size type
 6'******************************************************************************************
 7Public Function getSizeTypeForSubF01Tb(ByVal sizeNum As Long, ByVal ctlName As String) As String
 8    
 9    'Const
10    Const FUNC_NAME As String = "getSizeTypeForSubF01Tb"
11    
12    'Variable
13    Dim rtn As String
14    
15    On Error GoTo ErrorHandler
16    
17    getSizeTypeForSubF01Tb = ""
18    
19    rtn = Module_ManageFormControls.getSizeType(sizeNum)
20
21    'Reconfigure the column width
22    If SysCmd(acSysCmdGetObjectState, acForm, Form_F_01.Name) <> 0 Then Form_SubF_01.Controls(ctlName).ColumnWidth = -2
23
24    getSizeTypeForSubF01Tb = rtn
25    
26ExitHandler:
27
28    Exit Function
29    
30ErrorHandler:
31
32    MsgBox "An error has occurred and the macro will be terminated." & _
33           vbLf & _
34           "Func Name:" & FUNC_NAME & _
35           vbLf & _
36           "Error No." & Err.Number & vbNewLine & _
37           Err.Description, vbCritical, TOOL_NAME
38        
39    GoTo ExitHandler
40        
41End Function

As a result, Automatic column width adjustment now works for size type ver2 as well.

column width adjustment for size type ver2

2. ASSIGN THE LENGTH EXPLICITLY

Inside Form_Resize(), I hardcoded the column width numbers of some of control after the column width auto-adjustment loop.

 1Private Sub Form_Resize()
 2    
 3    'Const
 4    Const FUNC_NAME As String = "Form_Resize"
 5    
 6    'Variable
 7    Dim ctl As Access.Control
 8    
 9    On Error GoTo ErrorHandler
10    
11    'the loop
12    For Each ctl In Me.Controls
13        If ctl.ControlType = acTextBox Then
14            ctl.ColumnWidth = -2
15        End If
16    Next
17
18    'assign the column width of size type ver2 explicitly (more than 8 cm)
19    if Me.txtSizeTypeVer2.ColumnWidth < 8 * 567 then Me.txtSizeTypeVer2.ColumnWidth = 8 * 567
20    
21ExitHandler:
22
23    Exit Sub
24    
25ErrorHandler:
26
27    MsgBox "An error has occurred and the macro will be terminated." & _
28           vbLf & _
29           "Func Name:" & FUNC_NAME & _
30           vbLf & _
31           "Error No." & Err.Number & vbNewLine & _
32           Err.Description, vbCritical, TOOL_NAME
33        
34    GoTo ExitHandler
35        
36End Sub

The automatic adjustment is fine as well.

COMPARISON OF 1 AND 2

Method 1 doesn't increase the function number, but with width to be hardcoded,
make the tool need to be maintained when the string returned by getSizeType() changes.

Method 2 enables all the widths to be auto-adjusted,
but increases the function number and makes the processing of the control source a little complicated.

Translations:

"Japanese" |