By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

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

P: 23
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.....
Nov 11 '13 #1
Share this Question
Share on Google+
4 Replies


P: 16
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):

Expand|Select|Wrap|Line Numbers
  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
Nov 11 '13 #2

P: 23
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
Nov 12 '13 #3

P: 16
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
Nov 12 '13 #4

zmbd
Expert Mod 5K+
P: 5,397
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.
Nov 14 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.