Link to home
Start Free TrialLog in
Avatar of JGutches
JGutches

asked on

Pull First name Last name, etc into textbox.

Hello,
I have created a database in Access that has several tables related to Patient Information Tables (Patient_Information, Patient_Primary_Insurance, ect)  The primary key is PtID in all related tables.  I have forms set up in vb that allow viewing information and adding/appending and delete.  I have another form that gathers all information together for billing insurance comapanies.  My problem is I have a combobox that is bound to DataSet111 wich hold Patient_Information and shows PatientLastName I would like for the combobox to combine FirstName, MidInitial, and LastName from Patient_Information table and further Once a name is selected Infromation is filled in the form such as address (which is broken down as PateintSteet, PatientCity, etc...) and Primary Insurance information from another tabe (Patient_Primary_Insurance).  I guess at the most basic I need to figure-out how to pull information from related databases and save the information into variables that I can then combine as I need into textboxes etc...

Thanks in Advance,

Jason
ASKER CERTIFIED SOLUTION
Avatar of Jeff Certain
Jeff Certain
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JGutches
JGutches

ASKER

Thank you both.  Both answers help me with different aspects of my problem.  I appreciate the prompt and accurate answers.
Your adherent,
Jason
First Step:

Getting information from database and binding it to Your Combo Box:

Private Function FillComboBox()

        'Getting Connection Information
        Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConnection As New SqlConnection(strConnection)

        'Build Sql Query(modify the sql query with your field names)
        Dim strSQL As String = "Select Distinct PtID, PatientFirstName + ' ' +  PatientMiddleIntial + ' ' +  PatientLastName as PatientName From Patient_Information"

        Dim objCommand As New SqlCommand(strSQL, objConnection)
        objConnection.Open()

        Dim dr As SqlDataReader
        dr = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
        ComboBox1.DataTextField = "PatientName"    'Concatinated Name is stored as text to display
        ComboBox1.DataValueField = "PtID"    'ID is stored as value for retrival
        ComboBox1.DataSource = dr
        ComboBox1.DataBind()

        'CleanUp Code
        dr.Close()
        objCommand.Dispose()
        objCommand = Nothing

       
    End Function


---------------------------------------------------------------------------------------------------
Second Step:

Getting the Other information from Database for the selected names:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        Dim strPatientID As String

        Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConnection As New SqlConnection(strConnection)

        Dim dr As SqlDataReader

        If ComboBox1.Items.Count <> 0 Then
            strPatientID = ComboBox1.SelectedItem.Value   'Get the Patient ID  for the selected patient name
        End If

       ' you may use sql join to get information from different tables for the selected patientID.
        Dim strSQL As String = "Select xxx , xxx, xxx, " _
                        & " Where PtID=  '" & Trim(strPatientID ) & "'"  


        Dim objCommand As New SqlCommand(strSQL, objConnection)

        objConnection.Open()
        dr = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

         'Retrive the other information through  data reader and display it in your form.
        If dr.Read Then
            txtContact.Text = dr("PatientStreet")
            txtContactPhone.Text = dr("PatientCity")
             etc etc.....

        End If


        'CleanUp
        dr.Close()
        objCommand.Connection.Close()
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Close()
        objConnection = Nothing


    End Sub

Good luck
Also for the ComboBox1_SelectedIndexChanged event to fire

you have to set ComboBox1 autopostback =  true.


Good luck.
mani sai,
Your awsome.  The datavaluefield set to the PtID is gold I was having problems trying to keep track of what record was being displayed.  Thank you for the logical step by step I have learned a great deal from this answer.
Again Thank you,
Jason