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

how to Update Stocks when used?

P: 33
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

Sep 5 '07 #1
Share this Question
Share on Google+
1 Reply

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

Expand|Select|Wrap|Line Numbers
  1. Function CheckStock (quoteQty as single, sPart as string)
  2. stockqty= dlookup("available_qty", "stock", "PartNo='" & sPart &"'")
  3. if stockqty< quoteQty then
  4.    Msgbox "Insufficient stock - only " & stockqty & " available", vbcritical
  5. else
  6.    Msgbox "Stock OK" ' this part is optional
  7.    DoCmd.RunSQL "Update stock set available_qty=available_qty-" & quoteQty & " where Partno='" & sPart & "'" 'this line reduces the available stock
  8. End if
  9. 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
Sep 5 '07 #2

Post your reply

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