I do not know how to update it I do know how through an ADO connection how to fill a Flexgrid from a record set. this code filled my flex grid and created the columns as well. I also had it filling a list box, you can ignore that part. I also should mention that I have a comboBox filled at formload, so I can select a user and his assocated records. You may or may not want this. Throughout the code I have it commented fairly well,but if you have any questions let me know.
Hope it helps
Option Explicit
Dim Rs As Recordset ' General Declarations
Dim Rs2 As Recordset
Dim CN As Connection
Dim rSql As String
Dim stn As String
Dim fld As ADODB.Field
'Dim hflxResults As MSHFlexGrid
Private Sub Combo1_Change()
End Sub
Private Sub Combo2_Click()
On Error Resume Next
Err.Number = 0
rSql = "DROP TABLE [testTable]" ' Deletes the the table
CN.Execute rSql
Err.Number = 0
Me!lstOPut.Clear
' Creates the testTable
rSql = "SELECT [Survey database].Customer, [Survey database].User, [User Id].Name, [Survey database].[Q8 Verb],[Survey database].Department INTO testTable FROM [Survey database] INNER JOIN [User Id] ON [Survey database].User = [User Id].[User ID]Where [User Id].[User ID] = '" & Mid([Combo2], 1, 3) & "'"
CN.Execute rSql
'Sets up the connection string for Rs2
Set Rs2 = New ADODB.Recordset
Rs2.Open "SELECT * FROM testTable", _
CN, adOpenKeyset, adLockOptimistic
DisplayForwardGrid Rs2, hflxResults ' Calls the DisplayForwardGrid Function to fill the Flex Gid
'this is where you put the list view code
Rs2.MoveFirst
Do Until Rs2.EOF
lstOPut.AddItem (Rs2!Customer & Space(5) & Rs2!User & Space(5) & Rs2!Name & Space(5) & Rs2![Q8 Verb] & Space(5) & Rs2!Department)
Rs2.MoveNext ' move to the next record
Loop
Rs2.Close 'Close the recordset
Set Rs2 = Nothing
End Sub
Private Sub DataGrid1_Click()
End Sub
Private Sub Form_Load()
Set CN = New ADODB.Connection
' Sets up the connection
CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:\E544293\Desktop\Survey Database.mdb;"
Set Rs = New ADODB.Recordset
On Error Resume Next
Err.Number = 0
rSql = "DROP TABLE [testTable]" ' Deletes the the table
CN.Execute rSql
Err.Number = 0
'Sets up the connection string Rs
Rs.Open "SELECT * FROM [User Id]", _
CN, adOpenKeyset, adLockOptimistic
Rs.MoveFirst ' moves to the first record
' while not at the end of file, fill the the combobox form the user ID table
Do While Not Rs.EOF
Combo2.AddItem Rs("User ID") & Space(3) & Rs("Name")
Rs.MoveNext 'Move to the next record
Loop
Rs.Close ' Close the Record set
Set Rs = Nothing
End Sub
Private Sub HScroll1_Change()
End Sub
Private Sub DisplayForwardGrid(Rs2 As ADODB.Recordset, hflxResults As MSHFlexGrid)
hflxResults.Redraw = False
hflxResults.FixedCols = 0
hflxResults.FixedRows = 0
hflxResults.Cols = Rs2.Fields.Count
hflxResults.Rows = 1
hflxResults.Col = 0
hflxResults.Clear
'SET UP THE hflxGrid headers
For Each fld In Rs2.Fields
hflxResults.Text = fld.Name
hflxResults.ColAlignment(hflxResults.Col) = 1
hflxResults.ColWidth(hflxResults.Col) = Me.TextWidth(fld.Name & "AA")
If hflxResults.Col < Rs2.Fields.Count - 1 Then
hflxResults.Col = hflxResults.Col + 1
End If
Next fld
'Move Through each row in the record set
Do Until Rs2.EOF
'Set the position in the hflxResults
'hflxResults.Rows = hflxResults.Rows + 1
'hflxResults.Row = hflxResults.Rows + 1
'hflxResults.Col = 0
'Loop through all fields
For Each fld In Rs2.Fields
hflxResults.Text = fld.Value
If hflxResults.ColWidth(hflxResults.Col) < Me.TextWidth(fld.Value & "AA") Then
hflxResults.ColWidth(hflxResults.Col) = Me.TextWidth(fld.Value & "AA")
End If
Next fld
Rs2.MoveNext
Loop
If hflxResults.Rows = 1 Then
hflxResults.Rows = 2
End If
hflxResults.FixedRows = 1
hflxResults.Redraw = True
Set hflxResults.DataSource = Rs2
End Sub
