How do I set size of colums in a table subform datasheet view in MS Access

Question

I have a subform bugging me. The mainform contains buttons etc. Everytime the user close/open the form, the columns width is reset to whatever the table likes. If i open the table directly, the size is as i want. If i change it in the subform, it is not saved. (See screendump)

I would like "Phase" to stay about 2 cm width. I can enter the subform and edit the "Width" but that is only applied to the other views.

I hope you can help, Emil.

Solution 1

I realize this post is almost 2 years old, but I ran into the same problem and came across this post.

I am running MS Access 2013 on Windows 7 Ultimate. I did not find the solutions offered here to work for me, so, I set out to find something that would work for me before I went to VBA code. (Incidentally, I appreciate the link offered by @Patrick_Honorez in his comment on the original post because that was going to be my fall-back solution.)

Anyway, here is what worked for me and I hope perhaps it will work for others as well:

1. Open the parent form.
2. In the subform, right-click the column header for the column for which you wish to adjust the width.
3. Select the “Field Width” item from the context menu.
4. In the “Column Width” dialog that appears in step 3, enter the desired column width in points, or, use the [Best Fit] button. Then click the [OK] button to close the dialog and return to the form.
5. Right-click the parent form’s border to bring up the parent form’s context menu. Click the “Save” item in the context menu.
6. Now close the parent form.
7. The next time the form is loaded, the column widths should be as set it step 4 above--at least they are for my setup.

Solution 2

I know this is late to the party and most likely going to be the last comment anyone reads, but this can be done quite simply in MS Access 2016 - by someone like myself who has no more than 4 days experience in databasing overall and no experience with writing custom Macro's or VB Script (using only what is native to MS Access).

Here's how I did it.

Scenario - Split Form (Form + Datasheet).

Extra Recommendations:

1. It pays to be across all properties of every object type in your database, as a change in a field property can cause unpredictable erratic effects, which take ages to figure out why it happened and how to stop it from happening again, whilst delivering your desired outcome.

2. Me.Requery in your VBA script after every necessary event and also in your main form (generally the 'After Update' event is used most), and be wary that too many Me.Requery's (in unnecessary events) can also be detrimental - so too much of a good thing can be a bad thing.

Bottom Line Up Front - Modify the format of your query that is to be exported/printed.

1. In 'Design View' of the query you are concerned with, ensure that the fields are in the order you need them outputted in first as this is exactly how the macro will present them for export/print (example could be "Australia" then "Northern Territory" then "Town's In The Northern Half Of The State" then "Darwin" then "Suburbs In The Northern Half Of City").

2. Switch to 'DataSheet View' in the same query and use the top left folded over triangle looking thingy to highlight the entire data set then right click that same triangle to present an options menu. Select 'Row Height' and type in "15" (default row height in Excel).

3. Deselect the entire spreadsheet and this time select every column (left click the left most column, hold shift button, scroll over to the right to the far end of the data set and click the last column) and then right click one of the highlighted columns to present another menu. Select 'Field Width' and within that new pop-up menu select 'Best Fit' and then 'OK'.

4. (Optional - not sure if this helps or hinders but I did it for my purpose) With the columns still selected right click one of the highlighted columns again and select 'Freeze Fields'.

5. My scenario had buttons with macros configured to run filtered reports so I was able to check this by simply clicking any of those buttons and seeing the report formatting, which it held true to the work I had just done. I exported using another button with a macro that exports to Excel with 'Print Formatting' selected (my main form also had the datasheet view as the only thing that could be printed and was also set in 'Print' formatting.

The Excel spreadsheet opened with all row heights and column widths in a way that I could read every field/record with perfect ease and without extra modification.

This also worked for cascaded combo boxes, with the export only outputting the 'drilled down/filtered' datasheet records, in a format that required no further modifications.

Hope this helps someone, as its saved my hide! :)

Solution 3

I see this post is quite old and OP must have figured someway to tackle the issue. I came across same issue today and found solution on this link. For anybody else having same issue, use following code (I modified the code a little because original code from the above mentioned post saves column width of only text boxes but my form has combo boxes too, column width of which was not getting saved) in close and open events of your subform and then open main form in Form View and then manually select desired widths either by mouse, by entering column width value or using best fit. Save the form and reopen to check results. That's it.

Private Sub Form_Close()
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        If (ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox) Then
            SaveSetting "propertiesDBS", Me.Name, ctrl.Name, ctrl.ColumnWidth
        End If
    Next
End Sub


Private Sub Form_Open(Cancel As Integer)
Dim ctrl As Control
Dim frm As Form
Dim w As Long
    For Each ctrl In Me.Controls
        If (ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox) Then
            w = GetSetting("propertiesDBS", Me.Name, ctrl.Name, 0)
            If w <> 0 Then ctrl.ColumnWidth = w
        End If
    Next
End Sub