Set Datasheet Column Widths - How? (1 Viewer)

kirkm · Jan 5, 2020

There's subforms on a Main Form, displayed in datasheet mode.
The column widths are wrong, and although I can adjust them (and Save) this does not stick. Next tine the Form is opened they're wrong again.

I googled for a solution and found this

1. Open the form in Datasheet mode
2. Right click on any one of the column titles to select the whole column and then click "Unfreeze All Fields"
You should be able to adjust the column and save the setting from this point.

But it doesn't work for me. Does anyone have another solution ?
Thanks.

vba_php · Jan 5, 2020

Kirk,

I ran on a test on what google told you, and it *did* work for me under these circumstances:

open the main form in *form* view
right click on any given field's title section in the subform
select *unfreeze all fields*
manually adjust the field by dragging the column left or right to desired width
repeat step #3.
close main form
reopen main form (changes are reflected)

make sure that "allow datasheet view" is set to "yes" for the subform.

kirkm · Jan 5, 2020

Hey Adam, caught your reply just as I was heading for bed, will try it tomorrow. Fingers crossed, many thanks.

MajP · Jan 5, 2020

You can do a lot in code
https://vb123.com/you-can-do-that-with-datasheets/

kirkm · Jan 5, 2020

Adam, I have Office 2010 and I'm not getting those results when following the steps you show. When you say "Open the main Form in Form view" do you mean do not choose Layout or Design view? If yes, that's what I'm doing - but it's columns in the subForms on the Main Form I'm trying to adjust. Is it should work, for some reason it's not. (And "allow datasheet view" is Yes)

mjP thanks for that link, I read through it and will try it.. It wants a query - this would be built from the sql that's the Forms Record Source? It goes onto say If you need to do the same task with a Form, it’s even easier: Just set all of the ColumnWidth properties to -2. No extra steps are needed. It is a subForm Form I have but I cannot find a column width property anywhere.

CJ_London · Jan 5, 2020

I cannot find a column width property anywhere

like a number of properties, the columnwidth property can only be accessed using VBA.

you could use code along the following lines to set the column width to fit the data

Code:

dim ctl as Control
for each ctl in me.controls 'or if code is on the mainform me.subformcontrolname.form.controls
    if ctl.controltype<>aclabel then ctl.columnwidth=-2
next ctl

the code needs to be run after data has been loaded - so suggest the load event of the subform but this does depend on how your form/subform works - perhaps no data is loaded initially or you have wildly different values from one mainform record to the next, in which case suggest use the mainform current event

isladogs · Jan 5, 2020

Try this. I use it in a form where the subform source object is updated depending on a combobox selection:

Code:

    Dim ctrl As control
    For Each ctrl In Me.SubFormControlName.Form.Controls
    On Error Resume Next 'control does not have a columns width property, so move on
        ctrl.ColumnWidth = -2 '-2 equivalent to double clicking on the column header right edge
    Next ctrl

EDIT: Sorry - CJL posted the same idea whilst I was typing

CJ_London · Jan 5, 2020

no problem - variation on a theme

isladogs · Jan 5, 2020

Yup - totally different - you used ctl and I used ctrl!

For info, it also works if you omit the .Form part of the code which surprised me

CJ_London · Jan 5, 2020

well you used on error resume next, I excluded labels

kirkm · Jan 6, 2020

Wow, thanks very much CJ and isladogs, I have that working - on two subforms showing a great improvement.
If I wanted to a specify the actual width of a column can I use the same technique, something like "ctlColumnWidth(4)= 20" ?

CJ_London · Jan 6, 2020

yes, but in vba you use twips. There are 1440 twips to an inch, 567 to a cm

edit - the (4) won't work

ctlColumnWidth(4)

kirkm · Jan 6, 2020

Sorry I meant to say the width of individual columns, and the 4 meant column 4. Must all column thus be the same width?

CJ_London · Jan 6, 2020

no. The code provided dynamically resizes all the controls depending on the contents of the field identified in the control source.

you need to identify columns by name (you can by number, but it may not be the control you expected). e.g.

me.controlnameX.columnwidth=700
me.controlnameY.columnwidth=2000
me.controlnameZ.columnwidth=800
etc

or if you want to use numbers

me.controls(1).columnwidth=700
me.controls(2).columnwidth=2000
me.controls(3).columnwidth=800
etc

kirkm · Jan 6, 2020

Thank again CJ I have that working too. Is there a list of other options like "columnwidth"? Maybe one to center align the text in the field or (this would be good!) specify the font/size to use?

CJ_London · Jan 6, 2020

in the vba window, when you type the name of the control followed by a . most of the properties will appear in a list. There are one or two 'undocumented' ones for some controls, but none come to mind at the moment.

To get a full list, google/bing it for the type of control. One of the techniques for homing in on what you are looking for is to precede your search string with 'access vba' e.g.

access vba textbox

or

access vba combobox

will return a number of hits which will either take you to the right place or a page which has links to do the same. A lot of properties are common to all or most controls, some are unique to a particular type of control.

Or make a note of this link https://docs.microsoft.com/en-us/office/vba/api/overview/access and open up the object model dropdown on the left

Helps to get to know terminology and be aware some properties only work in certain situations - can grow/shrink for example only applies when printing.

with datasheets, font size/weight applies to the whole form. Text align applies to the column and is the same property as used for the control (textalign)

vba_php · Jan 6, 2020

CJ_London said:
in the vba window, when you type the name of the control followed by a . most of the properties will appear in a list.

thanks CJ. Had no idea about that.

CJ_London said:
One of the techniques for homing in on what you are looking for is to precede your search string with 'access vba' e.g.

access vba textbox

or

access vba combobox

for non-VBA issues, I will use the search string *ms access + [issue topic].* for VBA issues specifically, I almost do what you do CJ, and search *ms access vba [issue topic]*

kirkm said:
Is there a list of other options like "columnwidth"? Maybe one to center align the text in the field or (this would be good!) specify the font/size to use?

do you do any front end web development, Kirk? That makes it sounds like you're confusing CSS styling with what Access offers....

sxschech · Jan 7, 2020

If someone is interested.

Here was some code I had for adjusting col widths based on approximation of number of characters. This was based on 8 point calibri, so would need modification for a different type face or point size.

Code:

    Const TWIPSTOINCHES = 1440
    Const TWIPSTOCHARWIDTH = TWIPSTOINCHES * 0.06
'    'TWIPS times (number of inches for 1 character at 8 point Calibri)
    Me.txtSearchTerm.ColumnWidth = TWIPSTOCHARWIDTH * 25 'desired character width of 25 characters

Set Datasheet Column Widths - How? (1 Viewer)

kirkm

Registered User.

vba_php

Forum Troll

kirkm

Registered User.

MajP

You've got your good things, and you've got mine.

kirkm

Registered User.

CJ_London

Super Moderator

isladogs

MVP / VIP

CJ_London

Super Moderator

isladogs

MVP / VIP

CJ_London

Super Moderator

kirkm

Registered User.

CJ_London

Super Moderator

kirkm

Registered User.

CJ_London

Super Moderator

kirkm

Registered User.

CJ_London

Super Moderator

vba_php

Forum Troll

sxschech

Registered User.

Similar threads

Users who are viewing this thread