Update UnitsInStock

[HubbyMax]

I have a problem with my UnitsInStock value.

1- Req4 form contains an entry field for QuantityIssued
2- Products2 table that contains the UnitsInStock value.
3- Req4 table contains Request detail data

The forms record source is a query that joins the 2 tables using a product
number.

As it is now the user must manualy change the UnitsInStock on the form by
substracting the QuantityIssued from the current UnitsInStock. I want the
UnitsInStock to update itself after the QuantityIssued is entered. I have
tried AfterUpdate code and expressions with no luck.

 

[Steve]

On your form when you enter QuantityIssued, the AfterUpdate event must
calculate:
UnitsInStock = UnitsInStock - QuantityIssued

Steve
(e-mail address removed)

 

[HubbyMax]

Thanks but this does not work. Should it work even tho UnitsInStock and
QuantityIssued are from different tables?

 

[Al Campagna]

Hubby,
If I understand correctly, it sounds as though every time you Disburse
a quantity of an item, you want to store the UnitsInStock value in a table?
Products2?
What happens in the case or Recieving PartNos?
Please give some table & field names and example values for each type
of transaction.

As a general rule, and particularly in the case of an inventory system.
it is not advisable, or necessary, to store the current UnitsInStock... but
to calculate that value, when needed, on any form, query, or report.
It's not an iron clad rule, but a matter of good practice.

A inventory system can be done very easily with just one table.
PartNo Rcvd Disb TransDate
1234Q 10 0 1/1/10
5162W 22 0 1/1/10
1234Q 0 5 1/7/10
5162W 0 6 1/25/10
1234Q 6 0 1/27/10

The UnitsInStock at any time for each PartNo would be
Sum of Rcvd for a PartNo - Sum of Disb for a PartNo
1234Q = (16 - 5) = 11 UnitsOnHand
and would be calculated on the fly on any form, query, or report.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[HubbyMax]

I know I am not doing this in the simplest or best way. I will eventually
redo my program to reflect all have learned from this forum but need to find
a way to make my current program work properly.

The UnitsInStock value is stored in the Products2 table. This is the main
table containg most product information.

The Req4 table contains requision information that includes the amount
distributed as QuantityIssued.

The Req4 form is used to enter requision information including
QuantityIssued. This form shows the UnitsInStock so the user can see that the
requested amount is in stock.

In it's simplest form this is what I want to have happen.

User enters QuantityIssued value. After this is entered the QuantiyInStock
is adjusted.

The QuantityInStock should be Products2.QuantityInStock =
Product2.QuantityInStock - Req4.QuantityIssued

 

[Al Campagna]

HubbyMax,
You still haven't answered my question/s.If you had 100 UnitsInStock (for P/N 1234) already, and received 50
more for stocking, how would you update the UnitsInStock in Products2?
Would you go into Products2 table and update the quantity to 150?

Once I know how Receiving and Disbursing are handled now, we can decide
how to proceed.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[HubbyMax]

I'm very sorry I forgot to include some info. When items are recieved it is
entered on the Products2 form based on the Products2 table. The addition is
added by a formula, UnitsInStock + AddUnits = TotalUnits. UnitsInStock is
then replaced by the TotalUnits amount by an AfterUpdate event in the
AddUnits field using Me! statements. These controls are in the Products2
table so this works fine.

StockNu UnitsInStock AddUnits TotalUnits
1001 10 10 20

Items going out are handled by the Req4 form. This form is based on a query
that joins the Products2 table with the Req4 table using the StockNu in both.

Products2.StockNu Products2.UnitsInStock Req4.QuantityIssued
Req4.StockNu

1001 20
10

Products2.UnitsInStock for 1001 should change to 10.

 

[Al Campagna]

HubbyMax,
That's what I thought...
As I mentioned, this is not the way to do it, but...

Use a Dlookup to display the current value of QuantityInStock, for that
P/N on form Req4.
You'll then need to do an Update query on the AfterUpdate event of
QuantityIssued, on Req4... against tblProduct2.
That Update query will add -QuantityIssued to the current value of
QuanitityInStock
for that particular PartNo.
After the Update query runs, you'll need to Refresh/Requery the
Req4 form, so that the QuantityInStock DLookup will recalculate to the new
value.

If you have any further problems keeping track of UnitsInStock, then I
would
strongly suggest you abandon this design for a more stable Credit/Debit
system of
Inventory control.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[PieterLinden via AccessMonster.com]

HubbyMax,

You might want to look at Allen Browne's Inventory/Quantity on Hand DB on his
website... it would at least give you an idea of how you could do this
(without reinventing the wheel).

http://www.allenbrowne.com/AppInventory.html

 

[Fred]

I kind of divide the possibilities of overall approach into three:

1. A table of all transactions (including the initial entry as a
"transaction") and then current inventory is calculated (by a report or
form) as a sum of all transactions. I think Allen's is of this type.

2. A table with current inventory quantities. edited by hand for all
changes.

3. A table with current inventory quantities, and tables which hold all
transactions (sales, purchase, adjustments, use for manufacture, creation by
manufacture etc. ). And then procedures have each of one these modify
inventory values once and only once. All of the "run your whole company"
softwares do this.

I think that you are trying to do #3, which is exceedingly complicated.
I've not seen any templates or example DB's which do this.