[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How automatically expand column width in Access 2007 tables

Posted on 2009-02-10
22
Medium Priority
?
2,397 Views
Last Modified: 2013-11-29
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?
0
Comment
Question by:SAbboushi
  • 9
  • 7
  • 4
  • +1
22 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 23605375
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
 

Author Comment

by:SAbboushi
ID: 23605441
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
 
LVL 75
ID: 23605475
got it.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23608571
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
 

Author Comment

by:SAbboushi
ID: 23636035
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
 
LVL 75
ID: 23636057
If you are referring to tables, I don't think there is any way to do that - realistically.

mx
0
 

Author Comment

by:SAbboushi
ID: 23636096
Hi mx-
Not clear then on what that code is for... I thought that it was for the Datasheet view of a Table??
0
 
LVL 75
ID: 23636124
"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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23636568
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
 

Author Comment

by:SAbboushi
ID: 23636708
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
 
LVL 75
ID: 23636825
"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
 

Author Comment

by:SAbboushi
ID: 23636869
k thanks for the clarification and the help
0
 
LVL 75
ID: 23636915
I do get what you want to do ... and it is a bit of a pain to always have to resize columns.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23637208
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
 

Author Comment

by:SAbboushi
ID: 23637490
Table Datasheets

I want an automated way to select all the columns, right-click, choose Column width, and select Best Fit
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 600 total points
ID: 23639482
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
 

Author Comment

by:SAbboushi
ID: 23641400
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
 
LVL 75
ID: 23642016
"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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 600 total points
ID: 23645497
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
 

Author Comment

by:SAbboushi
ID: 23645862
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 23645991
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
 

Author Comment

by:SAbboushi
ID: 23646005
Thanks J - was just wondering!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question