ColumnWidth question.

[Vigilante001]

Hello,

?:: Are there instances where the ColumnWidth property of a textbox & combobox cannot be changed through VBA?

I have a form with two buttons (save & load) and a sub-form (30+ columns). When the save button is pressed, I have a VBA sub pull the name, sequence, and width of all rows and store them in a table. The code is below:

Private Sub btnSaveRememberFieldParameters_Click()
    
    '****************************************************************************
    'Ensure table is present
    '****************************************************************************
    Dim tblDef As TableDef
    Dim SQL As String
    Dim SQL2 As String
    Dim varCreateTable As String
    
    
    varCreateTable = "Create"
    
    
    'Look for tblUserOptions
    For Each tblDef In CurrentDb.TableDefs
        If tblDef.Name = "tblUserOptions" Then
            varCreateTable = "Flush"
        End If
    Next tblDef
    
    
    'If table was found...flush values. If not found, create it.
    If varCreateTable = "Flush" Then
        If MsgBox("This action will replace your prior saved settings. Proceed?", vbYesNo, "Confirm Overwrite") = vbYes Then
            'Flush user values
            SQL = "DELETE * FROM tblUserOptions WHERE OptionCategory = 'FormWidthSettings';"
            DoCmd.SetWarnings (WarningsOff)
            DoCmd.RunSQL SQL
            DoCmd.SetWarnings (WarningsOn)
        Else
            Exit Sub
        End If
    End If
 

    '****************************************************************************
    'Record the Type, Name, Width, and Order of all objects
    '****************************************************************************
    Dim ctlType As String
    Dim ctlName As String
    Dim ctlWidth As String
    Dim ctlSequence As String
    
    'Run through all objects
    For x = 0 To Forms![DailyActivityES&S]!DailyActivitySubform.Controls.Count - 1
            
            'These properties apply to all controls
            ctlType = TypeName(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x))
            ctlName = Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).Name


            'These properties apply to only TextBox & ComboBox controls
            If TypeName(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x)) = "TextBox" Then
                
                ctlWidth = Nz(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).ColumnWidth, "")
                ctlSequence = Nz(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).ColumnOrder, "")
    
                SQL = "INSERT INTO tblUserOptions (OptionCategory, OptionCategoryDate, ControlType, ControlName, ControlWidth, ControlSequence) " & _
                      "VALUES ('FormWidthSettings', '" & Format$(Date, "mm/dd/yy") & "', '" & ctlType & "', " & _
                      "'" & ctlName & "', " & _
                      "'" & ctlWidth & "', " & _
                      "'" & ctlSequence & "');"
                DoCmd.SetWarnings (WarningsOff)
                DoCmd.RunSQL SQL
                DoCmd.SetWarnings (WarningsOn)
            
            ElseIf TypeName(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x)) = "ComboBox" Then
            
                ctlWidth = Nz(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).ColumnWidth, "")
                ctlSequence = Nz(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).ColumnOrder, "")
    
                SQL = "INSERT INTO tblUserOptions (OptionCategory, OptionCategoryDate, ControlType, ControlName, ControlWidth, ControlSequence) " & _
                      "VALUES ('FormWidthSettings', '" & Format$(Date, "mm/dd/yy") & "', '" & ctlType & "', " & _
                      "'" & ctlName & "', " & _
                      "'" & ctlWidth & "', " & _
                      "'" & ctlSequence & "');"
                DoCmd.SetWarnings (WarningsOff)
                DoCmd.RunSQL SQL
                DoCmd.SetWarnings (WarningsOn)
            
            Else 'Any other control type gives blanks for ControlWidth and Sequence
                SQL = "INSERT INTO tblUserOptions (OptionCategory, OptionCategoryDate, ControlType, ControlName, ControlWidth, ControlSequence) " & _
                      "VALUES ('FormWidthSettings', '" & Format$(Date, "mm/dd/yy") & "', '" & ctlType & "', " & _
                      "'" & ctlName & "', " & _
                      "'', " & _
                      "'');"
                DoCmd.SetWarnings (WarningsOff)
                DoCmd.RunSQL SQL
                DoCmd.SetWarnings (WarningsOn)
            
            End If
            
    Next x
    
    Forms!frmUserOptions!txtRememberFieldParametersSaved = "Last saved on: " & Nz(DLookup("OptionCategoryDate", "tblUserOptions", "OptionCategory = 'FormWidthSettings'"), "[Not Saved Yet]")
    Forms!frmUserOptions!btnLoadRememberFieldParameters.Visible = True
    
End Sub

Then, I resize/reorder the columns in the sub-form. When I press load, another sub applies the saved settings back to the sub-form columns, searching for textboxes & comboboxes. See here:

Private Sub btnLoadRememberFieldParameters_Click()

    '****************************************************************************
    'Apply object values to the new form
    '****************************************************************************
    
    If MsgBox("This action will replace your prior saved settings. Proceed?", vbYesNo, "Confirm Overwrite") = vbYes Then
       
        For x = 0 To Forms![DailyActivityES&S]!DailyActivitySubform.Controls.Count - 1
        
            ctlType = TypeName(Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x))
            ctlName = Forms![DailyActivityES&S]!DailyActivitySubform.Controls(x).Name
        
           If ctlType = "TextBox" Then
                ctlWidth = DLookup("ControlWidth", "tblUserOptions", "ControlName = '" & ctlName & "'")
                ctlSequence = DLookup("ControlSequence", "tblUserOptions", "ControlName = '" & ctlName & "'")
                
                If IsNull(DLookup("ControlName", "tblUserOptions", "ControlName = '" & ctlName & "'")) = False Then
                    'Debug.Print ctlName
                    MsgBox (ctlName & vbCrLf & vbCrLf & "Width Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth & vbCrLf & "Width Future: " & ctlWidth & vbCrLf & vbCrLf & "Sequence Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder & vbCrLf & "Sequence Future: " & ctlSequence)
                    
                    Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth = ctlWidth
                    Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder = ctlSequence
                    
                    MsgBox (ctlName & vbCrLf & vbCrLf & "Width Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth & vbCrLf & "Width Future: " & ctlWidth & vbCrLf & vbCrLf & "Sequence Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder & vbCrLf & "Sequence Future: " & ctlSequence)
                End If
            End If
            
            
            If ctlType = "ComboBox" Then
                ctlWidth = DLookup("ControlWidth", "tblUserOptions", "ControlName = '" & ctlName & "'")
                ctlSequence = DLookup("ControlSequence", "tblUserOptions", "ControlName = '" & ctlName & "'")

                If IsNull(DLookup("ControlName", "tblUserOptions", "ControlName = '" & ctlName & "'")) = False Then
                    'Debug.Print ctlName
                    MsgBox (ctlName & vbCrLf & vbCrLf & "Width Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth & vbCrLf & "Width Future: " & ctlWidth & vbCrLf & vbCrLf & "Sequence Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder & vbCrLf & "Sequence Future: " & ctlSequence)
                    
                    Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth = ctlWidth
                    Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder = ctlSequence
                    
                    MsgBox (ctlName & vbCrLf & vbCrLf & "Width Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnWidth & vbCrLf & "Width Future: " & ctlWidth & vbCrLf & vbCrLf & "Sequence Current: " & Forms![DailyActivityES&S]!DailyActivitySubform.Controls(ctlName).ColumnOrder & vbCrLf & "Sequence Future: " & ctlSequence)
                
                End If
            End If
        
        Next x
    
    Else
        Exit Sub
    End If

    ' Here I am!
    
End Sub

The sequence part works for all columns, but the ColumnWidth works for all columns but five. Four of them are multicolumn comboboxes, and the other is an Autonumber. This works for other mulitcomboboxes, and I wasn't sure if there were instances where the ColumnWidth property cannot be changed.

Thanks

-V

 

[DCrake]

From what I gather I can't decide whether the width of the control is determined by the content of the control or a known width, or even something else.

If the arrangement and widths can be predetermined dependig on the type of record chosen in the main form. Then why not have several subforms on the main form stacked up on top of each other and make the relevent on visible and other invisible to the user.

Just a thought

David

 

[Vigilante001]

DCrake:

Thank you for the reply. The way it works is this... the user has their subform columns customized the way they want it through dragging the column's head. Whenever I send an update to the main form, though, it wipes out the users subform columnwidths. So, using the code the the person can save their column widths to a table, update (all names of forms, subforms, and columns remain the same), and then click load to re-apply those settings. I know this seems very mundane, but you would be surprised how finicky my office of older ladies get when they have to spend 30 minutes resizing their columns.

An image of the program is attached... ... along with what the tblUserOptions looks like... View attachment tblUserOptions.xls

In particular, ES1, S1, ES2, S2, and XID are the fields whose widths do not respond to the load code. Their sequence does.

Thank you for looking!

-V

 

[DCrake]

Ok,

For you autonumber field I would have this as a fixed width as this is a read only field. Alternatively you could create a field that looks at the autonumber with leading zeros and as this is a text field, again read only, you could resize this one.

As for your multi column combo box are the column widths set at fixed widths and is the list width set to auto? If you set the list width to the same size as the total column widths, and some (to contend with the vertical scroll bar), this could be done. I can't se why users would want to resize individual column widths of the combobox.

David

 

[Vigilante001]

DCrake:

You're right, the users never manipulate the columns internal to the combobox, but they do change the width of the combobox itself either to match the contents of the cell (20%) or moreover to condense the columns for better visibility (80%). I'll try deleting the cells ES1,2 and S1,2, and will use your suggestion of keeping the XID read-only. (It has been, but I'll tweak the code to leave it alone.

Thanks for your help! Hope your Thanksgiving was filling.

-V

 

[Vigilante001]

I fixed the error, though I have no idea why it works...

I created a new combobox for the ES1,S1,ES2,S2 fields and applied the same rowsource, name, internal column widts, etc, so that it was an exact replica, and now the columnwidth VBA statement can change them. What's up with that?

So, moral of the story, if a control isn't behaving like you know it should, try creating a replica control and retiring the old one.


Thank you again for your help!

-V