Two Datagridviews with Master-Detail relationship

Posted on 2007-07-30
Last Modified: 2013-12-25

I need to create a Visual Basic windows form with two datagridviews that shows data for the following tables:

Table: Employee_Table
PrimaryKey:  Employee_ID (GUID)
Fields: Last_Name, First_Name, Address

Table: Dependents_Table
Primary Key: Dependent_ID (GUID)
Fields: Employee_ID (FK), Dependent_Last_Name, Dependent_First_Name, Relationship

The first datagridview should show the contents of the Employee_Table. When I click on a row on that datagridview, I want to related Dependents_Table record to show on the other datagridview.  

How do I do this? I am using Visual Basic 2005. And SQL Server 2005.

Question by:crickpaolo
    LVL 48

    Accepted Solution

    Hi crickpaolo,

    You can do it using the Microsoft example

    This will fill the first datagridview. Then after cellclick you repeat this procedure only changing the SQL statement and clear the datagridview before (datagridview.datasource = nothing).

    LVL 12

    Assisted Solution

    Private WithEvents rsMaster As ADODB.Recordset
    Private rsChild As ADODB.Recordset

    Private Sub Form_Load()

       Set rsMaster = New ADODB.Recordset
       Set rsChild = New ADODB.Recordset

       rsMaster.Open "SELECT Employee_ID, Last_Name, First_Name, Address " & _
                               "FROM Employee_Table", YOUR_CONNECTION_OBJECT, adOpenKeyset

       Set DataGrid1.DataSource = rsMaster

    End Sub

    Private Sub rsMaster_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
     On Error Resume Next
     ' Display dependent info for selected employee.
     With pRecordset
       Call showDependentInfo(.Fields("Employee_ID"))
     End With
    End Sub

    Private Sub showDependentInfo(Byval nEmployeeID As Long)

       Set DataGrid2.DataSource = Nothing

       rsChild.Open "SELECT Dependent_Last_Name, Dependent_First_Name, Relationship " & _
                            "FROM Dependents_Table " & _
                            "WHERE Employee_ID = " & nEmployeeID,  _
                            YOUR_CONNECTION_OBJECT, adOpenKeyset

       Set DataGrid2.DataSource = rsChild

    End Sub

    Author Comment

    Thanks guys. Your comments pointed  me on the right direction.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now