User to be warned of the current stock

[falcondeer]

Hi everybody
I created this query (pic-3) to count down the quantity of any prescribed item and it is working fine and updating the table (pic-1)
Now what I want is this:
When the user want to update the quantity combobox (pic2), I want to show him/her a message box saying either "There is enough stock or not" based on the number in ItemCurrentStock field (pic-1), So they either proceed with the request or stop it. I do not want to show them the whole table (pic-1) though.
How can I do that.
Thanks

 

[June7]

Could use DLookup() domain aggregate function. Expression can be in textbox or VBA.

 

[arnelgp]

I created this query (pic-3) to count down the quantity of any prescribed item and it is working fine and updating the table (pic-1)

did you consider any worst case scenario.
when entry was saved and by mistake saved wrong Quantity (instead of 2 he but 3).
then he want to edit it to correct the discovered wrong entry, how do you tackle that.

 

[falcondeer]

did you consider any worst case scenario.
when entry was saved and by mistake saved wrong Quantity (instead of 2 he but 3).
then he want to edit it to correct the discovered wrong entry, how do you tackle that.

It is a combobox limited to 1,2 only.

 

[arnelgp]

ok 1 or 2 he put 2 and saved.
on reviewing his entry, it should have been 1.
he browse for the balance in the table.
he edit the record and changed to 1.
1 should be returned to stock and only 1 should be deducted.
open the table what is the balance.
if you got correct balance then your Query is doing what
it is supposed to do. but i doubt.
this can only be done through vba.

 

[falcondeer]

ok 1 or 2 he put 2 and saved.
on reviewing his entry, it should have been 1.
he browse for the balance in the table.
he edit the record and changed to 1.
1 should be returned to stock and only 1 should be deducted.
open the table what is the balance.
if you got correct balance then your Query is doing what
it is supposed to do. but i doubt.
this can only be done through vba.

OMG, it keeps deducting every time the combobox is updated. I did not notice that, thank you.
It looks I have wrong query.
Now what should I do.

 

[arnelgp]

add code to the subform's AfterUpdate event:

private sub form_afterUpdate()
dim sql As String
sql = _
    "Update tblItemsSB Set ItemCurrentStock = Nz(ItemCurrentStock, 0)  + " & _
    Nz([Combo44].OldValue, 0) & " - " & Val([Combo44]  & "") & " " & _
    "Where ItemName = '" & [PRSB_Items] & "';"
currentdb.Execute sql
end sub

 

[June7]

Don't save inventory balance. Calculate when needed. Review http://allenbrowne.com/AppInventory.html

 

[arnelgp]

OMG, it keeps deducting every time the combobox is updated. I did not notice that, thank you.

it is suggested that you don't save balance to the table, but rather compute them on the fly.
i sugggest that you only keep the Beginning balance and add/subtract the quantity from the transaction table.
this is flexible in the case where at some point of time (every end of year or every end of 3 years), you want to Zero
out your transaction files to 0 records.
you save the inventory table and transaction table first to another db.
compute the balance of each items and save to Beginning balance field.
then delete all records from transactions.

 

[falcondeer]

add code to the subform's AfterUpdate event:

private sub form_afterUpdate()
dim sql As String
sql = _
    "Update tblItemsSB Set ItemCurrentStock = Nz(ItemCurrentStock, 0)  + " & _
    Nz([Combo44].OldValue, 0) & " - " & Val([Combo44]  & "") & " " & _
    "Where ItemName = '" & [PRSB_Items] & "';"
currentdb.Execute sql
end sub

Sorry to say it, it is not doing anything to the stock table (tblItemsSB),
When I run the query it subtract the new value, same thing like before.
Sorry, I am not that expert.

 

[arnelgp]

sorry it should have been in BeforeUpdate Event of the form not AfterUpdate.

 

[falcondeer]

sorry it should have been in BeforeUpdate Event of the form not AfterUpdate.

Perfect, thanks a lot.

 

[June7]

I think Pat meant "transactions".

 

[Gasman]

auto correct strikes again. Thanks.

Which is why I prefer, when I can responding from a PC. Then, any mistakes are mine.

 

[falcondeer]

Updating quantities directly is a poor way to manage inventory because it is not auditable. It is best to create transitions to add/delete from inventory so you have a record of each change.

Thanks very much for your reply, but what is the easy way to do so.

 

[June7]

Did you review article in post 8?

 

[falcondeer]

sorry it should have been in BeforeUpdate Event of the form not AfterUpdate.

Thanks a lot for your help, one more thing,
I want to stop the user from proceeding if the stock reaches 0 and get him/her a message saying "Out of Stock You can't proceed".
Is that possible.

 

[falcondeer]

Did you review article in post 8?

Not really, I will go through it later to learn the good way. Thanks

 

[arnelgp]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

 

[falcondeer]

i noticed on the form that you posted that there is a combobox for size?
which you don't have in your item table.
if you want to incorporate the "size" just add it to Item field, example:

1 record for: Elastic Ankle Support, small
1 record for Elastic Ankle Support, medium
etc.

your Combobox for "Item" should also include the "ItemCurrentStock" field,
so the Rowsource of your combo should be:

select Item, ItemCurrentStock from yourTable

then you add code to this combo's (Quantity combo) BeforeUpdate event to show the CurrentStock:

private sub Quantity_beforeUpdate(cancel as integer)
cancel = me.Item.column(1) < Me.Quantity
if cancel then
msgbox "Not enough stock to accommodate this request"
end if
end sub

Thanks for your suggestion, however

Because I have the two combos (item, size) cascaded, I moved the code in PRSB_Size not in PRSB_item and when selecting any quantity in the combo44 it is calculating but it exceeds 0 so I keep having negative numbers.

This is what I put in the before update event:
Cancel = Me.PRSB_Size.Column(1) < Me.Combo44
If Cancel Then
MsgBox "Not enough stock to accommodate this request"
End If

and this is the select statement in row source of size:
SELECT Size, ItemQuantity FROM tblSize WHERE ItemName = PRSB_Items;

Attached is tblsize (pic-4).

I appreciate your help