If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update UnitsInStock
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. |
#2
|
|||
|
|||
Update UnitsInStock
On your form when you enter QuantityIssued, the AfterUpdate event must
calculate: UnitsInStock = UnitsInStock - QuantityIssued Steve "HubbyMax" wrote in message ... 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. |
#3
|
|||
|
|||
Update UnitsInStock
Thanks but this does not work. Should it work even tho UnitsInStock and
QuantityIssued are from different tables? "Steve" wrote: On your form when you enter QuantityIssued, the AfterUpdate event must calculate: UnitsInStock = UnitsInStock - QuantityIssued Steve "HubbyMax" wrote in message ... 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. . |
#4
|
|||
|
|||
Update UnitsInStock
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" wrote in message ... 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. |
#5
|
|||
|
|||
Update UnitsInStock
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" wrote: 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" wrote in message ... 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. . |
#6
|
|||
|
|||
Update UnitsInStock
HubbyMax,
You still haven't answered my question/s. What happens in the case of Recieving PartNos? Please give some table & field names and example values for each type of transaction. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. . |
#7
|
|||
|
|||
Update UnitsInStock
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" wrote: HubbyMax, You still haven't answered my question/s. What happens in the case of Recieving PartNos? Please give some table & field names and example values for each type of transaction. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. . . |
#8
|
|||
|
|||
Update UnitsInStock
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." "HubbyMax" wrote in message ... 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" wrote: HubbyMax, You still haven't answered my question/s. What happens in the case of Recieving PartNos? Please give some table & field names and example values for each type of transaction. 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. . . |
#9
|
|||
|
|||
Update UnitsInStock
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#10
|
|||
|
|||
Update UnitsInStock
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. |
Thread Tools | |
Display Modes | |
|
|