User to be warned of the current stock (1 Viewer)

falcondeer

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
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
 

Attachments

  • pic-1.png
    pic-1.png
    16.5 KB · Views: 174
  • pic-2.png
    pic-2.png
    76.1 KB · Views: 250
  • pic-3.png
    pic-3.png
    12 KB · Views: 242

June7

AWF VIP
Local time
Yesterday, 23:26
Joined
Mar 9, 2014
Messages
5,424
Could use DLookup() domain aggregate function. Expression can be in textbox or VBA.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
add code to the subform's AfterUpdate event:
Code:
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
add code to the subform's AfterUpdate event:
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:26
Joined
May 7, 2009
Messages
19,169
sorry it should have been in BeforeUpdate Event of the form not AfterUpdate.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 19, 2002
Messages
42,981
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.
 

June7

AWF VIP
Local time
Yesterday, 23:26
Joined
Mar 9, 2014
Messages
5,424
I think Pat meant "transactions".
 

falcondeer

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
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

AWF VIP
Local time
Yesterday, 23:26
Joined
Mar 9, 2014
Messages
5,424
Did you review article in post 8?
 

falcondeer

Registered User.
Local time
Today, 00:26
Joined
May 12, 2013
Messages
101
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.
 

Users who are viewing this thread

Top Bottom