Hi -- I'm learning how to use VB to manipulate databases... not to mention learning how to work with databases in general.

I've created a form, which i want to pull data from two tables. I've managed to partially achieve what i'm looking for but cant quite get exactly the result i need.

Basically, the data pulled from table one is a string (Name). This string (Name) is related to the data i need from table two, which is also a string (Partner's Name). However, to spare duplication, table two's row data is referenced in table one by the row's unique reference number.

I've been trying to find away to search table two's reference column (ie column 0) using the reference number contained within table one, based on the record pulled from table one -- i hope i'm still making sense.

The aim is to have one text box show Name and the other to show Partner's Name. What i've tried follows (it doesnt work... but you can see what i've been trying to do...)


Code:
Imports System.Data.OleDb
    Private Sub btnRetrieveData_Click(sender As Object, e As EventArgs) Handles btnRetrieveData.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider$ = "Provider=MICROSOFT.ACE.OLEDB.12.0;"
        Dim dbSource$ = "Data Source = C:\Desktop\Access.accdb"
        Dim DS As New DataSet
        Dim DA As New OleDb.OleDbDataAdapter        
        Dim RefNum%

        tbl(0) = "Select * From tblName"
        tbl(1) = "Select * From tblPartnerName"

        Connect()
        DA = New OleDbDataAdapter(tbl(0), con)
        DA.Fill(DS, "NameTable")

        DA = New OleDbDataAdapter(tbl(1), con)
        DA.Fill(DS, "PartnerNameTable")

txtName.Text = DS.Tables("NameTable").Rows(0).Item(1)

RefNum = DS.Tables("NameTable").Rows(0).Item(2)

With DS.Tables("PartnerName")
'I've tried looping through each row in the table until RefNum is found... but couldnt figure out how to return the value i needed (which always rests in column 1)

txtPartnerName.Text = DS.Table("PartnerName").Rows(????).Item(1)

DisConnect()
End With
End Sub
Any help appreciated (even if it's a point in the right direction).

(I'm kinda late for work, so have rushed this... let me know what clarification you need and i'll update the post when i get back -- thanks again)

GTD