Opening new table from value selected in combobox

[Hawk123]

Hi, Im trying to make a simple programe that will make my life easier. But its more complex than i thought
i have 6 tables, 1 master and 5 sub. All the 5 tables are of different column and types.

Master Table
ID  Names
1   Branch
2   Committee
3   Translation
4   Distribution
5   Audio Visual

Branch Table
ID  Names       Notes
1   State 1     Notes 1
2   State 2     Notes 2
3   State 3     Notes 3

Branch Officials (Branch and Branch Officials are in relation)
ID  brID    President   Asst.President  Secretary   Treasurer   etc

Committee Table

Translation Table

Distribution table

Audio Visual table

What i want to do is make a combo box, and populate it with the master table data. When the combobox is selected i want the next combo to display data from its respective tables. I don't know how to do this. I can populate the master data, but i want to know how to select (or open a new tables) from the value selected in the first combobox say cmbMaster. Please help
my code to open the populate the first combobox is

cmbMaster.Items.Clear()

        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" & Application.StartupPath & "/database.accdb;")
                cn.Open()
                Dim command As New OleDbCommand("SELECT * FROM master", cn)

                Dim da As New OleDbDataAdapter
                Dim dt As New DataTable
                da.SelectCommand = command

                da.Fill(dt)

                cmbAux.DataSource = dt
                cmbAux.DisplayMember = "Names"
                cmbAux.ValueMember = "ID"

                cn.Close()

            End Using

        Catch ex As Exception
            MsgBox("Error: " + ex.Message)


        End Try

Please help...

[djjeavons]

Hi

If I understand correctly, your first combo box will contain the data from the Master table and based on the selected value you want to load a second combo box with data from a table that has the same name as the item in the Master table.

Thhe following will do this for you by concatenating the Text value selected in the cmbAux combo box to the SQL SELECT statement.

    Private Sub cmbAux_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbAux.SelectedIndexChanged

        If formLoaded = True Then
        secondComboBox.DataSource = Nothing

        Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Database3.accdb;")

            cn.Open()

            Dim command As New OleDbCommand(String.Format("SELECT * FROM {0}", cmbAux.Text), cn)

            Dim da As New OleDbDataAdapter
            Dim dt As New DataTable
            da.SelectCommand = command

            da.Fill(dt)


            secondComboBox.DataSource = dt
            secondComboBox.DisplayMember = "Names"
            secondComboBox.ValueMember = "ID"

            cn.Close()

        End Using

        End If

    End Sub

Note that the above code uses the SelectedIndexedChanged event of the cmbAux combo box which will fire as soon as you bind data to the control. For this reason, I added a boolean variable Private formLoaded As Boolean = False to the top of the code file (just below the Class declaration) which is updated to True once the form has loaded.

While this will work if all of your tables have an ID and Names column, it won't work if the structures differ and if that is the case then you will need to consider a different approach.

HTH

[Hawk123]

Thanks a lot.