How automatically expand column width in Access 2007 tables

Instead of having to highlight all the columns of each table in a DB, right click, and select column width best fit, Is there a parameter that I can set to have all my tables expand to "Best Fit" column widths?
SAbboushiAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
See this for some ideas:

http://msdn.microsoft.com/en-us/library/aa217449(office.11).aspx

Also ... this was some code from EE's JerryB ... that may work (I did not test yet):

Private Sub Form_Load()
Dim ctl As Control
For Each ctl In Me
If ctl.ControlType = 109 Then
ctl.columnWidth = -2
End If
Next
End Sub

mx
0
 
SAbboushiAuthor Commented:
Thanks - not looking for code.  And your link was filled with lots of stuff.  I appreciate you pointing me somewhere to do research, but my motive for posting is to AVOID doing the research.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
got it.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jeffrey CoachmanMIS LiasonCommented:
SAbboushi,

"Is there a parameter that I can set to have all my tables expand to "Best Fit" column widths?"

No.

1. In datasheet view the default column width is 1".
Autofit works by scanning all the records on the "Screen" (unlike Excel, that scans the entire sheet)
and determining the best column width.
2. This will not work in Tabular (continuous form) view because the fields are actually Controls arranged to look like a datasheet view.

If you are worrying about "Running the code", remember that MX's code will run automatically, silently when the form opens, so you never know it's there, so in that sense it funtions like a "Setting".
It can probably be made to work for all forms that get opened by creating a hidden form to check each new form in the forms collection. (or a similar system)

But if ultimate question is:
Is there a "Setting" in Access that will autofit all the columns in your tables (or queries), then the answer is: No.
:-(

JeffCoachman
0
 
SAbboushiAuthor Commented:
Thanks for the posts.  I am not using forms.  How can I get this to run automatically when the databse is opened for all datasheets?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If you are referring to tables, I don't think there is any way to do that - realistically.

mx
0
 
SAbboushiAuthor Commented:
Hi mx-
Not clear then on what that code is for... I thought that it was for the Datasheet view of a Table??
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I thought that it was for the Datasheet view of a Table??"

Well ...  indirectly.  It would be used in a Form that is in Datasheet view - which of course looks just like a table ...

mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
SAbboushi,

Forms should be the only way users can interact with your database for that very reason, you have more control over it.

If you created a Datasheet view sub form from your table and inserted it into a blank Main form,  you could insert a button on the main form to run the code. (and autofit the columns)

I am sure MX can help you implement this.

;-)

Jeff
0
 
SAbboushiAuthor Commented:
Thanks Jeff.  This application is for me - no users to worry about.  After 38 years of computer experience, I still shoot myself in the foot now and then-- but not enough for me to spend the extra time to bulletproof the apps that only I will use.

SO - against your guidance, if I am sticking with datasheets, is there a solution?  I wasn't clear on whether you were guiding me to do it a 'better' way, or whether you are saying it is not possible to set the column widths of all my tables when using Datasheet view.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"you are saying it is not possible to set the column widths of all my tables when using Datasheet view."

I won't say it's not possible such that when ... you have a table open ... that *maybe* you could run a macro with a hot key that would call a function that would use the TableDef object on the open table - and somehow resize ALL the columns to fit.  But ... it would take some digging to see if this could happen.  There is no way that I can imagine that ... just by open a given table - that it would automatically 'size to fit' all columns.

This sounds like a fun project for Jeff Twilley ... who I will ping ...

mx
0
 
SAbboushiAuthor Commented:
k thanks for the clarification and the help
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I do get what you want to do ... and it is a bit of a pain to always have to resize columns.

mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can you clarify what you mean by "datasheets", because you can have a datasheet view form, but tables really only have a Datasheet view.

So when you say "Datasheets" are you referring to a Form or a table?

If yopu are referring to automatically resizing all your table columns to AutoFit, then there is no way *that I know of* that this will be possible.

Again, I am not the be-all and end-all, but if it is possible, it might not be easy.

;-)

JeffCoachman

0
 
SAbboushiAuthor Commented:
Table Datasheets

I want an automated way to select all the columns, right-click, choose Column width, and select Best Fit
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
SAbboushi,

Again, you cannot "Automate" anything in a Table.

Is there a reason why you are using Tables, and not a form in Datasheet view?

As MX posted, this can be done quite easilly in forms, but not in tables.

Let's see what MX has to say...

:-)

JeffCoachman
0
 
SAbboushiAuthor Commented:
boag2000:
Thanks for the clarification.
Forgive me if I am about to be annoying... I am a newbie to Access and don't know the proper names of stuff and I don't know what I don't know...!

>> Again, you cannot "Automate" anything in a Table.
I underestand you to be saying that if I use Datasheet view to view a table, that there is no way to use code to alter the parameters of how the Table is presented in the Datasheet view.  Which strikes me as odd:  If I manually set the column width of a field in Datasheet view, I am prompted to save my changes to the layout of the Table.  So I believe you are telling me that the Table layout parameters for a Datasheet view are inaccessible by code?

>> Is there a reason why you are using Tables, and not a form in Datasheet view?
Probably ignorance on my part: if I can quickly and easily define a form that works just like the datasheet view (I can sort by columns, filter, jump to a row number, find) but I can also automate things like column width, then please tell me this is the way to go and show me how!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"that if I use Datasheet view to view a table,"
Actually, there is no other way to 'view' a table by itself.

"Which strikes me as odd: "
That's just how it is.  I've never seen any properties that would allow you to do this.

"then please tell me this is the way to go and show me how!"
This is easy, follow this drill:

Open a new form in design view.
Open the Form's property sheet. (Menu>>View>>Properties)
Click on the Format tab.
Set the following properties:
   Default View: Datasheet
   Allow Form View: No
   Allow Datasheet View: Yes

Now ... click on the Data tab, and into the Record Source property ... and select your Table from the drop down list.  This should open up another window that displays all the fields in your table.

Drag the fields you want onto the form.  The position and layout will not matter in Datasheet view, however the default tab order will be the order you put the controls on the form.  Again, don't worry about the layout or trying to make it pretty ... it has no bearing in Datasheet view.

Now, open your form in normal view ... and you will see it looks just like a Table !

mx
0
 
jefftwilleyConnect With a Mentor Commented:
Because a standard database table doesn't have an "On Open" event, you have to create the event by using some other means. This seems to work ok if you don't mind opening your table by clicking on a macro or a form button.  

Just as a side note here. Tables aren't meant to be "Formatted". They can be, obviously to some extent, however as has already been mentioned in this thread, forms are designed with the intention of making your data "Pretty".

I also wouldn't advise SendKeys unless you don't anticipate any kind of background application pop-ups from intervening in the process.

Enjoy!
J


Function FormatTableOnOpen()
'This function tabs through the columns in a table resizing them to the best fit.
Dim I As Long
Dim x As Long
I = CurrentDb.TableDefs("YourTable").Fields.count - 1
DoCmd.OpenTable "One", acViewNormal, acEdit
For x = 0 To I
    SendKeys "%(OCB)"  'This is opening the format menu, and sizing columns
    SendKeys "{TAB}"   'This is required to move to the next field
Next x
End Function

Open in new window

0
 
SAbboushiAuthor Commented:
jefftwilley:
Thanks - It looks like you provided what I asked for.  Is there not a way to select ALL columns with one operation?

boag2000: Thanks for helping me recognize that there is a Table Datasheet view and a From Datasheet view - and that

DatabaseMX: Thanks for showing me how.

NOW - back to boag2000's thought:
>> It can probably be made to work for all forms that get opened by creating a hidden form to check each new form in the forms collection. (or a similar system)

How can I do that?
0
 
jefftwilleyCommented:
I played around with the Select All option, however, the menu option to Size columns when all records are selected is not available. Tabbing through the columns was the only way I could find to achieve your goal.
J
0
 
SAbboushiAuthor Commented:
Thanks J - was just wondering!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.