Conditional entry into form- Intresting

[shanky365]

Hello all.
i am creating an inventory sytem
i have created a table for all items recieved and a table for all issued items. i have given simple relationship between item code.
eg tables are:
item code - quatity recived



and
item code - issued qty

nw i have created a form also to enter the values into table.
so, i want to put a restriction that whnever somebody opens a issue form, he cannot issue more items than in recived quantity, isnt it?
so condition is for all line items
issued qty (of any item) <= recived qty (of that item)

if he does, a msg should pop up or he should not be able to save.

please help!!!!!! Atleast tell me some directions whrer i shud go

[outhwaik]

I would suggest that you put that check in code to be executed before exiting. If you have a 'save and exit' button, or similar, I'd put the code behind that. You can reference the text boxes on the form (assuming thats what you're using) and check the condtions you need. You can then choose to only run the DoCmd.close command if the condition is satisifed, and just display a message box if not, telling the user what they did wrong

[rpeare]

I'm assuming your items received table is going to have more than one row per item? if so you'll have to sum all the received items then sum all the issued items to see if your system thinks you have any of the item left. Then check against that number.

[shanky365]

thanks friend.... but could u tell me in a bit more detail, coz i m new to access. Yes i am using Form and have a button called save.

[shanky365]

yes i have the sum of issue and net balance query, but how to check or compare these while using the form to enter values.

[rpeare]

Create a query that takes your initial inventory amount, adds all the receipts of that item, subtracts all the issuances of that item then lookup that value (either by dlookup or through other vba code) to populate your form or as a check against what the person is trying to take out. You should be able to create a single query that has a list of all your items (with the primary key) and the total amount on hand if your tables are as you say they are. Once you create that query it's pretty simple to use either the dlookup

Syntax would be something like

dlookup("[OnHand]", "Qry_ItemOnHandLookup", "[ItemID] = " & forms!formname!itemIDfield & ")

where you'd put in your own object names or through vb code to retreive this info and do the compare.

[shanky365]

@ rpeare:
hey great!!! it worked..
but a simple pblm.. i have a form in which you enter the "item code "(primary key) then go for "issue to".. nw as soon as i enter item code i want the bal qty texbox to get updated and show the bal qty. this isnt happening. if i enter eg 4 in item code den go to design view n come back top form view , den bal qty gets updated in textbox. can i provide sum button or sumthn where as soon as i enter item code, textbox of bal qty gets updated.
btw.. thanks 4 help

[rpeare]

I can't tell how your form is set up but if you have a text box called balqty (do not use spaces in object names in access) after you choose an item you would do something like this in the ON EXIT property of your item selection

balqty = dlookup("[OnHand]", "Qry_ItemOnHandLookup", "[ItemID] = " & forms!formname!itemIDfield & ")

Note it's the same code I used before.

If you are adding multiple items to a single request (for instance with a PO database) you would likely have to work it a little differently. If balqty is actually a field in your query or table it won't get updated because you are trying to insert a formula into a field that has a value.