Search code examples
vb.netms-access

How do relationships work and how can I implement it in my project?

I have a User table and Institutions table. I have put made a relationship with those two in access. Inst id is now in user table as a foreign key.

In my vb form, i populate a combobox with the institution names from inst table. When I select a username from a list box, his/hers relevant details are captured into textboxes on the form. But I dont know how to capture the Institution name using the foreign key.

'actions when listbox selection is changed
Private Sub listbox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
    cbInst_putData()
    cbAccountType_putData()
    If gbEditUser.Visible = True Then
        Dim selected_item As String = ListBox1.SelectedItem

        qr = "Select * FROM [User] WHERE Username = '" & selected_item & "'"
        Using cn As New OleDbConnection(cnString)
            cn.Open()
            Using cmd As New OleDbCommand(qr, cn)
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                While reader.Read
                    txtFirstname_edit.Text = reader.Item("Firstname").ToString
                    txtLastname_edit.Text = reader.Item("Lastname").ToString
                    txtAddress_edit.Text = reader.Item("Address").ToString
                    txtPhone_edit.Text = reader.Item("Phone").ToString
                    Dim dt As Date = Date.Parse(reader.Item("DateofBirth").ToString)
                    txtdob_edit.Text = dt
                    txtUsername_edit.Text = reader.Item("Username").ToString
                    txtPassword_edit.Text = reader.Item("Password").ToString
                    cbAccountType_edit.SelectedItem = reader.Item("AccountType").ToString
                    cbInst_edit.Text = reader.Item("InstitutionIDFK").ToString ' this is the combobox for institution list.
                    txtDesc.Text = reader.Item("Description").ToString
                    Dim checkActive As String
                    checkActive = reader.Item("Active").ToString
                   End While
            End Using
            cn.Close()
        End Using
    End If
End Sub

I want to store the institution name in the user table and also be able to capture it again. I did it without making a relationship before. By just having a institution field separately in the user table.

image

Im very new to vb. And completely new to posting on here even though ive been looking at other questions on this site. So please excuse me if my codes are bad and if im not posting properly.

Solution

  • Pretend that my Roasters are institutions and my Coffees are Users.

    I bind the list box and the combo box to the data in the Form.Load.

    When the selection in the ListBox is changed we get the RoasterId (the Foreign Key) associated with the selection. Next we loop through the items in the combo box Primary Key in the ID field. When we get a match, select that item and exit the loop.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim RoastersSql = "Select * From Roasters;"
    Dim CoffeesSql = "Select Top 10 * From Coffees;"
    Dim RoastersDT As New DataTable
    Dim CoffeeDT As New DataTable
    Using cn As New SqlConnection(ConGD)
        Using cmd As New SqlCommand(RoastersSql, cn)
            cn.Open()
            Using reader = cmd.ExecuteReader
                RoastersDT.Load(reader)
            End Using
        End Using
        Using cmd As New SqlCommand(CoffeesSql, cn)
            Using reader = cmd.ExecuteReader
                CoffeeDT.Load(reader)
            End Using
        End Using
    End Using
    ListBox1.DisplayMember = "Name"
    ListBox1.ValueMember = "ID" 'NOT the RoasterID, this is th PK of the Coffees table
    ListBox1.DataSource = CoffeeDT
    ComboBox1.DisplayMember = "Name"
    ComboBox1.ValueMember = "ID"
    ComboBox1.DataSource = RoastersDT
    UpdateUI(ListBox1.SelectedItem)
End Sub

Private Sub FillTextBoxes(item As Object)
    Dim drv = DirectCast(item, DataRowView)
    TextBox1.Text = drv("Name").ToString
    TextBox2.Text = drv("Type").ToString
End Sub

Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
    UpdateUI(ListBox1.SelectedItem)
End Sub

Private Sub UpdateUI(item As Object)
    Dim RoasterID = CInt(DirectCast(ListBox1.SelectedItem, DataRowView)("RoasterID"))
    For Each item In ComboBox1.Items
        Dim ID = CInt(DirectCast(item, DataRowView)("ID"))
        If RoasterID = ID Then
            ComboBox1.SelectedItem = item
            Exit For
        End If
    Next
    FillTextBoxes(ListBox1.SelectedItem)
End Sub