Consulting

Results 1 to 11 of 11

Thread: autoexpand column values not save in access 2016

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    49
    Location

    autoexpand column values not save in access 2016

    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.

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    401
    Location
    Provide your code for analysis.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Jan 2016
    Posts
    49
    Location
    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
    Last edited by Aussiebear; 01-21-2025 at 01:11 AM.

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    401
    Location
    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?
    Last edited by June7; 01-03-2025 at 03:47 AM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    VBAX Regular
    Joined
    Jan 2016
    Posts
    49
    Location
    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

  6. #6
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    401
    Location
    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.
    Last edited by June7; 01-03-2025 at 01:54 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    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
    Last edited by Aussiebear; 01-04-2025 at 02:12 AM.

  8. #8
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    118
    Location
    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
    Last edited by Aussiebear; 01-04-2025 at 02:14 AM.

  9. #9
    VBAX Newbie
    Joined
    Jan 2025
    Posts
    1
    Location
    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.
    Last edited by Aussiebear; 01-29-2025 at 09:03 PM. Reason: Translated to English

  10. #10
    VBAX Regular
    Joined
    Jan 2016
    Posts
    49
    Location
    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

  11. #11
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    401
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •