Changing or editing

[goldstar]

Hello All,

Im struggling to get this idea and work out how to go about solving this issue.

What i want to happen is when a item is ordered and quantity selected, it is placed into a listbox, this quantity is removed from the item table. Then if the item is edited, for example if a total of 2 is selected first then it is edited to say 4, what i want it do to is remove another two from the order table. Or the other way if quantity of 4 is selected and it is changed to 1, then i want it to add 3 back on.

Im really stuck on this, any help would be appreciated

[janders468]

I am not sure how your table is structured, but given what you are doing I would assume that you have a master item table of some sort that lists the items you have and then another column that shows how many are in stock (an inventory on hand column perhaps) and an order table that shows the items ordered and the quantity. If this is the case then all you need to do is run a query that subtracts the quantity ordered in the orders table from the quantity on hand in the items table. You could have this query run every time an addition or deletion is made, and simply put in some logic that controls for things like ordering more items than are in stock. So this is how I am viewing it.

Items
Item Quantity

OrderedItems
[Items Ordered] [Quantity Ordered]

The query would then be

UPDATE Items 
INNER JOIN OrderedItems ON [Items].Item=[OrderedItems].[Items Ordered] 
SET Items.Quantity = [Items].Quantity-[OrderedItems].[Quantity Ordered];

If the amount ordered is adjusted down you could do the exact same thing only change the query to add Items back instead of subtract, or you could utilize the same query by interpreting putting items back as ordering negative amounts. For instance if initially a quantity of 4 is selected and then the quantity is decreased by 2 it would be interpreted as ordering -2 items which would put 2 items back into the inventory on hand column.
Hopefully that helps if it's off the mark let me have a little more detail and I'll see what I can come up with.

[goldstar]

i have an Items Table - which contains item details and stock levels
Itemhire table - item ordered, stock (from the items table) and quantity (this is the amount which the client selects)

Ive had a look and tried working with what you have produced but it still does not like it.

Would you have a sample of this?

All this help is much appreciated... Thanks

[goldstar]

im still having issues with the information provided, would you have an example of this

[janders468]

I'll try to work up an example on this, sorry I didn't get back sooner I didn't see that you had replied.

[goldstar]

Thanks

That would be much appreciated!!!!

[janders468]

I was re-reading this post and realized I might have over-answered your question. What part were you having trouble with initially? If you have a control that holds the number of items ordered then a query can adjust the inventory to the inventory - quantity ordered. Are you having trouble with figuring out how to work that into a query? I apologize it has taken me so long between posts I've been very busy, but if you are still having a problem let me know.