Calculated Field pop up

[chriscook]

Hi All,

I am quite new to Access and hope I have put this in the right section in the forum.

I have created a Stock Database for my company. It records all stock items, any stock adjustments (in or out) and the sales of items.

I have a query (FreeStockCalculation) which calculates the current amount of stock for each item.

The calculation is as follows:

InStock: IIf([Current Stock]>0,[Current Stock],0)-IIf([ShippedQuantity]>0,[ShippedQuantity],0)

"Current Stock" is the total of Stock Adjusted in, minus any Stock Adjusted out.

"Shipped Quantity" is the total of quantity shipped from a sale.

What I want to achieve is if, an adjustment is made which reduces the InStock figure to be below 0, for there to be a pop up. I presume there would be something which needs to be added to the VBA code in the adjustment form?

I also want the same to happen if there is a sale of an item.

Sorry if I haven't been 100% clear, if you need any other information. Let me know. Or if you need me to send over the database with some test data.

Thanks in advance.

 

[jdraw]

send over the database with some test data

 

[chriscook]

Hi,

Sorry i have just been told I cannot provide database due to security reasons.

Sorry.

I can provide screenshots of the forms/queries if that helps?

 

[jdraw]

You can, and readers may review them. It is often easier to focus a response when you can see the issue in context.

Bottom line:
It is probably a good development strategy to have some phoney/mocked up data for names and addresses etc to protect anything confidential/private. Things like
Porky Pig, Heeza Payne, General Purpose, Cody Akbear etc. People here don't want your secrets or customer lists. We're volunteering some services to help. I understand some organizations do not want their info discussed/available on the internet.
Test and develop with test data.

Good luck with your project.

 

[chriscook]

Hello again,

Please accept my apologies for the late response, other projects have taken priority. I am now back to trying to resolve this issue (hopefully with your help)

Please see below link to my database with some sample data in it.

https://www.dropbox.com/s/htwwul44bk50axb/Stock Database.rar?dl=0

To refresh what I hope to achieve, I want a message to appear if the InStock figure is below 0.

It could happen when an order is shipped (Orders Form) or if an a removal adjustment is made (StockAdjustment Form).

I look forward to hearing your suggestions.

Thanks again

 

[jdraw]

I haven't look at your file (I don't use RAR).

What you are describing is often called ReOrderPoint (or similar). That is, when current number of Item X in inventory is ZZ or below, then ReOrder. And the amount/quantity to ReOrder is often called ReOrderLevel (or similar).

When a transaction (filling a Customer Order) results in Inventory quantity for that Item<= that Item's ReOrderPoint, a separate transaction is spawned to create an Order to a supplier for ReOrderLevel quantity of that Item.

Good luck.