How can i relate one field in a table with an other field of another table?

[moonrb]

I have two table named 'stock' & 'delivery' with following fields-

stock-
1.product id
2.date
3.quantity

delivery
1.memo no
2.product id
3.date
4.quantity

I need whenever i input value in 'quantity ' field of "delivery" table it should search automatically, is that quantity available in "stock" table or not. if not available, a massage should appear like this "quantity not available" on moving to other field or button or on pressing tab or clicking any where of the form. PLZ give me a proper solution. thanx.....

[CJ_London]

There are a number of ways you can do this however I would do the following:

in the after update event of your quantity control on your form put the following (change names to suit - txt... refers to the name of the control on your form):

Dim rst as Recordset
 
set rst=currentdb.openrecordset("SELECT quantity FROM Stock WHERE [Product id]=" & txtProductID)
if rst.fields(0)-txtQuantity<0 then
    msgbox "Only " & rst.fields(0) & "available, please reduce quantity"
end if

[moonrb]

THANK U SO MUCH
BUT I CAN NOT UNDERSTAND THE BOLD PORTION BELOW. PLZ EXPLAIN


(change names to suit - txt... refers to the name of the control on your form):
1. Dim rst as Recordset
2.
3. set rst=currentdb.openrecordset("SELECT quantity FROM Stock WHERE [Product id]=" & txtProductID)
4. if rst.fields(0)-txtQuantity<0 then
5. msgbox "Only " & rst.fields(0) & "available, please reduce quantity"
6. end if

[CJ_London]

On your form where you are entering the quantity you will have selected a product. You have not said what the name of any of the controls on your form are called so to identify them I prefixed them with txt.

i.e. txtProductID should be replaced with the name of your productID control on your form

[zmbd]

moonrb

Keep it simple... follow MS tutorial:
Create relationships for a new database

Once you have this down, please work thru this tutorial:
MS Access 2010 Tutorials Please create the project that this tutorial walks you thru. By doing so, you will learn a great deal about the basic concepts behind what Access is and is not.