Subtracting in a form

[storebay]

I have a database in which I want to track stock on hand. I have one table where I record purchases (Purchases Table), and this is linked to the Purchases Data Entry Form. Then I have another table where I record sales (Sales Table), and this is linked to the Sales Data Entry Form. Now in the sales form I created a field (Grandtotalbags), that uses the sum function to find the total of the quantity field - e.g. =Sum([Quantity])

In the Purchases form I have another field (TotalPurchases) that uses the sum function to find the total of the quantity purchased field - e.g. =Sum([QuantityPurchased])

Then I created a field (in the purchases form) that would have a calculation that would minus these two values and thus display the stock balance. I created the field (Stockbalance) and selected properties, and went to the expression builder in the control source. Then I selected the Purchases Data Entry Form and double clicked on the Quantity purchased field to add that to the expression. Then I selected the minus sign, after which I opened the forms folder, selected all forms, selected the sales data entry form, and from that double clicked on the Grandtotalbags field so that it was also added to the expression. However, when I open the form the stockbalance field has #Name? in it. It does not display the stockbalance. What am I doing wrong? Can anyone help me with this? Thanks in advance. Eagerly awaiting your replies.

 

[RickSpr]

I think you have several conceptual problems here. First, a form can only "see" one row at a time from its recordsource. Thus, the Sum() function can only access the Quantity field in the current row--it doesn't automatically scan the whole database. For that you need the DSum() function.

Second, the Expression Builder lets you build lots of expressions that will only work in certain circumstances. In this case, I believe you're getting #Name? because your expression refers to a control on another form, and that form isn't open (in Form View) at the time the control's value is needed. It might also be that the expression is just not well-formed. I'd have to see it to know more.

But I think you need to back up a level for a minute. What is it you're trying to do? Do you just want to show the current stock level on the Purchase form? If so, is adding up all the purchases and subtracting all the sales going to give you the right answer? What about returns, defective items, and miscellaneous losses (such as pilferage or accidental destruction)? It's more typical to keep a stock-on-hand counter in your Stock table and update it with purchases, sales, and all the other things that can change the stock level. Then you could just fetch that one stock level to show on the form (though you'll need some VBA code to do that--plain Forms can show rows, or summary data, but usually not both).

One more thing: If you use this Total Purchases - Total Sales formula, then after a while you'll be doing an awful lot of arithmetic to process thousands of purchases and sales, and you'll be doing it again and again. That will slow things down, eventually quite a lot.

 

[storebay]

RickSpr,

Thanks for your extremely informative advice. Well, the sum is adding up all the quantities and not just the current record, so that is okay. Secondly, yes we have to take into account damaged items and so on, and I did. The damages and returns are being tracked in the sales form. The GrandTotalBags field is actual a calculation that determines all sold minus damages and returns, but since that is working I didn't want to include it, thereby I could keep the question simple and focus on the problem. Now you said that the sales form has to be open in order for the stockbalance field to calculate in the purchases form. This is great! I haven't had it open. I thought that access would just go in and do it. Thanks for that advice! I will go and check it and see how it works and let you know okay?

One thing though, can you give me a working examply of DSum? I want to know how to use it a lot. Thanks in advance again.