[RESOLVED] Updating MSHFlexgrid to reflect changes in data

[anguswalker]

This may well not be the right place to post it, but I hope it will help anyone who has experienced the difficulties I have had with the (generally excellent) MSHFlexGrid control.
The problem I had was that I used an MSHFlexGrid to display data from an MSDataShape recordset, by binding it to a command object in the data environment. However if I added or deleted a record (using an .execute statement on the connection) I could only get the Flexgrid to update by closing its underlying recordset, unloading the form, reopening the recordset and then reloading the form. .additem and .removeitem do not work with hierarchical recordsets the way the help file says they should, and .refresh does nothing. Even closing the recordset, setting .datamember to "", issuing .clearstructure then reopening the recordset and rebinding the grid does nothing. It seems that once the grid is bound to a command object it just won't let go!

However I have now found out that if I create my ADO connection in code (rather than using the dataenvironment designer) and populate the MSHFlexGrid using
Set MSHFlexGrid.Recordset = ADOconn.Execute(strSQL)
then changing the recordset and repopulating the grid works fine, without unloading and reloading the form.

No doubt this will come as no surprise to you proper programmers who never use databound controls anyway, but I thought that since the Flexgrid only gives you read-only access that it wouldn't really count as a databound control. Shows how wrong you can be.

Another connected issue- if you update the recordset obviously the pattern of .expanded rows is lost. I was advised (in a previous thread) to use an array to track which rows are expanded, then rebuild the structure after repopulating the grid. The problem is that .row and .rows change in a grid displaying a heirarchical recordset, depending on which rows are or are not expanded. In my grid, with the same recordset .rows can go from 5 if all rows are collapsed to 100 if all are expanded, or anywhere in between. What I did to get round this was to use a uniqueID column in each band (width set to zero). I then stepped through the rows in each band and if a row in a band was expanded put its uniqueID in a variable. I then used these variables to re-expand the rows after repopulating.

Anyway, I hope this helps someone. Should I mark the thread as resolved, as it isn't really a question?

[dee-u]

Have you tried Requery? Or perhaps using a ClientSide Cursor?

[anguswalker]

Flexgrids don't have a .requery method. Using client side cursors made no difference. As I said though, I sorted this out by not binding the control and using set .recordset = ... instead.

[dee-u]

I didnt mean the FlexGrid, the recordset...

[anguswalker]

Sorry! Yes I did. I then resorted to closing and reopening, but as I said, even that didn't work. There's something odd about what flexgrids do with recordsets. Look at this sequence:
1) You have a flexgrid displaying a heirarchcial recordset. Let us say there are 5 rows in the child query.
2) You disconnect the flexgrid from its recordset, clear it and clear its structure. It's empty, right?
3) You close the recordset, add a row to a table, meaning there are now 6 rows in thew child query.
4) You requery the recordset. You even close it and reopen it. It has definitely got the new data in it.
5) You reconnect the flexgrid to the recordset, refresh it, do what you want to it.
6) There are still 5 rows in the child query.
I got an error code once that VB didn't list, and when I looked it up in MSDN it referred to a control in an early version of access, so presumably the flexgrid is based on that, and as I know from experience Access front ends do pretty weird stuff with data.

[waxz]

personally i would stick with the ADO coding method...but if u looking for a fix...you may need to set the variables to nothing and/or declare them again...

[anguswalker]

I think I'll set this thread to resolved. As I said, I only posted it in case it was of help to anyone else. The ADO method does work fine.

[sdenby]

I too am trying to use an MSHFlexGrid to display data and then make changes to my query and redisplay. I don't know how to do it.
The difference with my program is, I'm not linking the MSHFlexGrid to an ADO, I'm linking it to a Command in a DataEnvironment.
It's working great for what I want to show, but I can't edit the query and have the grid refresh. If I can't get an answer, then I'll have to make multiple MSHFlexGrids, one for each query I want to do and just have them become visible when needed.

[Tom951]

Funny, I was trying to do something very similar at the same time you were.

I display records in an MSHFlexgrid control on a form.

When the user selects a row to delete, I confirm their request, delete the record from the recordset, and redisplay the MSHFlexgrid as follows;

If MsgBox(str, vbOKCancel) = vbOK Then
        Rs.AbsolutePosition = irow
        Rs.Delete
        Set MSHFlexGrid1.DataSource = Rs
    End If

This is all new, but it seems to work OK. The MSHFlexGrid resizes itself and displays the data without the deleted row.

Comments? - Tom

[anguswalker]

Yes- this is the same solution as I found. Basically if you make its datasource a command object, or a data control, or even a SQL string then it won't update properly. However if you Set its recodset to a recordset object then it will.
To answer #8, say you have a command called MyData, then that will create a recordset called rsMyData. So try
Set MSHFlexGrid.Recordset = DataEnvironment1.rsMyData
and see what happens.