Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Unanswered: Adjust table/query column width by code

    Hi all, I have an urgent issue, any help will be greatly appreciated.

    In access, if you double-click on the right edge of a column in a table or a query, the column will automatically adjust its width to accomodate the widest data available, so the field would display completely. What I need is to be able to call this option by code, or to simulate it by code. Imagine, this is a definite requirement by the project sponsor
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  2. #2
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Wink Long_Text.Width

    What kind of control do you want to adjust the width of?
    If it's a text box you can try this:
    On "Double Click" event, use this code

    Me.Long_Text.Width = Len(Me.Long_Text) * 70

    Where "Long_Text" is the name of the text control.
    "70" is depending on font size.

    On "Lost Focus" you may reset the width of the control

    Sincerely
    Vestigo

  3. #3
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    Thanks vestigo, but this is not exactly what I need.

    I need to adjust the width of a column in a subform in datasheet view, or a query which is used as a source object for a subform (gee I know using queries/tables as source objects for subform is not a good approach, but there are reasons for it).
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  4. #4
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    Here's an example of what I am talking about.
    Attached Thumbnails Attached Thumbnails column_width.bmp  
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  5. #5
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    So, any ideas, anyone???
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  6. #6
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Red face It's possible using Continuous form

    Hi.
    I don’t think its possible to do what you like using a subform in datasheet view.
    If you use a subform in Continuous view it will look exact like.
    You will then have the possibility to do what you like.

    Send me your e-mail address and I mail you an example.

    Sincerely
    Vestígő

  7. #7
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Question

    I cannot use the continuous form because I also need to show some of the columns and hide other ones, and if I try to make this form through a continuous form -- it will be a real pain.

    OK, another question. Access has a built-in "Best fit" feature for automatically adjusting the table / subform datasheet column width to accomodate the widest data in this field (see attached screenshot). Is there an acCmdSometing or any other way to simulate this through VBA? I've looked though a ton of pages on the internet and there doesn't seem to be an answer for it
    Attached Thumbnails Attached Thumbnails Best_Fit.bmp  
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I know that the posts haven't been massively helpful for you so far - and I'm afraid this isn't much different, however with the DAO field object there is a ColumnWidth property - perhaps you could use this? I can't offer any more because I have never used it however it might provide an avenue of investigation perhaps? Not sure how you measure the width of your table data either as capitalization etc would be a factor.

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I have been watching this post closely because I am sure there is a way for some VBA to check the length of some text in a column and then to adjust the column by that width plus a few twips. I dont know how to do it, but I am very interested in knowing.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, Vestigo might be on to something - you could simulate the look of datasheet view with continuous forms. You'd be able to control the size of the textboxes and labels (relatively!) easily.

  11. #11
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    Hiding columns in a continuous form is no problem and as "pootle flump" says; it's relatively easily.

    sincerely
    Vestigo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •