MSDataShape

[TallOne]

Hi Everyone,
I need help with a procedure. I've created a hierarchical recordset I use with the MSHFlexgrid. I want to use the same recordset for a datagrid. My problem is that when I assign the child recordset value to the datagrid, only data from the parent's first record is given. I'd like all the data from each parents record displayed in the grid. I hope I explained that clearly. Do I need to create a seperate recordset for the datagrid? Here's my code:

Private Sub LoadGrids()
Dim colX As Variant
Dim varTotalPlaced As Variant
Dim varTotalBalance As Variant

On Error GoTo errorHandler
Set cnMain = New ADODB.Connection
cnMain.Open "Provider=MSDataShape.1;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & App.Path & "\debtall.mdb"
If cnMain.State = adStateOpen Then
Me.Icon = Image0.Picture
Else
Me.Icon = Image1.Picture
End If
Set rsMain = New ADODB.Recordset
rsMain.Open "SHAPE {SELECT * FROM `Client`} AS Client " & _
"APPEND " & _
"(( SHAPE {SELECT * FROM `DebtAll`} AS DebtAll " & _
"APPEND ({SELECT * FROM `Payment`} AS Payment " & _
"RELATE 'PKID' TO 'PKID_DebtAll') AS Payment) AS DebtAll " & _
"RELATE 'ClientNumber' TO 'ClientNumber') AS DebtAll", cnMain, adOpenStatic, adLockOptimistic

'Uncomment the next three lines then it works
'Set rsDebtAll = New ADODB.Recordset
'rsDebtAll.Open "Select * From Debtall", cnMain, adOpenStatic, adLockOptimistic
'Set DataGrid1.DataSource = rsDebtAll
Set MSHFlexGrid1.DataSource = rsMain
'Use this next line then only records from 1st parent record
Set DataGrid1.DataSource = rsMain("DebtAll").Value

rsDebtAll.MoveFirst
Do Until rsDebtAll.EOF
varTotalPlaced = rsDebtAll.Fields("AmountPlaced") + varTotalPlaced
varTotalBalance = rsDebtAll.Fields("Balance") + varTotalBalance
rsDebtAll.MoveNext
Loop
rsDebtAll.MoveFirst
lblRecordCount = "Total Records = " & rsDebtAll.RecordCount
lblTotalBalance = "Total Balance = " & FormatCurrency(varTotalBalance)
lblTotalPlaced = "Total Placed = " & FormatCurrency(varTotalPlaced)
lblPercentageCollected = "Percentage Collected = " & _
Format((varTotalPlaced - varTotalBalance) / varTotalPlaced, "#.#%")
DataGrid1.Refresh
'Eliminate Microsoft Data Control Error 7008 The current row is not available
For Each colX In DataGrid1.Columns
colX.Locked = True
Next
Exit Sub
errorHandler:
MsgBox Err.Number & vbCrLf & Err.Description _
& Err.Source

End Sub

 

[jebenson]

The MSHFlexGrod was designed to handle hierarchical recordsets, the DataGrid was not. If you want a grid that displays only the parent data, just use another MSHFlexGrid and set the properties so that it only displays the data you want. To do this, just set the BandExpandable property of the first (parent) band to False:

MSHFlexGrid1.BandExpandable(0) = False

This will prevent the band from expanding to show the child records.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[TallOne]

jebenson,
Thanks for the reply! I just decided to create a few different recordsets and keep them in sync on _movecomplete. I was just hoping that I could create one hierachical recordset and assign other diminsioned rs to the childs which should stay in sync for me. Anyways, thanks for the reply.
TallOne