Insert Value from one table to other table

See more:

VB

Dear all
My name is Sarfaraz.
I am developing a small project in vb6 and access.
My problem is like that I have a table called investigations which has investigations and rate and other table patients where i am storing patient name receipt no. investigations prescribed and rate.
Actually i want the rate to be automatically inserted into the corresponding field of patient table from the investigations table for the corresponding item.
All the other things are going quite well but i do not know how to do that.
My following code is running successfully. But now i want to insert rate also.
Please help
Thank you
______________________________________

Connection

sql = "patients"
Set rs = New ADODB.Recordset
rs.Open sql, Con, adOpenDynamic, adLockOptimistic
 Dim x As Long
 For x = 0 To List2.ListCount - 1
With rs
    .AddNew
        !Receipt_no = Text1.Text
        !OPD_MRD = Text2.Text
        !Patientname = Text3.Text
               
     !Investigations = List2.List(x)

       
        !Receipt_date = Label6.Caption
        !Receipt_time = Label7.Caption
        
    .Update
End With
    Next
    MsgBox "New student record successfully saved..", vbInformation, "Success"