View Text box calculated in an other form.

[L'apprentis]

Hello,
I have been struggling to see on a new form data calculated on a different form.
What I mean is that I have calculated a stock balance using a subform where the balance=[QuantityReceived]-NZ([Stock record table Subform].[Form]![SumQtyIssued])

Now, I woud need to see the balance value on an another form with the same Data source query.
I have been trying to assign the control source of the balance as follow but doesn't work: balance=Forms![Stock record sheet]!Balance where [stock record sheet] is the form where the balance is originallly created.

I guess that you can't reuse data calculated in form and should probably store these data in a table or a query. Is there any other sensible way to do this?

 

[ScottGem]

Pat is very correct, but I'm concerned how you are calculating stock balance. A properly designed inventory application is centered on a transaction table that records all movement of stock in and out. Each movement of stock becomes a transaction record. Stock balance is then calculated by adding all the incoiming transaction and subtracting the outgoing transactions. You could then use that calculation anywhere you need to display the stock balance.

 

[L'apprentis]

More stock confusion

After the post by Scottgem I' ve been spending a large amount of time looking at Stock control, Inventory and transaction table and now feel a bit confused (an other one...).

If I used a transaction table, I need to use one record for one stock movement.

I receive Parts that are allocated directly when arriving on the workshop; Therefore my GOOD RECEIVED TABLE presents each items with their allocation points and specific information (Supplier, date received, Quantity received, Po, certification...) Which gives me a good receivedID for each single item received.

What I also have that I though was the equivalent of the transaction table (but not so sure because of the last reply I received) is a stocktable where 1 record was showing a Quantity issued (for a job) for any item received. This is the reason i used STOCKTABLE subform inside GOODRECEIVED form and of course (balance=Quantity recceived-sumQuantityissued)

Do I have to enter in Stocktable the quantity received as well? NO, that doesn't sound right: Mr NORMALIZATION (2NF) would probably kill me.

Guys i'm really sorry, i am aware that this is the zillion time you get this kind of threads with that subject but if someone is patient enough to give his thought on the matter it would be really apreciated.

 

[ScottGem]

No apologies are necessary.

I'm not sure if I'm following your work flow here. Generally an Inventory app will encompass one or both of the functions of purchasing and sales/usage. In a reseller, items are purchased into stock and sold out of stock. In a manufacturer, parts might be purchased into stock and then allocated to the assembly of a product. The key is to design a SINGLE transactions table that records all movement of stock. The workflow might look like this:

1) A part is ordered using a PO or order. The PO/Order table is linked to the Transactions table, where the quantity ordered is entered.
2) A quantity of parts is assigned to manufacturing to be used in the assembly of an item. This assignment would be part of a Bill of Materials. The BOM record would then be linked to the SAME transactions table and the quantity recorded as outgoing.

By using this SINGLE transactions table to record all movement it becomes easy to calculate stock on hand by adding the incoming and subtracting the outgoing.

If you need to track individual parts, that would be done in a separate table where each part would be identified and linked to either an order or a BOM.

 

[The_Doc_Man]

You really need to start searching the forum for threads on inventory issues. We have had many inventory discussions on practical and theoretical issues. Further, the SEARCH function in this vBulletin program works quite well. Yeah, I know there is no "immediacy" with a search of static threads, but the information you want is there.

Do you understand normalization? One of the reasons you don't want to store a "current quantity" in a table is because the odds are that the table does not have the keys required to meet normalization requirements. You see, "current quantity" implies a time as well as a product. You probably won't be creating new "quantity on hand" records for that product with a different timestamp. But if you just update the quantity and date in the product record, you have in effect trashed your history, meaning that down the road you will never be able to support trend analysis or projected usage analysis.

Most serious inventory systems need to be able to see history. You won't have that if you take the approach you are taking. It might work in a simplified manner now, but down the road you will remember this thread, smack yourself in the forehead, and say ... "Wow, I could have had a...." (Whoops, wrong image!) "Wow, I wish I had listened closer to what they were telling me."

Inventory is a dynamic concept. It changes frequently. The bigger your company & the larger your inventory, the faster it will change. Storing a dynamic quantity in a static record is not the answer.

Something that you need to understand, and it is simple...

Don't worry that you don't store a current stock count in a table. You don't need a current stock count field in a table. You can put a SUMMATION query to the task. If you have the current stock count field in the SUMMATION query, that is enough, because for reports and for many forms, anything you can do with a table, you can do with a query. (And frequently, SHOULD do with a query.)

This works because your disk is still literally thousands of times slower than your CPU. So if you have to compute a summation, don't worry. You've got the CPU cycles to spare.

Doubt me? Take a 7200 rev./minute disk... = 120 rev./second. = 2 rev./power cycle if you are on 60-cycle power. = 1 rev every 8.333 milliseconds. In a 1.0 gigahertz machine, that is 8.333 MILLION memory cycles (typically) or at least 2 million instruction cycles on a really ugly instruction sequence. Trust me, the SUMMATION query will still be waiting for the disk. You won't waste any disk spins.

Therefore, make your computer work for you. It has the time. That's why you bought it. Have the computer .... COMPUTE something for you when you need it. Don't be NICE to your machine. Make it work its little BUNS off!