Disallow A User From Over Allocating Available Stock (1 Viewer)

Local time
Today, 21:07
Joined
Feb 27, 2022
Messages
49
Hi all,

I've got a database which records purchases and sales. What I'm trying to do is if say we have 20 Units of Product A available, to stop a user from accidently entering anything over 20 sales of Product A. The same should be applied for Product B, C etc.

I've been unsuccessful for a while now on how to get my form to do this and produce a messagebox to warn the user and prevent the data entry so I thought I would reach out for some guidance.

I've put together a really simple database attached that is similar to my actual database. If you load the form "frmMain", you will see 2 subforms on there. The first shows the available inventory in stock and the second is for the user to enter the allocation of that stock. When the user enters the "Quantity" amount on the Allocation of Units form, I was wanting the database to check if there was enough stock of that product, or produce a message to tell the user to correct the quantity and prevent the entry.

Could someone please enlighten me how to prevent an overallocation of a product please?
 

Attachments

  • ReferenceDB.zip
    69 KB · Views: 198

Ranman256

Well-known member
Local time
Today, 06:37
Joined
Apr 9, 2015
Messages
4,337
then user enters quantity, do a check in the afterUpdate event:

Code:
sub txtQty_AfterUpdate
if txtQty > txtAvail then
   msgbox "You cannot order more than what's available"
   txtQty = txtAvail    
endif
end sub
 
Local time
Today, 21:07
Joined
Feb 27, 2022
Messages
49
Thanks Ranman256. I understand your logic here, but I don't know how exactly to apply it with references to the subforms.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Jan 23, 2006
Messages
15,361
I would suggest the BeforeUpdate rather than AfterUpdate event.
 

bob fitz

AWF VIP
Local time
Today, 10:37
Joined
May 23, 2011
Messages
4,717
Thanks Ranman256. I understand your logic here, but I don't know how exactly to apply it with references to the subforms.
The attached file shows one way of doing what you ask but IMHO I think your table design is not the best for inventory control.
 

Attachments

  • RefDB BobFitz01.zip
    69.1 KB · Views: 181

jdraw

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Jan 23, 2006
Messages
15,361
I agree with Bob -- table design could be improved. I note that he used the BeforeUpdate event. (y)
 
Local time
Today, 21:07
Joined
Feb 27, 2022
Messages
49
The attached file shows one way of doing what you ask but IMHO I think your table design is not the best for inventory control.
Hi Bob,

Thanks so much for showing me how to do this. I totally agree that the table design is not the best for inventory control- in my example database here, I tried to create an example of what I was trying to achieve, so the setup is a little different to my actual database. Part of that reason for doing this was I thought that once I find out how to do it, I would have to take it and try and apply it to my own situation so I'm forced to learn a little. And learn I did! In applying what you showed me, I had a small problem with my actual database that I couldn't figure out for a few hours- hence this reply is late.


Finally sorted and all good now- really appreciate your specific help. Fun learning.

Have a great week.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:37
Joined
Sep 21, 2011
Messages
14,038
Always best to show the real scenario, even if you have to load with non sensitive data.
 

bob fitz

AWF VIP
Local time
Today, 10:37
Joined
May 23, 2011
Messages
4,717
Hi Bob,

Thanks so much for showing me how to do this. I totally agree that the table design is not the best for inventory control- in my example database here, I tried to create an example of what I was trying to achieve, so the setup is a little different to my actual database. Part of that reason for doing this was I thought that once I find out how to do it, I would have to take it and try and apply it to my own situation so I'm forced to learn a little. And learn I did! In applying what you showed me, I had a small problem with my actual database that I couldn't figure out for a few hours- hence this reply is late.


Finally sorted and all good now- really appreciate your specific help. Fun learning.

Have a great week.
Always glad to help if I can. Good luck with your project.
 

GPGeorge

Grover Park George
Local time
Today, 03:37
Joined
Nov 25, 2004
Messages
1,775
Hi Bob,

Thanks so much for showing me how to do this. I totally agree that the table design is not the best for inventory control- in my example database here, I tried to create an example of what I was trying to achieve, so the setup is a little different to my actual database. Part of that reason for doing this was I thought that once I find out how to do it, I would have to take it and try and apply it to my own situation so I'm forced to learn a little. And learn I did! In applying what you showed me, I had a small problem with my actual database that I couldn't figure out for a few hours- hence this reply is late.


Finally sorted and all good now- really appreciate your specific help. Fun learning.

Have a great week.
Here's my take on the inherent dangers of "simplifying" things for a post on an online forum.
 

Users who are viewing this thread

Top Bottom