autoexpand column values not save in access 2016

[TonC]

Hello,

Recently I thought I had a solution to automatic change the columswidht in a subdatasheet form.
With great help for VBAX Regular it worked fine. But now access 2016 hold not the values when I go to another form, or close and reopen the DB

In other words “Access 2016 column width resizing does not stay saved”
I already did look for a solution on the internet, but I cannot find anything.
Is there an answer to solve this problem.

[June7]

Provide your code for analysis.

[TonC]

Hello June7,


Option Compare Database
Option Explicit

Private m_columnWidth As Long

Private Sub Datum_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 43 ' Plus key
KeyAscii = 0
Screen.ActiveControl = Screen.ActiveControl + 1
Case 45 ' Minus key
KeyAscii = 0
Screen.ActiveControl = Screen.ActiveControl - 1
End Select
End Sub

Private Sub Form_Load()
'save the original column width to a variable
m_columnWidth = Me.Omschrijving.columnwidth
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateVandaag", acViewNormal
DoCmd.SetWarnings True
Me.Requery
End Sub

Private Sub Omschrijving_Click()
' auto size Omschrijving column
Omschrijving.columnwidth = -2
End Sub

Private Sub Omschrijving_GotFocus()
'auto size Omschrijving column
Omschrijving.columnwidth = -2
End Sub

Private Sub Omschrijving_LostFocus()
' re-instate the column width
Omschrijving.columnwidth = m_columnWidth
End Sub

Here is also other code in my subform (datasheet) but these lines of code is not relevant for my question,
Private Sub Datum_KeyPress(KeyAscii As Integer
Private Sub Form_Open(Cancel As Integer

[June7]

Could provide your db and I will test it in Access 2021.

LostFocus event restores width to what it was when form first opens. What setting do you expect to be saved?

[TonC]

When I open the subform in datasheet mode, the column "Ömschrijving" has a certain widht. When I move my cursor to the column, the column expand to the whole text within that field
mostlly expand to the longest text in column.
Then when I go with my cursor to another column than should the column widht "Omschrijving" shrinking to the old value for columnwidt

[June7]

When subform is one of several on pages of tab control, must be the one that gets focus when main form opens. Otherwise, code does not work.

Confirmed same behavior in 2010 and 2021 versions.

[arnelgp]

what does Datum_Keypress() even do?

ActiveControl is Not a Number, it is a Control. so obviously the code is wrong.
You can try to elimitate both the Click and Gotfocus event and just add code to it's Enter event:


Private Sub Omschrijving_Enter()
Me.Omschrijving.ColumnWidth = -2
End Sub


Private Sub Omschrijving_Exit(Cancel As Integer)
Me.Omschrijving.ColumnWidth = m_columnWidth
End Sub

[Gasman]

Here is what I use for a combo in a subform of mine.


Private Sub cboFoodIDFK_GotFocus()
' Debug.Print Me.cboFoodIDFK.ColumnWidths
Me.cboFoodIDFK.Dropdown
TempVars("FoodWidth").Value = Me.cboFoodIDFK.Width
' Debug.Print "GotFocus " & TempVars("FoodWidth").Value
Me.cboFoodIDFK.Width = "8000"
Me.cboFoodIDFK.ColumnWidths = ExpandCombo(Me.cboFoodIDFK.ColumnWidths)
End Sub


Private Sub cboFoodIDFK_LostFocus()
' Debug.Print "LostFocus " & TempVars("FoodWidth").Value
Me.cboFoodIDFK.Width = TempVars("FoodWidth").Value
Me.cboFoodIDFK.ColumnWidths = ShrinkCombo(Me.cboFoodIDFK.ColumnWidths)
End Sub

Public Function ExpandCombo(pstrWidths As String) As String
Dim strWidth() As String, strNewWidth As String
Dim i As Integer
strWidth() = Split(pstrWidths, ";")
' For i = 0 To UBound(strWidth)
' Debug.Print strWidth(i)
' Next
strWidth(1) = CStr(Val(strWidth(1)) * 1.5)
For i = 0 To UBound(strWidth)
strNewWidth = strNewWidth & strWidth(i) & ";"
' Debug.Print strWidth(i)
Next
' Debug.Print strNewWidth
ExpandCombo = Left(strNewWidth, Len(strNewWidth) - 1)
' Debug.Print "Expanding " & ExpandCombo
'MsgBox ExpandCombo
End Function


Public Function ShrinkCombo(pstrWidths As String) As String
Dim strWidth() As String, strNewWidth As String
Dim i As Integer
strWidth() = Split(pstrWidths, ";")
' For i = 0 To UBound(strWidth)
' Debug.Print strWidth(i)
' Next
strWidth(1) = CStr(Val(strWidth(1)) / 1.5)
For i = 0 To UBound(strWidth)
strNewWidth = strNewWidth & strWidth(i) & ";"
' Debug.Print strWidth(i)
Next
ShrinkCombo = Left(strNewWidth, Len(strNewWidth) - 1)
' Debug.Print "Shrinking " & ShrinkCombo
' MsgBox ShrinkCombo
End Function

[Anna44]

Hey Unfortunately, Access 2016 doesn't save column widths in Datasheet view when you close and reopen the database. You can try to save the column widths with VBA code, save them to a table, and then reset them when you open the form. Another option is to check for Access updates, as sometimes newer versions fix these types of issues.

[TonC]

Hi Anna,
Your joking me, to give me a comment in a language I do not speak.
but I apreciate your effort to help me.
Bay Bay

[June7]

Right, makes us do the translation when poster should have done that. So according to Google:

Hello Unfortunately, Access 2016 does not save column widths in datasheet view after closing and reopening the database. You can try saving the column widths using VBA code, storing them in a table, and then resetting them when the form is opened. Another option is to check for updates to Access, as sometimes newer versions resolve these types of issues.