Hierarchical Flexgrid using changing recordset

[BobWman]

I am populating a hierarchical flexgrid with a disconnected recordset mrstList. The query used looks like this:
Select
ID ' Identity field, primary key
Field1,
Field2 = Substring(Field2, 1, 5) + ' ' + Substring(Field2, 6, 3)
From MyTable

The Recordset property of the grid is set to the resulting recordset derived from this query.

The grid is used for selecting a record, which is loaded into another recordset, using the ID field in the query.

When a new record is added/deleted to/from the table, I would like to update the grid without re-loading the recordset(making a round trip to the server). This presents various problems.

1. If I use AddItem/RemoveItem methods, I invariably have problems in other portions of my code involving the Rows property.

2. My preferred solution would be to update mrstList, clear the grid, and re-apply the recordset. However,
when I try to update the recordset, I have some problems.

Here is the code:

With mrstList
If mblnAdding Then
mrstList.AddNew
End If
lngID = mrstData!ID
!ID = lngID 'Problem 1
!Field1 = Str2Field(txtLabNumber.Text)
strData = NoSpace(txtField2.Text)
!Field2 = strData 'Left(strData, 5) + " " + Mid(strData, 6, 3) ' Problem 2
.Update

Notice that I know the new ID field from the master recordset. However, it can't be saved to mrstList, because it has a field property of Identity also(ISAUTOINCREMENT = True).

Secondly, Field2 has an unknown field property(BASETABLENAME = Null). Therefore, it won't accept an Update either.

I am looking for any suggestions related to this problem.

Thanks

 

[zemp]

I had trouble using recordsets as well. I switched to using arrays.

1. Start be feeding the recordset into an array of type UDT.

2. Then manually load the array into the flexgrid for display.

3. Add a new record to the array using array syntax and rules. Redim preserve and add your values.

4. When done, insert this new record into the database and use the @@Identity to get the ID number into the array.

5. Manually clear and refeed the array values into the grid.

When this is done both your databse, array and grid are up to date.

You can also set up the grid to allow direct editing through the use or a floating text box. If you are interested in trying this I can email you a small test project that I created that will demonstrate how I do this.
Thanks and Good Luck!

zemp