MSHFlexGrid ColWidth & BandLevel with DataShape

[Zivz]

Hi.

I am attempting to size my column widths in an MSHFlexGrid to fit my
data on an Access form. Problem is that in iterating through my rows
and columns, I seem to get the same value of 90 both before and after
attempting to set it except for columns 1-3. col(0) will set to what I
tell it to, while 2 & 3 collapse to virtually nothing and my remaining
columns remain what ever they start out as whether I have manually
changed them or not.

I have tried any number of combinations of settings, including but not
limited to, FormatString, FixedCols, FixedRows, WordWrap, Redraw,
Refresh, & AllowUserResizing to no avail.

Also, when I try to use the BandLevel property to pass to ColWidth, I
only get a 0 returned even though Bands returns 2 for my particular
shape. I ran into these problems previously, and have wasted an
inordinate amount of time on them, both trying different things and
trying to find a solution.

I ran into a web page (http://www.experts-
exchange.com/Programming/Programming_Languages/Visual_Basic/Q_
20616849.html) refering to a Band Tab in properties, but Access 2002 SP3
doesn't show such a tab.

Here is the code from one of my forms, with my simplest MSDataShape, my
txtWidth function is rather simplistic but serves as a stub until I get
the actual ColWidth to work:


Option Compare Database
Dim cnSched As New ADODB.Connection
Private Sub CommittedSchedules_Change()
DoCmd.ApplyFilter , "Meeting_ID='" & CommittedSchedules.Value & "'"
showSchedule
End Sub
Private Sub showSchedule()
Dim strCn As String
Dim objADOCommand As New ADODB.Command
Dim rsSched As ADODB.Recordset
Dim strSh As String
Dim row
Dim myColIndx
Dim strTemp As String
Dim hfgWidth As Long
' Create a CommandString
strSh = "SHAPE {SELECT Distinct Meeting_ID as Meeting, Format
(TDateTime, 'dddd, mmmm d yyyy') as [Day] from MeetingItinerary WHERE Meeting_ID='" & CommittedSchedules & "'}" & _
" APPEND ({SELECT Format(TDateTime, 'dddd, mmmm d yyyy') as [Day], Format(TDateTime, 'h:Nn AM/PM') as [Time], Topic, Luminary from MeetingItinerary WHERE Meeting_ID='" & CommittedSchedules & "' ORDER BY TDateTime} RELATE Day TO Day) as Myrows"
strCn = "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source = g:\Blah-access\Blah_be.mdb"
' On Error Resume Next
With cnSched
Do Until .STATE = 0
.Close
Loop
.ConnectionString = strCn
.Provider = "MSDataShape.1"
.Open
End With
' On Error GoTo 0
With objADOCommand
.ActiveConnection = cnSched
.CommandText = strSh
End With
With hfgSchedule
Debug.Print .Bands
.AllowUserResizing = 0
.Clear
' .Cols = 2
' .FixedCols = 1
' .FixedRows = .Rows - 1
' .WordWrap = False
Set .DataSource = objADOCommand.Execute
'populate the grid
.col = 2
.row = hfgSchedule.Rows - 1
For myColIndx = 0 To .Cols - 1
For row = 0 To .Rows - 1
.row = row
.col = myColIndx
hfgWidth = TxtWidth(strTemp)
If hfgWidth + 90 > .ColWidth(myColIndx, .BandLevel) Then
' Debug.Print "B" & vbTab & .ColWidth(myColIndx, .BandLevel)
.ColWidth(myColIndx, .BandLevel) = hfgWidth + 90
' Debug.Print "A" & vbTab & .ColWidth(myColIndx, .BandLevel)
Else
If myColIndx = 0 Then
' Debug.Print "B" & vbTab & .ColWidth(myColIndx, .BandLevel)
.ColWidth(myColIndx, .BandLevel) = 250
' Debug.Print "A" & vbTab & .ColWidth(myColIndx, .BandLevel)
Else
If Not .ColWidth(myColIndx, .BandLevel) > hfgWidth Then
' Debug.Print "B" & .ColWidth(myColIndx, .BandLevel)
.ColWidth(myColIndx, .BandLevel) = 800
' Debug.Print "A" & vbTab & .ColWidth (myColIndx, .BandLevel)
End If
End If
End If
Next
Next
.Redraw = True
.Refresh
.AllowUserResizing = 1
End With

End Sub
Private Function TxtWidth(txtString) As Integer
TxtWidth = Len(txtString) * 93
End Function


Please, HELP me! I have a limited supply of hair to pull.
Thank you. Zivz