Adding Numbers to a Table Through a Form

[pokeytrev]

Hello,

I haven't been using access for long, and I have run into a bit of a snag.

Question Detailed:
I have three tables and three forms I use to track inventory movement: 1) Table: Inventory items; Form: Modify Inventory Items 2) Table: Parts Received; Form: Receive Part(s) 3) Tablearts Sent; Form: Send Part.

I have three of these combinations because I want to keep track of what was sent, what was received, and what the current numbers are for parts in stock. So to receive an item, I go to the Receive Part(s) form and fill in the required information including the amount received. I then go into the inventory items table and update the amount (an extra step). Is there a way I can receive items in the Receive form and have that amount added to inventory items? The same goes with depleting items?

So basically what I am asking is this: Is it possible to have one form update the amount received ( Qty 2 Widget A received) while adding to the main inventory table (2 of Widget A received, add +2 to inventory changing Qty from 3 to 5)?

Background for Question:
I keep track of an inventory of repair parts for the department I work for, and I use both access and our company's ERP system. I both deplete and add inventory items as our engineers make their rounds, and I use both applications to track this. The reason I use both applications is due to the fact that I am tasked with running monthly reports showing what was taken and by whom. The ERP system that we use would require our engineers to scan an inventory item with a PDA in order to deplete said item. Because the PDA software is really difficult to learn and requires multiple fields, we opted to use a sign-out sheet which I collect daily and make inventory adjustments from using the PDA (making my name appear as the one taking parts out). Because of this, I use Access to record what is taken as well as the ERP system, and although it is extra work, it does wonders for the reports I run.

 

[MarkK]

Typically a balance is calculated, like if you think of your bank balance, the fact of that number is that it is . . .

balance = deposits - withdrawals

. . . and of course all bank activity is dated, so you can (and should be able to) calculate the balance on any particular day (even into the future).

Similarly with an inventory system, you have sales orders and purchase orders, and so you can calculate the "stuff on hand" using . . .

StuffOnHand = StuffPurchased - StuffSold

So if possible, do a little paradigm shift, and think about your stock level as a dated calculation you do on your tables 2) and 3). Even think about merging your tables 2) and 3), and simply altering the sign on the quantity field, positive for stuff your receive, negative for stuff you send, and then just sum those quantities, up to a date, for a particular productID, and *poof* you have a very simple, very flexible, stock level manager.

Hope this helps,

 

[pokeytrev]

That makes a lot of sense, actually. I over thought this by way too much. I will go ahead and try that. I will make a test database tomorrow before thinking of applying it to the main database.

Cheers