1 Replies - 999 Views - Last Post: 09 July 2013 - 01:12 PM Rate Topic: -----

#1 south73paw  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 88
  • Joined: 06-October 08

Display data from two tables based on combobox selection

Posted 04 July 2013 - 02:44 AM

Hi

I'm wanting to update text boxes based on a combo box selection.

I'm using Visual Basic 2010 and Access 2010.

My current code works as intended for the first 3 records, but then only the Patient_Name field displays. The Patients table had more records than the Wards table.

 Dim i As Integer = 0
    Dim dt_Patients As New DataTable
    Dim dt_Wards As New DataTable
    Dim cs As OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        cs = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Hospital.mdb")

        cs.Open()

        Dim query1 As New OleDbCommand("SELECT * FROM Patients", cs)
        Dim query2 As New OleDbCommand("SELECT * FROM Wards", cs)

        Dim da_Patients As New OleDbDataAdapter(query1)
        Dim da_Wards As New OleDbDataAdapter(query2)

        da_Patients.Fill(dt_Patients)
        da_Wards.Fill(dt_Wards)

        UpdateTextboxes()

        Patient_NameComboBox.DataSource = dt_Patients
        Patient_NameComboBox.DisplayMember = "Patient Name"

        cs.Close()

        cs.Dispose()
        da_Patients.Dispose()
        da_Wards.Dispose()

    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        btnPrevious.Enabled = True

        If (i < dt_Patients.Rows.Count - 1) And (i < dt_Wards.Rows.Count - 1) Then
            i += 1
            UpdateTextboxes()
        Else
            btnNext.Enabled = False
        End If

    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        btnNext.Enabled = True

        If (i > 0) Then
            i -= 1
            UpdateTextboxes()
        Else
            btnPrevious.Enabled = False
        End If

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        ClearForm()
        EnableEdit()

        AddNewPatient()

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

    End Sub


    Private Sub Patient_NameComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Patient_NameComboBox.SelectedIndexChanged

        i = Patient_NameComboBox.SelectedIndex
        UpdateTextboxes()

    End Sub

    Private Sub UpdateTextboxes()

        Patient_NameTextBox.Text = CStr(dt_Patients.Rows(i)("Patient Name"))
        Ward_NameTextBox.Text = CStr(dt_Wards.Rows(i)("Ward Name"))
        Sister_in_chargeTextBox.Text = CStr(dt_Wards.Rows(i)("Sister in charge"))

    End Sub

    Private Sub ClearForm()

        Patient_NameTextBox.Clear()
        Ward_NameTextBox.Clear()
        Sister_in_chargeTextBox.Clear()

    End Sub

    Private Sub EnableEdit()

        Patient_NameTextBox.ReadOnly = False
        Ward_NameTextBox.ReadOnly = False
        Sister_in_chargeTextBox.ReadOnly = False

        Patient_NameTextBox.Focus()

    End Sub

    Private Sub AddNewPatient()

        Dim name As String = Patient_NameTextBox.Text.Trim
        Dim ward As String = Ward_NameTextBox.Text.Trim
        Dim sister As String = Sister_in_chargeTextBox.Text.Trim

        Dim updateDatabase As New OleDbCommand

        cs = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Hospital.mdb")

        updateDatabase.CommandText = "INSERT INTO Patients(Patient Name), Wards(Ward Name, Sister in charge) VALUES name, ward, sister"

        cs.Open()
        updateDatabase.ExecuteNonQuery()
        cs.Close()

    End Sub

End Class



The problem with my logic at the moment, I think, is to do with the variable i. When the 4th name in the combo box is selected, i is one more than the number of records in the Wards table, so the Ward_nameTextBox and Sister_in_chargeTextBox doesn't update.

What do I need to do to get these two text boxes to display the corresponding fields in the record based on the name sleected in the combo box?

Thanks! :rolleyes2:

Is This A Good Question/Topic? 0
  • +

Replies To: Display data from two tables based on combobox selection

#2 south73paw  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 88
  • Joined: 06-October 08

Re: Display data from two tables based on combobox selection

Posted 09 July 2013 - 01:12 PM

Can any one help me here?
Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1