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.
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.
CodePudding user response:
Do the following:
- Make sure that you created a relationship (one-to-many).
- Create a new query and add both tables.
- Add all the fields from both tables in the query (except for the foreign key).
- Make the query the data source of your
Form
.
CodePudding user response:
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