×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Resizing column to best fit vba

thread702-1490932

Resizing column to best fit vba

Resizing column to best fit vba

(OP)
Hi, I create the table in access by importing from a text document via this code:

DoCmd.TransferText acImportDelim, "Import Specs", fdr, CurrentProject.path & "\" & fdr & ".csv", True

How do I auto resize the columns to best fit such that the data from every cell is displayed fully.  Thanks!

RE: Resizing column to best fit vba

I would wonder why you would bother with doing that anyway, as surely your table is not the way you are viewing the data, or allowing the data to be viewed on a regular basis.

But if you want to do that, you might could do something to this extent... you'd have to fill in the details:

CODE

Private Sub Import()
  Dim db as DAO.Database
  Dim tdf as TableDef
  Dim x as Integer 'Count fields
  Dim y as Integer 'Counter to loop through fields
  Set db = CurrentDb

  DoCmd.TransferText acImportDelim, "Import Specs", fdr, CurrentProject.path & "\" & fdr & ".csv", True
  
  Set tdf = db.Tabledefs(fdr)
  
  'get the count of the fields
  'Something like:
  'x = tdf.Fields.Count - but I don't remember for sure
  
  For y = 1 to x
    db.ModifyTable(tdf)
    tdf.Fields(y).Width = MaxLengthOfAnyRecordWithinField
    tdf.Update
  Next y
  
  Set tdf = Nothing
  db.close
  Set db = Nothing
End Sub

As you can see, lots to fill in, and probably some of it is not 100% correct on terminology, as I just typed here... didn't test in VBA window.

--

"If to err is human, then I must be some kind of human!" -Me

RE: Resizing column to best fit vba

(OP)
Thanks for the quick reply but I'm a new user who does not understand the syntax of that code at all.  I c/p into my code and it doesn't work.

RE: Resizing column to best fit vba

Well, my advice is to just not worry about it.  You shouldn't have to resize the columns, as you shouldn't be using the table as the way you look at the data in most situations, anyway.

Create a customizable user form instead, that'll work much better.

--

"If to err is human, then I must be some kind of human!" -Me

RE: Resizing column to best fit vba

(OP)
Well to view data, I use the form to put the data and the data executes a query from the table.  If the table's columns aren't resized, the data will not show completely and the user will have to resize manually.  I just wanna get around this problem.

RE: Resizing column to best fit vba

So, you're saying you view the data in the form, correct?  Otherwise, I'm not 100% sure I follow exactly what you mean.

--

"If to err is human, then I must be some kind of human!" -Me

RE: Resizing column to best fit vba

(OP)
The user inputs are taken from a form and then the form executes a query based on the input which selects data from the table and the data is displayed with the table's column width.

RE: Resizing column to best fit vba

So the form executes a query is what your saying...

A user opens the form, selects/types some data, then clicks a button that opens a query that is based on the inputs.

You could just open the query in a subform, or a separate form, instead of directly in the query, as well, so that you could have more control over the appearance as well.

--

"If to err is human, then I must be some kind of human!" -Me

RE: Resizing column to best fit vba

(OP)
Okay, let's say I want to execute a query named query1.  How would I do that in a subform?

RE: Resizing column to best fit vba

You create a subform, say on the bottom of your main form.  Then set the subform's data source to your query as a start.  Then, you customize your subform to appear in whatever way you want/need.

If the query is always a brand new query, and not an existing query, in other words a query is actually created each time, and not just opened, then you'll need to change the data source each time using code.

--

"If to err is human, then I must be some kind of human!" -Me

RE: Resizing column to best fit vba

I played with this code and works well, here is the relevant code so you do not have to dig through the article.

Quote:


Resizing columns

The ability to resize columns programatically to display the data currently in the column is the original reason I wanted to write this article: It's just an amazing feature. The first place I ever saw it was in Access's Normalizer, better known as the Access Table Analyzer (the names of all of the wizard's objects are still prefixed with "NORM_" because the marketing change to "Table Analyzer" didn't force any code changes!). The Normalizer creates a query using DAO, and then it sets the columns of the resulting datasheet to be the appropriate width to display the data in the columns. And remember, tables and queries in Access are displayed using datasheets (at least, when you have them in browse view), which are forms.

The trick to resizing columns automatically to the data currently in them requires two steps:

   1. You must set the datasheet's ColumnWidth properties to -2, a number that seems to mean "best fit" to Access, internally. Access will then change the ColumnWidth property value to an appropriate number (in twips).
   2. To make the change permanent, you have to add a property called ColumnWidth to DAO's Property collection (which won't contain the property by default for all queries) and set the property's value to the same value as the control's ColumnWidth property from Step 1.

To do this, you can use my procedures FixColumnWidthsOfQuery or FixColumnWidthsOfTable and their helpful subroutine, SetDAOFieldProperty:

CODE

Public Function FixColumnWidthsOfQuery _
               (stName As String)
    Dim db As Database
    Dim qdf As QueryDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(stName)
    DoCmd.OpenQuery stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(qdf.Fields(ictl), _
      "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acQuery, stName
End Function

Public Function FixColumnWidthsOfTable _
                      (stName As String)
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
    
    Set db = CurrentDb
    Set tdf = db.TableDefs(stName)
    DoCmd.OpenTable stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(tdf.Fields(ictl), _
      "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acTable, stName
End Function

Private Sub SetDAOFieldProperty _
               (fld As DAO.Field, _
 stName As String, vValue As Variant, _
               lType As Long)
    Dim prp As DAO.Property
    
    For Each prp In fld.Properties
        If StrComp(prp.Name, stName, _
         vbBinaryCompare) = 0 Then
            prp.Value = vValue
            Exit For
        End If
        Set prp = Nothing
    Next prp
    
    If prp Is Nothing Then
        Set prp = fld.CreateProperty(stName, _
         lType, vValue)
        fld.Properties.Append prp
    End If
End Sub

Quote:


You can simply pass the name of any table or query to these routines and let them do the rest!

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close