Solved Trying to run an update query through a subform to update stock levels (1 Viewer)

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
I have a stock management system and I want it so when you put through an order on a form it automatically updates the stock level. I have the correct update query and I have added a macro to the form so when you click off of the quantity field it should update the stock level. However, it keeps popping up with asking me to fill in the parameters. I've figured this is because I'm trying to run this through a subform and it would work if it was running through a normal main form I do not wish to change this as I want people to be able to order multiple items at once. Just hoping there is a work around?
update query code:
Picture1.jpg
Picture2.jpg
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:59
Joined
Jul 9, 2003
Messages
13,391
It's not a good idea to store a stock level, it's much better if you calculate it at the time you need to know.
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
I understand that it's not ideal haha. It's for an assignment, and part of the brief is to keep track and update stock. Just wanted to know if there was a way to achieve this in a continuous subform without it asking for parameters. Thanks
Picture1.jpg
Picture2.jpg
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
Sorry about that, still new to this. Basically everything works when I use the parameters, I just want it to run automatically without them. Not really sure how to use TempVars lol, could you explain a bit more?
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:59
Joined
Sep 21, 2011
Messages
8,832

jdraw

Super Moderator
Staff member
Local time
Today, 03:59
Joined
Jan 23, 2006
Messages
13,840
Can you describe the assignment and the requirement specifically?
And, in plain English, your approach to solution? That is, how will you adjust stock level based on Ordered Product?
I have the correct update query...
Perhaps, you could also post your query SQL.
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
"Take into consideration factors such as placing orders, managing stock as well as updating data such as new customers etc. (dependent on your clients business)"
The way I have it running so far is that the customer orders through the 'Orders Form', this contains a combobox to enter a customer's ID and then a subform which is a continuous form where you select an item from another combobox and a quantity and then you can select another item and that quantity etc. My update query is set up to a macro which is attached to the add records command button's 'After Update' event.

SQL:
UPDATE tbl_products SET tbl_products.UnitsinStock = [UnitsinStock]-[Forms]![frmOrderDetailsSubform]![Quantity]
WHERE (((tbl_products.ProductID)=[Forms]![frmOrderDetailsSubform]![ProductID]));

However, when the update query is run it comes up with parameter value pop up windows. When I enter the product ID and quantity into them, it works. But I just want it to run automatically without the parameter boxes, if that's possible.

Sorry if I'm being difficult, I'm very new to this haha.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:59
Joined
May 7, 2009
Messages
13,797
you need to include the Parent form:

UPDATE tbl_products SET tbl_products.UnitsinStock = [UnitsinStock]-[Forms]![tbl_Orders]![FrmOrderDetailsSubform].Form![Quantity]
WHERE (((tbl_products.ProductID)=[Forms]![tbl_Orders]![FrmOrderDetailsSubform].Form![ProductID])));
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
I'm still getting the parameter problem :/ the sql code definitely still works when I'm inputting it through the parameter boxes, just not running automatically still.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:59
Joined
May 7, 2009
Messages
13,797
change your update query to:

UPDATE tbl_products SET tbl_products.UnitsinStock = [unitsinstock]-[tempvars]![p2]
WHERE (((tbl_products.ProductID)=[TempVars]![p1]));

change your macro to:

Code:
SetTempvar
    Name    p1
    Expression    [ProductID]
    
SetTempvar
    Name    p2
    Expression [Quantity]
    
OpenQuery
    Query Name    theNameOfYourQuery
    View        datasheet
    Data Mode    Edit
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:59
Joined
May 7, 2009
Messages
13,797
you move your macro to the subform's AfterUpdate event.
your button outside the subform is useless.
the record on the subform will be saved when you "leave" the subform.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:59
Joined
Sep 12, 2006
Messages
14,531
You need to consider your work flow.

At which point are you updating the inventory on hand? Is it when the order is accepted? It sounds like you are doing it at the point of selecting each product. So, what happens if you cancel the order during entry, or even subsequently. You have to reverse any amendments you have made to evaluate the free stock. What if the computer crashes with a partially updated order? Because of issues of this nature it's better just to verify the current stock is sufficient to fulfil the order at the time the order is placed, but not to update the quantity at all. Just re-assess all the line movements at the same time, when the order is accepted, to make sure they can all be fulfilled. Then the next use of this product will re-assess the current inventory, taking into account the sale you just processed.0

In your example, what happens if your first selection of the product notes that there are 10 items in stock, but the user takes a few minutes to enter the quantity he requires, and in the intervening period between first selecting the product, and entering the quantity, all the 10 have been issued to other users, and your user's entry makes the free stock go negative?

This is why you are being advised that this is a dubious idea. It's just not necessary to do this, and the stored quantity on hand may become out of synch with the real position. It's far easier not to try to maintain a current inventory on hand balance.
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
you move your macro to the subform's AfterUpdate event.
your button outside the subform is useless.
the record on the subform will be saved when you "leave" the subform.
It works!! Thank you for actually taking the time to help me so much and not just advising me not to do it! You are the best, much love <3
 

jackatharr

New member
Local time
Today, 08:59
Joined
Mar 20, 2021
Messages
8
As for everyone else, thanks for helping me! I understand that I shouldn't be doing stock updates like this on access. However, this is gonna get me a high mark for my assignment lol.
 

Users who are viewing this thread

Top Bottom