How to Perform "Lookups" from 2 Tables

[salimwng]

I have a Database (MS Access 2000) in which there's 3 Tables(E1_Customer, E2_Supplier and E3_Product). Two Attributes in the E1_Customer table are the foreign keys of Table E2_Supplier (Supplier_ID) and E3_Product (Product_ID) respectively.

Example

Table E1_Customer:

Customer_ID Name Address Tel. Supplier_ID Product_ID
45 Salim Whatever 123 12 34
49 John Whatever 123 14 35
50 Tania Whatever 145 14 36
67 Lilan Whatever 145 17 37


Table E2_Supplier:

Supplier_ID Name Address Rank
10 Bonne Apetit Whatever 1
12 Fresh_Fruit Whatever 2
17 QuickDel Whatever 3

Table E3_Product:

Product_ID Description Unit Class
30 Dried Sultanas Kgs Perishable
31 Canned Tune Box/100 Expiry
34 Fresh Fruits Kgs Perishable
37 Frozen Fish Kgs Frozen

In my form, i am using a listbox for the Supplier_ID and Customer_ID fields.

The problem is;

I would like to obtain the Supplier_ID and Product_ID when my cursor lands onto them and next to each field, the Name and Description appears that will remind me about them. If i choose another Supplier_ID or Product_ID the Name and Description changes accordingly

I have been able to work out ( Save, Modify and Delete) the rest of the field, but for these two, i can't make it.

I have been searching the Threads in the forum { Linking two tables, Relationships, DataBinding, Foreign Keys, etc) but could not get something.

I am doing these programmatically ( i.e. at runtime ).

Can someone help me please. I am new to VB.net completely.

Thank you very much in advance.
Salim

 

[DougP]

I would go in Access and use the QBE grid to create a query for those tables with the fields you want. let Access do the work for you. Then click the SQL tab and copy the SQL code it creates.
back in VB.net
this SQL will be pasted in a VB.NET control, which control you use is up to you.
A grid is easiest. look in Help there is a step by step procedure for settign up a grid. you will change the SQL to use yours.




DougP, MCP, A+