How to Change Column Widths in Datasheet View of Form?

[UNC_Access]

Hi!

I am using Access 2013 (but I assume that the solution for Access 2010 will apply).

Here is the situation:

1. I have a simple datasheet form with three columns

2. I need to adjust the column widths so that all the text is visible

3. When I adjust the column widths, save the form, close the form, and re-open the form, the column widths reset

4. I need the column widths to either 1) stay the same as they previously were, or 2) be wide enough to fit all the text in each field

5. I have no idea where to enter VBA (if VBA is even necessary necessary)

Thanks!

 

[David R]

The trick here is ColumnWidth = -2, which tells the datasheet to resize to Optimal. http://msdn.microsoft.com/en-us/library/office/aa224081(v=office.11).aspx

You can do this from Form_Load (you could write a loop, but for just three columns it's not worth it)

Private Sub Form_Load()
    Me.Column1Name.ColumnWidth = -2
    Me.Column2Name.ColumnWidth = -2
    Me.Column3Name.ColumnWidth = -2
End Sub

 

[UNC_Access]

Thanks David!

However, I did not work.

I copied and pasted the VBA code exactly as you have written it, except I changed the column names, as follows:

Private Sub Form_Load()
    Me.[Level 01 and 02 ID].ColumnWidth = -2
    Me.[Level 03 ID].ColumnWidth = -2
    Me.[Case].ColumnWidth = -2
End Sub

I'm probably missing something obvious. Can you help?

Thanks!

 

[David R]

Did you click the [...] button at the end of the Load event, before you copy-and-pasted? You have to tell Access that there's an event procedure to run, and if you just open the VBA window raw it won't always notice. Good news is you can 'associate' your pasted code with the event after the fact, just go back to your Properties window.

Other than that it looks good...

 

[UNC_Access]

Hi David!

Works perfectly!

At first I could not find the On Load event, but I realized that the event is on the Form Selection type of the Property Sheet.

I figured I would add that note just in case someone searches the forums in the future!

Thanks a lot!

 

[David R]

Yep, events can be a little tricky to find at times (some are form-specific, some are control-specific). Glad you got it working.

 

[UNC_Access]

Thanks! Appreciate the help!

 

[js84]

As I had the same problem yesterday, I have tested the behavior with Access 2013, 2010 and 2003: if a form with datasheet view does reset column width after saving it, the form object seems to be corrupted. Saving manually adjusted columns of a corrupted form is not possible in all Access versions tested above.

My solution was: create a new blank form, set transfer all controls with copy & paste to the new blank form, set RecordSource, copy & paste all event procs to the new form, set activation order, adjust columns in datasheet view of the cloned formed as needed - and save it.

 

[BMiles13]

I found the following to work for me:

1. [FONT=&quot]Open the form that has all of the subforms on it.[/FONT]
2. [FONT=&quot]re-arrange or size the fields in the subforms as you wish them to be[/FONT]
3. [FONT=&quot]click on (highlight) one of the columns in the subform[/FONT]
4. [FONT=&quot]on the ribbon, in the text formatting group, center this column (or change any attribute in the text formatting group), then change it back to left justified (or Bold and then un-bold the subform)[/FONT]
5. [FONT=&quot]Now - close the form, you will be prompted to save, do so[/FONT]

Your subform should now have all of your changes.
Has worked for me without fail

 

[nhtuan]

Thanks David!

However, I did not work.

I copied and pasted the VBA code exactly as you have written it, except I changed the column names, as follows:

Private Sub Form_Load()
    Me.[Level 01 and 02 ID].ColumnWidth = -2
    Me.[Level 03 ID].ColumnWidth = -2
    Me.[Case].ColumnWidth = -2
End Sub

I'm probably missing something obvious. Can you help?

Thanks!

It works perfectly, thank you

 

[pflipper]

Excellent solution. This helped me resolve a long standing issue with a subform that had as it's source a query. I could not figure out how to stretch the columns to fit the longest piece (data or field name) until now. Solution for me was to get a count of the fields in my query (x), then use the count to loop through the subform Controls(x).ColumnWidth and set the value to -2. Perfecto!