I'm trying to set up 3 related grid 1

[SBTBILL]

I'm trying to set up 3 related gridviews. The goal is to have a header grid that controls the other two. Thus if the record pointer is moved in the top grid the contents of the bottom two should automatically change. I don't want to require the user to click on a field to cause it to happen. In VFP there is a method called AfterRowColumnChange basically I'm looking for a similar property in VB. In VFP I'd load all the data and work with it in memory. I've tried that but it would be OK to load the data for grids 2 and 3 on changes in grid 1.

Looked through the web and saw a lot about joins and stuff like that but this isn't a join because the top table will have 1 record per item and the 2nd table will have up to 5 records per item and the 3rd grid will have up to 100 records per item.

My selects will look something like this

Grid 1
Select * from items where year = theyear

Grid 2
Select * from levels where year = theyear and foreignkey = grid 1 key

Grid 3
Select * from detail where year = theyear and foreignkey = grid 1 key


While I might have several hundred records in Grid 1 each will relate to a unique batch of records in Grids 2 and 3.

Thank for the help

 

[jebenson]

This can be done easily with a CurrencyManager and DataViews.

I typically do this in the DataGridView's MouseUp event, using global objects:

First, define the CurrencyManager and a DataRowView globally:

Dim cmMainGrid As CurrencyManager
Dim drvMain As DataRowView


Also, create DataViews of the tables for the 2 sub-grids:

Dim dvGrid2 As DataView
Dim dvGrid3 As DataView


And, bind the DataViews to the appropriate DataTables:

dvGrid2 = DataTable2.DefaultView
dvGrid3 = DataTAble3.DefaultView

Use the DataViews as the DataSource of the two sub-grids.

Now, the main grid's MouseUp event handler:

Private Sub DataGridView1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseUp

Dim hti As DataGridView.HitTestInfo 'define a HitTestInfo object to get the row clicked

hti = DataGridView1.HitTest(e.X, e.Y)

'check if the
If hti.RowIndex < DataGridView1.Rows.Count And hti.RowIndex > -1 Then
DataGridView1.Rows(hti.RowIndex).Selected = True
cmMainGrid = DataGridView1.BindingContext(DataTable1) 'note: use the name of the table you have bound to the grid
drvMain = cmPrinter.Current 'get a DataRowView of the currently selected row
'Filter the DataView for Grid2
dvGrid2.RowFilter = "Field1=" & drvMain.Item("Field1Key") 'dvGrid2 will only show data that matches the RowFilter
Else
cmMainGrid = Nothing
drvMain = Nothing
dvGrid2.RowFilter = "Field1=-9999" 'use a value that will never return any rows in the filter
dvGrid3.RowFilter = "Field1=-9999" 'use a value that will never return any rows in the filter
DataGridView2.DataSource = Nothing
DataGridView3.DataSource = Nothing
End If

End Sub

Set up a MouseUp event handler for the second grid, similar to the one for the first grid, and use it to set the RowFilter for the third grid.


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

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[jebenson]

Oops, one mistake.

this line:

cmMainGrid = DataGridView1.BindingContext(DataTable1)

should not be in the event handler. Put this code right after where you bind the datatable to the datagridview.

Sorry about that.

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

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[SBTBILL]

Thank you.

So Far I've only got one grid working but you solved a problem I've been bugged by for a year. I don't know that I'd agree with you that the solution is simple though.

 

[jebenson]

Well...simple because I've been doing it for almost a decade now

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

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!