Lookup Field

[alwsid]

Hello...
I want to know how to use lookup field in my vb form.
I've 2 table which related each other.
So I want to display a customer name in my Order Form which have a same custID.

In Ms Access we can use DLookup but in VB I've no idea.
Hope someone can help me...

Waiting for response.

[Mildumar]

Try this function.
Function DB_DLookup(cn As ADODB.Connection, sField As String, sTable As String, sCondition As String) As Variant
Dim rs As ADODB.Recordset

Set rs = cn.Execute("SELECT " & sField & " FROM " & sTable & " WHERE " & sCondition)

If Not rs.EOF Then
DB_DLookup = rs.Fields(0)
Else
DB_DLookup = Null
End If
End Function

[Clunietp]

Just search the second table using the CustID in the WHERE clause, and get your data from a recordset.

[alwsid]

If I'm trying to use DAO to call Mildumar Function, How to do it.

[Clunietp]

Are you using the data control? If so, try this:


dim lngCustID as long

'get custid from current record
lngcustid = data1.recordset.fields("CustID").value


Dim rs As Recordset


Set rs = Data1.Database.OpenRecordset("Select * from Customers where CustID = " & lngCustID)


text1.Text = rs.Fields("Customer Name").Value


Set rs = Nothing


HTH

Tom

[This message has been edited by Clunietp (edited 01-05-2000).]

[alwsid]

Yes Tom I'm using the data control.
But Tom for the the lookup field i want to display to unbound Textbox(Text1.Text).

That mean when custID in the Order Form is same with Customer Table, The Customer Name will display in the unbound Textbox(Text1.Text) in the Order Form.

[Clunietp]

Hi alwsid

I have modified my previous post so that it should now work for your purposes.

I create a new recordset off of the already connected database (via Data1 data control) and search in that recordset for the record with your specific CustID.

If that still is not right, please be more specific

Tom

[alwsid]

Yes Tom now it's work ...

Thank's for helping.