Stock control

[astarbyfar]

Can anyone give me the an idea. Ive already posted this in Query forum and now relize that it should be in VBA or modules. Im creating a stock control system and when i click on a Pay button i want Quantity that is in Stock Table to be deducted by 1. The form that the pay buttojn is on is based on a query. Mile this seems to be your forte Please help!!!!

 

[Rich]

Use queries to calculate stock levels, or search here for inventory

 

[astarbyfar]

Thanks, ive nearly done this ive inserted an SQL ststement UPDATE Stock SET Quantity = Quantity -1 but i need it to just update the levels for the produnt number that i am inserting from the query anyone got the SQL statement for this?

 

[Mile-O]

Use the WHERE statement in your SQL:

UPDATE Stock SET Quantity = Quantity -1 WHERE bla bla bla

 

[astarbyfar]

Ive done that but what goes where the bla bla bla goes. I need it to read in the product number that the user would enter in a query

 

[Mile-O]

Urgh! Personally I don't like queries that ask the user for a value as they can return unexpected (or no) results if the person using isn't on the exact wavelength.

Surely the product number would be on the current form when running this, if so this would be your syntax:

"UPDATE Stock SET Quantity = Quantity -1 WHERE [Product Number] = " & Me.txtYourProductNumber & ";"

 

[astarbyfar]

nice one genius!!!!

 

[astarbyfar]

Sorry but this does not work the code ive got from Access help is

UPDATE Stock SET Quantity = Quantity - 1 WHERE Forms![EPOS].[Item#].Value;

Where EPOS is the name of the form which will have the product details on and Item number is the unique identifier. What i want is for just that unique identifier that is being inserted via a query box to deduct one form the quantity in stock any ideas