VB6.0 with DataControl Database Programming

Hey, can I ask you something? I'm using VB6.0 and I have s开发者_运维知识库ome problem with the connection of my Database through DataControl. I have a table named tblEmployee and the other one is tblPosition then I passed the value of the two tables to two DataControls respectively. How can I then get the value of a certain row of Position field. With my code, my Position field returns only the first row. Here's my code

Private Sub cmdSearchEmployee_Click()
With datEmployee.Recordset
    datEmployee.Recordset.Index = "idxid"
    datEmployee.Recordset.Seek "=", txtIDNumber.Text

    If .NoMatch = True Then
        MsgBox ("No Record Found!")
    Else
        Me.txtLastName.Text = .Fields("lname")
        Me.txtFirstName.Text = .Fields("fname")
        Me.txtMiddleName.Text = .Fields("mi")

        With datPosition.Recordset
            Me.txtPosition.Text = .Fields("position")

        End With
    End If
End With

End Sub

I can't see that you have "passed the value" to your DataControl named datPosition. Could this be the problem? e.g. where you have

    With datPosition.Recordset
        Me.txtPosition.Text = .Fields("position")

    End With

...should be more like this:

    With datPosition.Recordset
        .Index = "some_index??"
        .Seek "=", "some_value??"
        Me.txtPosition.Text = .Fields("position")
    End With

Also consider using the recordsets' Filter to remove the rows that do not match your criteria then RecordCount to loop through the rows that do match your criteria.

Further consider returning a single recordset by creating a join between tblEmployee and tblPosition, either in SQL code or returning a hierarchical recordset using the MsDataShape with its SHAPE syntax.