Dynamically Size Width of DataGrid

Try this code instead:

J = datagrid1.columns.count
For K = J-1 To 0 Step - 1
   If DataGrid1.Columns(K).Visible = True Then
      DataGrid1.Width = DataGrid1.Columns(K).Left + DataGrid1.Columns(K).Width
      Exit For
   End If
Next K

No, didn't work.  This code truncates more recordsets than the original code.

The basic logic is fine.  Hmmm... try this variation to add the size of the lines between columns:

j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       DataGrid1.Width = DataGrid1.Width + DataGrid1.Columns(k).Width + Screen.TwipsPerPixelY
     End If
   Next k

No, didn't work.  This code truncates more recordsets than the original code.

Didn't mean to post the second "no, didn't work."  That was an accident.  I haven't tested the Twips code yet.  Sorry!

Okay, I tested the Twips code.  That didn't fix it.
Maybe there's a property that determines the width of the border of the grid.  I'll look.

Hope you need to do, some hardcoding like this for the border width, as your finding already.

DataGrid1.Width = DataGrid1.Width + 50 'or 100

Just test it with some hardcoding of borderwidth, and hope, it'd solve your problem.

Also check if the columns.width property returns including the borders width (I hope it returns incl. border width).

Try this out..


Call the function with following parms
 
Parms:  Xgrid (the control .. grid)
        Xform (the form)
        xMaxWidth (maximum width of the Grid)  
        xMaxMax (maximum width of container..
                 a frame or so)


Public Sub ResizeGridAndCols(xGrid As Control, xForm As    Form, xMaxWidth As Long, xMaxMax As Long)

    Dim intX As Integer
    Dim intY As Integer
    Dim intTempL As Long
    Dim strText As String
   
    Dim Total As Long
    Dim arrMaxlen() As Long
   
    On Error GoTo LogError
   
    ReDim arrMaxlen(0 To xGrid.Cols - 1)
   
    For intX = 1 To xGrid.Cols - 1
      arrMaxlen(intX) = 0
    Next intX
       
    For intX = 0 To xGrid.Rows - 1
      For intY = 0 To xGrid.Cols - 1
        strText = Trim$(xGrid.TextMatrix(intX, intY)) & "W "
        intTempL = xForm.TextWidth(strText)
        If intTempL > arrMaxlen(intY) Then
          arrMaxlen(intY) = intTempL
        End If
      Next intY
    Next intX
   
    For intX = 0 To xGrid.Cols - 1
      xGrid.ColWidth(intX) = arrMaxlen(intX)
      Total = Total + xGrid.ColWidth(intX) + 50
    Next intX
   
   If Total > xMaxMax Then
     xGrid.ScrollBars = 3
     xGrid.Width = xMaxMax - 75
   Else
     xGrid.Width = Total + 90
   End If
   
End Sub


Play around with it... it works good..

Zorxx
"May the source be with you"

'Determine width of DataGrid based on sum of widths of visible columns
 DataGrid1.Width = 0
 DataGrid1.DefColWidth = 0 'Column width will be size of field name or
                           'Field length, whichever is larger
 j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       DataGrid1.Width = DataGrid1.Width + DataGrid1.Columns(k).Width
     End If
   Next k
  ' Add the width of the RecordSelector Column
  ' Width of Record Selector column is 310
  DataGrid1.Width = DataGrid1.Width + 310

cnealy, try this one:

    Dim dblWidth As Double
    For intCol = 0 To DataGrid1.Columns.Count - 1
        dblWidth = dblWidth + DataGrid1.Columns(intCol).Width
    Next
    DataGrid1.Width = (18 + DataGrid1.Columns.Count) * Screen.TwipsPerPixelX + (dblWidth) + IIf(Adodc1.Recordset.RecordCount > DataGrid1.VisibleRows, 15 * Screen.TwipsPerPixelX, 0)

Zorxx -

I don't understand what this code is doing.  Please explain. Thx.

Now, let's think about this.  My original code works perfectly for some tables, but truncates the last column for some tables.   The DefColWidth property determines the width of a column based on the wider of the field name or the table-defined field length.

I have set this property in code.

DataGrid1.DefColWidth = 0 'Column width will be size of field name or
                                                'Field length, whichever is larger

Why is it working for some tables, but not for others?

Okay, let's define the problem.

(1)  Is the datatype of the *last* column on the problem recordsets always the same type?  Does it differ from those recordsets that work?

(2)  On the problem recordsets, what is the actual difference between the width calculated and the actual needed width (you'll probably need to get into debug mode to check this, just setting the width manually).  Is it some multiple of the total?

(3)  Can you show some example data, e.g.:

RecordsetThatWorks:
Field1   DataType(Size)  ColumnWidth
...

RecordsetThatFails:
<Same as above>

Only show the info for the visible columns.

Let's see what we can see...

bhess -

1.  The last column of the problem recordset is always text.  And, no it does not differ from the "good" recordsets.

2.  Not sure about this

3.  Sample Data - I'm showing the hidden columns just in case.

Good RS
Agency_ID, autonum, hidden in datagrid
Agency_Name, text, 50

DA_ID, autonum, hidden in datagrid
DA_First, text, 50
DA_Last, text, 50

Bad RS
City_ID, autonum, hidden in datagrid
City_Description, text, 30

Hearing_ID, autonum, hidden in datagrid
Hearing_Type_Description, text, 50

Also, I'm pasting in the entire on click procedure:

Private Sub cmdOpenReference_Click()
Dim i As Integer
Dim c As Column
Dim j As Integer
Dim k As Integer

  If lstReference.SelCount = 0 Then
    MsgBox "You have not selected an item.", vbOKOnly, "No Selection Made"
    Exit Sub
  End If
 
  i = lstReference.ListIndex
  Adodc1.CommandType = adCmdText
  Adodc1.RecordSource = "Select * from " & lstRefTable.List(i) & " Order by 2"
  Adodc1.Refresh
  Adodc1.Visible = True
  DataGrid1.Visible = True
  Set c = DataGrid1.Columns(0)
 
  If lstRefTable.List(i) = "tblPenal_Code_Ref" Then
    c.Visible = True 'Show primary key
  ElseIf lstRefTable.List(i) = "tblStatus_Code_Ref" Then
    c.Visible = True 'Show primary key
  Else
    c.Visible = False 'Hide primary key
  End If
 
'Determine width of DataGrid based on sum of widths of visible columns
  DataGrid1.Width = 0
  DataGrid1.DefColWidth = 0 'Column width will be the size of the field name or
                            'the field length, whichever is larger
  j = DataGrid1.Columns.Count
    For k = 0 To j - 1
      If DataGrid1.Columns(k).Visible = True Then
        DataGrid1.Width = DataGrid1.Width + DataGrid1.Columns(k).Width
      End If
    Next k
   
'Add padding to DataGrid width
 DataGrid1.Width = DataGrid1.Width + 500
 'Set position of Adodc control
 Adodc1.Left = DataGrid1.Left
 
 'Set position of labels for Staff table
  If lstRefTable.List(i) <> "tblStaff_Ref" Then
    lblStaffType.Visible = False
    lblType.Visible = False
  Else
    lblStaffType.Visible = True
    lblStaffType.Left = DataGrid1.Left + DataGrid1.Width + 100
    lblType.Visible = True
    lblType.Left = DataGrid1.Left + DataGrid1.Width + 100
   
  End If

End Sub

Check the no. of records in good and bad RS, as TimCottee had pointed out, if I understood correctly, may be the scroll bar causes the problem?

valli_an and TimCottee have a good point - is the scrollbar the problem here?  If so, check your recordcount - if you have more records than visible rows in your datagrid, add (16 * twipsperpixelx) to your width result.

Also, let's assume that there *is* a problem with the autogenerated width.  This code should get you the correct max width needed:

j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       DataGrid1.Width = DataGrid1.Width + Screen.TextWidth String(Adodc1.Recordset.Fields(1).DefinedSize, "W")  ' Ws are usually the widest character
     End If
   Next k
 

TimCottee's code works for some of the previously truncated columns.  We're getting closer.

Bhess - I need to try your code next.


 Dim dblWidth As Double
For intCol = 0 To DataGrid1.Columns.Count - 1
dblWidth = dblWidth + DataGrid1.Columns(intCol).Width
Next
DataGrid1.Width = (18 + DataGrid1.Columns.Count) * Screen.TwipsPerPixelX + (dblWidth) + IIf(Adodc1.Recordset.RecordCount  > DataGrid1.VisibleRows, 15 * Screen.TwipsPerPixelX, 0)

Bhess - your code is giving me a compile error inside the If statement.

j  = DataGrid1.Columns.Count
 For k = 0 To j - 1
 If DataGrid1.Columns(k).Visible = True Then
   DataGrid1.Width = DataGrid1.Width +  Screen.TextWidth _        String(Adodc1.Recordset.Fields(1).DefinedSize, "W")
 End If
Next k

Also, Bhess - there's no Screen.TextWidth method.

DOH!  Me.TextWidth, not Screen.TextWidth.  NOTE:  This assumes that you have set the current form's font to the same font as is used in your datagrid.

Can someone please explain what this twips to pixel problem is about and what is the proper conversion.  So far, Tim Cottee's code is the closest to resolving the problem.  But it still needs tweaking.  I don't understand the Twips/Pixel conversion so I don't know what to tweak.

Tim - your code works for most of the recordsets, but not all of them.

Also bhess - is your code suggestion in lieu of Tim's or in addition to Tim's.  Also, the form's font and the datagrid font are different.

In addition, not in lieu of.

Twips/Pixel conversion:  The Width of most controls is expressed in twips, not pixels.  However, the number of twips per pixel is dependent on screen resolution.  Thus, the TwipsPerPixelX and TwipsPerPixelY functions.

Consider the simplest case - you need to move a control so that there is one pixel visible between it and the edge of your form.  The code:

MyControl.Left = TwipsPerPixelX

sets the control's Left value to one pixel (in my case, about 15 twips).

A scroll bar is 15 or 16 pixels wide.  So adding 16 * TwipsPerPixelX  to your width will widen the control to handle the scroll bar.

Okay - starting to get the picture (slowly).  So, in Tim's code he is adding 18 to the Columns Count and then multiplying by Screen.TwipsPerPixelX.  What is that for?

He's also multiplying the Screen.TwipsPerPiexelX by 15 if the visible rows is greater than the record count.  Should I also do the same thing for the Visible Columns count by adding another + like so:

Dim dblWidth As Double
For intCol = 0 To DataGrid1.Columns.Count - 1
  dblWidth = dblWidth + DataGrid1.Columns(intCol).Width
Next

DataGrid1.Width = (18 + DataGrid1.Columns.Count) * Screen.TwipsPerPixelX + (dblWidth) + IIf(Adodc1.Recordset.RecordCount
 > DataGrid1.VisibleRows, 15 * Screen.TwipsPerPixelX, 0) +
'New code below:
 IIf(Adodc1.Recordset.ColumnCount
 > DataGrid1.VisibleCols, 15 * Screen.TwipsPerPixelX, 0)

If your visible columns are wider than the grid, you should end up with a horizontal scrollbar at the bottom, not necessarily one at the side.  Your code *should* show all of the columns in the grid as it is, unless you have a maximum width limitation on the grid itself.  In any case, this shouldn't be necessary for columns.

Hmmm.... I wonder.

Try running your width adjustment code manually (in debug mode) after the screen is fully displayed.  Check and see if the column widths returned are the same.  Add this line of code to verify it (try this subroutine to automate the width adjustments):

Sub SetGridWidth(G as DataGrid)
Dim j as Integer
Dim k as Integer

 DataGrid1.Width = 0
 DataGrid1.DefColWidth = 0 'Column width will be the size of the field name or
                           'the field length, whichever is larger
 j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       DataGrid1.Width = DataGrid1.Width + DataGrid1.Columns(k).Width
       Debug.Print DataGrid1.Columns(k).Width
     End If
   Next k
End Sub

Replace your block of code with this code, then add (say) a command button on your form that runs this.  Are the numbers returned the same?

Before we do that - I still haven't tried your Defined Size code because you've got a compile error.  Just to refresh our memory, let me show you what I have so far.  I've incorporated Tim's code and changed the style a bit to fit my style.  It works for 12 out of 15 tables that are populating the recordset.  On the 3 where it doesn't work, the field lengths are long - more than 25 characters.  But, I think your Defined Size thing might do the trick, I just don't know:

a) where to put it and
b) how to get rid of the compile error.  

'Determine width of DataGrid based on sum of widths of visible columns
  intWidth = 0
  DataGrid1.Width = 0
  DataGrid1.DefColWidth = 0 'Column width will be the size of the field name or
                            'the field length, whichever is larger
 
 For j = 0 To DataGrid1.Columns.Count - 1
   If DataGrid1.Columns(j).Visible = True Then
      intWidth = intWidth + DataGrid1.Columns(j).Width
   End If
 Next j
 
 DataGrid1.Width = intWidth + (40 * Screen.TwipsPerPixelX) + _
 IIf(Adodc1.Recordset.RecordCount > DataGrid1.VisibleRows, _
 16 * Screen.TwipsPerPixelX, 0) 'adds extra space for vertical scroll bar

I ran the Debug on column widths, and the column widths that were returned were different.  Shouldn't they all be the same, since the width is determined by the size of the field in the underlying table?

I've increased the points to 200.  I really need to get this one resolved asap.

Speculation - based on what I know, the column widths are most likely being set based on the data in the first data row in the table in the first case.  After more data is loaded, then the control is reporting more correct data widths.

Hmmm... best method that I can think of for handling this.  Add a 'scratch' form to your project.  This form will never be displayed - it's only for this test case.

Load the form (without SHOWing it), and set the font to match that of your datagrid.  Then, try the code I had earlier using the .Textwidth property on that form.  For example, if the form was called frmScratch then:

Load frmScratch
frmScratch.Font = Datagrid1.Font

  j = DataGrid1.Columns.Count
  For k = 0 To j - 1
    If DataGrid1.Columns(k).Visible = True Then
      DataGrid1.Width = DataGrid1.Width + frmScratch.TextWidth String(Adodc1.Recordset.Fields(1).DefinedSize,
"W")  ' Ws are usually the widest character
    End If
  Next k
  ' Add the 18 pixels for the scroll bar if needed...
... other stuff...

I tried your code without the scratch form as so:

If DataGrid1.Columns(k).Visible = True Then
  DataGrid1.Width = DataGrid1.Width + Me.TextWidth(String(Adodc1.Recordset.Fields(1).DefinedSize,  "W"))

' NOTE - both the DataGrid and Form have a font size of 10 point.

This is essentially the same as using the scratch from, right?  At any rate, the code did not fix the problem.  In fact, it only increased the gray area to the right of the last column.  It didn't change the actual column width.

Your guess about the field being set to the width of the first value in the table sounds very plausible.

Setting DefColWidth = 0 is supposed to handle this, but apparently it's not.

There's got to be a logical explanation for this.

Getting desparate - will increase to 300 points.

Thanks for hanging in there with me.

Hold it... the problem is that the grid width is *larger* than the total of the column widths??  (The grey area is the area not taken up by the columns, right?)  I'm afraid I missed this earlier....

Then this *would* increase the problem.  So, let's see about setting the width again with a different value... we need the columns large enough to show the data, and the grid small enough to not display blank space while still showing all of the columns.  *NOW* we have it...

Let's try a bit of additional code in your latest:

Dim lCWidth as Long


If DataGrid1.Columns(k).Visible = True Then
 lCWidth = Me.TextWidth(String(Adodc1.Recordset.Fields(1).DefinedSize,  "W"))
 DataGrid1.Width = DataGrid1.Width + lCWidth
 DataGrid.Columns(k).Width = lCWidth - TwipsPerPixelX
End If

Try this and see if it's close at the end...

Don't forget to adjust for the scrollbar...

Okay, Not sure if I was supposed to do Screen.TwipsPerPixelX.  But, here's what I have.  This yields humongous column widths with the second one going off the page.  Check my code, I may have gotten confused.

Dim intWidth As Long
Dim intWidth As Long

For j = 0 To DataGrid1.Columns.Count - 1
   If DataGrid1.Columns(j).Visible = True Then
     ColWidth = Me.TextWidth(String(Adodc1.Recordset.Fields(1).DefinedSize, "W"))
     intWidth = intWidth + ColWidth
     DataGrid1.Columns(j).Width = ColWidth - Screen.TwipsPerPixelX
   End If
 Next j

DataGrid1.Width = intWidth + (40 * Screen.TwipsPerPixelX) + _
 IIf(Adodc1.Recordset.RecordCount > DataGrid1.VisibleRows, _
 16 * Screen.TwipsPerPixelX, 0) 'adds extra space for vertical scroll bar

Let's adjust a bit...:

For j = 0 To DataGrid1.Columns.Count - 1
  If DataGrid1.Columns(j).Visible = True Then
    ColWidth = Me.TextWidth(String(Adodc1.Recordset.Fields(j).DefinedSize, "Z"))
    intWidth = intWidth + ColWidth
    DataGrid1.Columns(j).Width = ColWidth - Screen.TwipsPerPixelX
  End If
Next j

DataGrid1.Width = intWidth + IIf(Adodc1.Recordset.RecordCount > DataGrid1.VisibleRows, _
16 * Screen.TwipsPerPixelX, 0) 'adds extra space for vertical scroll bar



(Have we really been using only the defined width of field 1 (instead of each field?) all along?  My typo, I fear....)

No prob - just happy that you're hanging in there.

Okay, on this one, the DefColWidth seems to be overridden.  For instance, in one of the tables, the first column has a field length of 1.  With the new code, the column is only 1 character wide, truncating the field description.

For the other columns - the long ones - they are still way too wide - off the page.

Okay - setting the width based on a default character width does not appear to be a workable solution to your problem.  So.....

Going back to something that was close to working.....

j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       DataGrid1.Width = DataGrid1.Width + DataGrid1.Columns(k).Width
     End If
   Next k

I have seen some information that indicates that the column width for text columns is 'underreported' for longer columns.  (This was in a posting on groups.google.com).  So, let's try modifying this to handle that situation.

According to the posting, the control adjusts automatically to a width of about 20 characters.  Assuming this is correct....

j = DataGrid1.Columns.Count
   For k = 0 To j - 1
     If DataGrid1.Columns(k).Visible = True Then
       ColWidth = Adodc1.Recordset.Fields(j).DefinedSize
       If ColWidth > 20 Then
          intWidth = DataGrid1.Columns(k).Width / 20 * Adodc1.Recordset.Fields(j).DefinedSize
          DataGrid1.Columns(j).Width = intWidth
       Else
          intWidth = Adodc1.Recordset.Fields(j).DefinedSize
       End If
       
       DataGrid1.Width = DataGrid1.Width + intWidth
     End If
   Next k

I'm a little confused on what we're doing here.  So, let me clarify what the variables are:

intWidth is a variable that I'm using to calculate the DataGrid width.  I assign DataGrid width the value of intWidth after the loop is complete.

colWidth is a variable that I was using to store column width - or at least I *thought* that was why you asked me to add an additional variable.

Based on those definitions, this code isn't clear to me.

Also, I like to use one counter (j) to loop through the FOR.

With this in mind, here's my translation of your code.  Either I've screwed up what you are doing, or the code doesn't work.  Same problem.  Fields are too wide.   Would it help if you created a quick Access database with some sample data in it (I could send you the datatypes/lengths) so that you can actually see the results.

 For j = 0 to DataGrid1.Columns.Count - 1
   If DataGrid1.Columns(j).Visible = True Then
         ColWidth = Adodc1.Recordset.Fields(j).DefinedSize
             If ColWidth > 20 Then
                 intWidth = (DataGrid1.Columns(j).Width /20)                                     *Adodc1.Recordset.Fields(j).DefinedSize
                  DataGrid1.Columns(j).Width = intWidth
              Else
                intWidth = Adodc1.Recordset.Fields(j).DefinedSize
               End If
        intWidth  = intWidth + colWidth
   End If
  Next j

Here's what I'm trying to achieve.

According to some info I came across, the datagrid tends to lock text columns in at 20 characters of size by default.  If your text fields are > 20, I want to increase the automatic size proportionately.  Using your variable assignments (sorry), the code should be:

For j = 0 to DataGrid1.Columns.Count - 1
  If DataGrid1.Columns(j).Visible = True Then
    ColWidth = Adodc1.Recordset.Fields(j).Width
    If Adodc1.Recordset.Fields(j).DefinedSize > 20 Then
      ColWidth = (DataGrid1.Columns(j).Width /20) * Adodc1.Recordset.Fields(j).DefinedSize
      DataGrid1.Columns(j).Width = colWidth
    Else
      ColWidth = Adodc1.Recordset.Fields(j).Width
    End If
    intWidth  = intWidth + colWidth
  End If
Next j

I changed this
ColWidth = Adodc1.Recordset.Fields(j).Width
to .DefinedSize due to compile error

Still not working.  Columns way off the page.

Do you have any thoughts on the DefColWidth = 0  which is supposed to size the columns based on the widest of the field length or the field description?

Bug / Typo on my part.  Fixed here - ColWidth = Adodc1.Recordset.Fields(j).Width is wrong, should be

  ColWidth = Datagrid1.Columns(j).Width.

However, let's go back a few steps.

TimCottee's code was the closest to working, correct?  Let's work with that and see where we are.  Going back to the basics of the problem...

(1)  After the recordset is loaded into the grid, you calculate what *should* be the width of the grid.  This works for most recordsets, but not all.

(2)  In the problem recordsets,
     (a) the right column is truncated, indicating that the width of the grid is insufficient to hold all of the columns.  This means that
     (b) the .Width of the columns is being reported incorrectly by the system.

(3)  There is no specific data field type that causes the problem - it has no apparent pattern.

All correct so far?

Desired:
   Adjust the Grid Width to match the actual width of the columns.

One adjustment - let's go back to original code + TimCottee's adjustments, and add These lines:

i = lstReference.ListIndex
 Adodc1.CommandType = adCmdText
 Adodc1.RecordSource = "Select * from " & lstRefTable.List(i) & " Order by 2"
 Adodc1.Refresh
 Adodc1.Visible = True
 DataGrid1.Visible = True
 DataGrid1.Refresh            '''' ADDED
 DoEvents                     '''' ADDED
 Set c = DataGrid1.Columns(0)

Yes, Tim's code was the closest to solving the problem.  I mean, really, really close.   And, yes, in the problem recordsets, the grid width is insufficient to hold all of the data that is in the column.  The datatype is TEXT for the problem columns.  

Okay, I reverted back to the original code and added your two lines.  It looks like the column width inched over a character or two to the right for the bad recordsets.  But, it's still truncated by a few characters.

I just did some debugging on two of the "bad recordsets."

Okay, the first table has 2 visible columns with widths of 2115 and 2429.  The DataGrid width = 5415 - which makes sense since we are adding extra space for the scrollbar (see  below).

The second table I debugged had 1 visible column with a width of 2430.  Its datagrid width = 3300.

So - if the DataGrid width is wide enough to hold both columns, then why is that column still truncating?  Urrrgggh!

DataGrid1.Width = intWidth + _
 (40 + DataGrid1.Columns.Count) * Screen.TwipsPerPixelX + _
 IIf(Adodc1.Recordset.RecordCount > DataGrid1.VisibleRows, _
 16 * Screen.TwipsPerPixelX, 0) 'adds extra space for vertical scroll bar

Hmmm.... I'm going to speculate some more.

"It looks like the column width
inched over a character or two to the right for the bad recordsets.  But, it's still truncated by a
few characters."

How many?  In other words, can you give a sample of the data that is truncated, and show (a) what is shown and (b) what it should be.  Also, is the data truncated in all fields, or only some (and if some, is it the first row, or, say, the eighth row?)

Manually resizing the column on the second bad example, how wide does it need to be to show the data?

Is the column width all the way to the right of the data grid, or is there blank space?

I was wrong.  The new code did not change the column widths.  When the data is truncated, it could be any row - depending on what's in that particular field.  The column width stops at the truncation point - no blank space.

Here are some examples:

What is displayed                             What it Should Say
Abandoning of Endangerin            Abandoning of Endangering Child
Aggravated Promotion of P            Aggravated Promotion of Prostitution
No Case by District Attorne            No Case by District Attorney
Alternative Learning Cente             Alternative Learning Center

These examples are from 3 different tables.  Hey, I just noticed that the point of truncation is exactly the same in each instance - approx 24 characters.  However, the actual field length in the table is 50.

Try setting the font on the data grid to a fixed width font (e.g. Courier) and see if the problem disappears, or moves.  I'm betting we've got that truncation at '20 characters or so' problem with the text fields that was mentioned.

Also, check the first data item returned.  Is it ever truncated?  If so, is it one of the above?

Changing the font to courier caused a bit more truncation - maybe half a character's worth.  For example, the first row that I referenced above has one less letter in it - it displays through to the 'i' instead of the 'n'.

Yes, the first item returned is sometimes truncated.  The first row that I used as an example above is also the first row of its corresponding table (as defined by the sort in the select statement).

I just changed the value of DefColWidth to 2500 and then 5000, which had no impact on the display.  However, in the properties description, it says that DefColWidth specifies the column width for auto-created columns.

Okay - one final thing to test.  Looking at all of your tables:

(1)  On those that are not truncated:
   (a)  What is the actual length (as opposed to the field size) of the data?  Longest?  Average?  Check the longest entries - are they truncated?
   (b)  What is the reported width of the columns?

(2)  On those that are truncated:  --- The same data as in (1) above.

Let me know the results....

Can I email you the Access database?  You can reach me directly at cnealy@att.net

If it's Access 97 or earlier, I can open it.  Otherwise, not.

You can send it to the E-Mail address in my profile (click on my name if you don't know how to access the profiles...)

It's Access 97.  I'll zip it up and send it now.  It'll take a few minutes since I'm on dial-up at the moment.

Thanks!  I have to run out for a bit but will test this code today or tomorrow.

I haven't tested your code yet (need a break from looking at it). :o)  Just curious, can you post the link to the Google discussion about the problem of truncating text in datagrids?

Sure:

http://groups.google.com/groups?q=datagrid+column+width+20&hl=en&rnum=4&selm=365F0420.3903C51F%40apexsc.com

bhess1 - I think you should run this code to see what it's doing.  It's not yielding the desired results.  I copied and pasted the code and ran it - column widths are running off the page.  If you need me to resend the MS Access database and/or the actual form that I'm using, let me know.

I'm going to increase the points again in the hopes of inspiring more interest in this problem.

Looks like 300 is the maximum number of points for a single question.  :o(

You may want to send the actual form you are using....

I know that some of the columns get very wide, but when 100 characters are allowed, then the width of the entry fields grow significantly.  Remeber, you need to allow space for the maximum length allowed.  As I noted in the commentary before the code, tblPenal_Code_Ref (for example) allows 100 characters of entry, so the column width has to handle 100 characters.  Those tables that handle names have two 50 character fields, so 100 characters need to be allowed *plus* the buffer space around the fields.  

You basically have some specific options:

(1)  Reduce the field lengths.  This is possible on some of the tables, but not others.

(2)  Reduce the font size.  This may or may not be possible.

(3)  Increase the width of the form.  To allow 100 characters, you may have to.  YOu could probably write code to autosize the form (e.g. if datagrid1.width + datagrid1.Left > Form.Width then Form.Width = Datagrid1.Width + Datagrid1.Left + 100)

Thanks for sticking with me on this one.