how to Update Stocks when used?

[kathnicole]

Hi All,

I am asked to build a database for a Printing Company that would prepare a Job quote for the Customers and review the stocks available to carry out the Job.

Stock Information performs the following:
1. Enter stocks information
2. update Stocks. say, when Eyelets stock is 20 and if we get another 5 eyelets today, then it should update the total eyelets to 20
3. Inform user when stocks runs out.

Quote information does the following:
1. enter the information and estimate the prize.
2. if stocks runs out to carry out the job, it should prompt the user.
Say for example, if the company has 20 Eyelets stock and it needs 25 eyelets for the current job to be done, while preparing the quote, the system , after estimating the prize, must tell the user that tstock runs out.
3. when the job is confirmed, it should then update the available stocks.
Say the company has 10 eyelts, and job needs 5 eyelets. once the job is confirmed, it should recduce the total available eyelets stocks to 5.


I need your help to perform this operation. As of now i am having a table for Stock,and Quote. i need someone to give me an idea of what i should do step-by-step.

This is very urgent and i want this to be done ASAP.

Need someone to guide

regards,
Kathy

[barry07]

Very briefly - you need a function which will be called by some event (button click or whatever) on the user form, which checks stock and allocates it if sufficient is available. I'm assuming a table called stock which contains a PartNo field (text) and a stock quantity field called "available_stock" (numeric - single)

Function CheckStock (quoteQty as single, sPart as string)
stockqty= dlookup("available_qty", "stock", "PartNo='" & sPart &"'")
if stockqty< quoteQty then
   Msgbox "Insufficient stock - only " & stockqty & " available", vbcritical
else
   Msgbox "Stock OK" ' this part is optional
   DoCmd.RunSQL "Update stock set available_qty=available_qty-" & quoteQty & " where Partno='" & sPart & "'" 'this line reduces the available stock
End if
End Function

Do you have a table that records what stock has been allocated against what quote? Might be a good idea in case you have to reallocate.

Hope this helps