weilerdo67
New member
- Local time
- Today, 02:09
- Joined
- Aug 2, 2019
- Messages
- 5
Hello wonderful people. I am scratching my head trying to figure this one out. I have a form "Inventory" that is based on a table "Inventory" that stores Part Details (Part number, description, supplier, etc). I have a second table that stores transactions for a given part (IE addition, removal, etc) they are linked via Part Number. On my form I have a field that I want to show me the current inventory quantity on hand. Since my form is not based on a query I am trying have the field only display the data and not write it to a table. It seems like this should be simple. In my tbl_transaction I have a field called Trans_Type and a field Quantity. This is the expression I have tried in different forms but only get a #NAME?
=DSum([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))
and this:
IIf([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))
Not sure if this should be done in VB or what is the best coarse, but in my "tbl_transaction" table for example I have a Part Number and have 3 rows in there 1 row is Addition Qty 25 and 2 rows of Removal for 1 and 5 so I would expect the result to be Quantity on hand in my form to be 19.
I hope this makes since and I appreciate any help in advance.
=DSum([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))
and this:
IIf([tbl_transaction].[Trans_Type]="Addition",[tbl_transaction]![Quantity],-([tbl_transaction]![Quantity]))
Not sure if this should be done in VB or what is the best coarse, but in my "tbl_transaction" table for example I have a Part Number and have 3 rows in there 1 row is Addition Qty 25 and 2 rows of Removal for 1 and 5 so I would expect the result to be Quantity on hand in my form to be 19.
I hope this makes since and I appreciate any help in advance.