updating stock

[Tech]

hi there.

in the order form database I have, it has number in stock of product, eg 100. this figure comes from another form (so it's a subform)

what i need to know is, if we order 4 (qty = 4) I know how to subtract that from the number of stock, but would it not subtract it every single time u move records back and forward?

how can I tell it to only subtract it once, unless the QTY ordered has been changed?

thank-you

 

[Elana]

Check out this article in the MS Knowledge base - I think it will do what you need:

http://support.microsoft.com/default.aspx?scid=kb;en-us;252813

 

[Tech]

I don't think that will work for me, it may do the same thing but I would then have to update the form and make major changes? like add new fields or start again?

 

[Tech]

there is also no updateTo cell?

and also, what would the name of my subform be? will it just be the same name as the original form? (that is going to be put in as subform)

 

[Elana]

Since I haven't seen your app, I'm not sure what it is you're asking me. The MS Knowledge Base instructions are just a guideline to give you the understanding of how to accomplish what you need to accomplish. You tailor their instructions to fit your particular application (form names, etc.)

HTH

 

[Tech]

ftp://temp:temp@techftpserver.co.uk/mdffc1.zip


basically, when the customer has ordered QTY of 4 for a product, to take away that from the total stock BUT...it shouldn't do it ALL the time, when we move from record to record, ONLY when the QTY of the product ordered has been changed, will it update the qty in stock

??

 

[Elana]

I must apologize, but I don't have time right now to work with you on this. My goal in directing you to the MS site was that you could look at the code they supplied and adapt it to your specific situation.

It seems to me that placing the suggested (modified) code on the BeforeUpdate event of your quantity ordered field would accomplish what it is you are trying to do.

Perhaps one of the other forum members can give you more help if you need it.

Good luck.

 

[Tech]

k, thx, n e more suggestions folks, post here

 

[KevinM]

You shouldn't store stock levels in a table.
These should be calculated from your trans table via a total query.....

TotalQTY In - TotalQTY out.

This is just one way. Larger recordets will need a 'Stock take' table which will act as a 'starting point' for the calculations as calculation from 1000s of transactions will be VERY slow and inpractical.

Storing the QTY in the 'products table' will just cause you lots of problems as you will need to make sure that you can have it automatically adjusted from everywhere in the db.
This will be impossibe if trans records are altered in table view.

Your original question emphasises this.

 

[Tech]

but it technically belongs to that table/form.

so, what else can be done?

i mean, surely there is a way, even using vba on the text box? or something?

 

[KevinM]

but it technically belongs to that table/form.

No it doesn't, it's derived data.

 

[Tech]

hm

well, it should belong to a table cause it's gonna b in the database, the product details form has a certain qty availible/made and then this is taken to the products purchased form

anyway, another way round?

 

[KevinM]

Well I have a db (table) that stores my Date of Birth (NOT my age) and it will easily calculate my correct age at any given date.

In another db I can calculate the VAT of any given price without storing it in a table.

Stock QTY is the same, althoug hit requires a couple of 'Total queries' to calculate it.

 

[Tech]

hmm...ok

it's just that, I can't figure out how it's gonna work cause when the manufacturing department puts in new products, they have to enter the qty availible...so other ppl know how much is gone/left etc...

and all the availible stock is then told in the purchases subform/form and therefore calcs can be done to subtract/add etc..

 

[Rich]

http://users.bigpond.net.au/abrowne1/AppInventory.html

 

[Tech]

:cries:

lol

thx..

 

[KevinM]

That's the link I was looking for Rich.

I know it's just coursework you are doing Tech so maybe you can get away with it (in a table) as far as the assignment is concerned.

Basically you will need to call a vb calculation like...

[StockQTY]=[StockQTY]-[QTYIssued] on several event procedures on the form to cover 'all bases'.

 

[Tech]

yeh, tyvm