I REALLY need help with a calculation. (1 Viewer)

[Benjamin Bolduc]

Im stumped! I need to use a complicated formula on one of my forms to determine the balance of products Im keeping track of. I only have experience in Excel Formulas. Here's what I need...
-First I have all the product information on the top of the form, and the inventory entry information in a subform at the bottom. I have a 'Quantity In'and'Quantity Out' field in that subform with the 'Balance' field on the top. I make changes to most of the inventory on a daily basis. (They come and go very quickly)

-I need to be able to type in the 'Quantity In' and Quantity out' in the same record.
-It needs to take the balance from the previous record and add the values of the 'Q-In' and subtract the value of the 'Q-out'
-Then it needs to display that value in the 'Balance' text box control I have in the form.
-Basically my goal is to bring up the product's form, quickly type in how many of it I received and got rid of and have that balance shown on the form. If anyone can help me Id greatly appreciate it
-

 

[chrismcbride]

Firstly you want to seriously consider storing the Balance amount. It is usual to never store calculated amounts. There are some exceptions used in larger inventory systems, however you would want to do some research on implementation of this type of exception.

On the subject of calculating the balance of an item, this is easily and reasonable quickly done. In your form you have a Q-IN and Q-out control which are bound to matching fields in your table. If you make a form footer in your sub form you can add two calculated controls to sum the two Q controls. Set the ControlSource of TotalQ-IN to =Sum(Q-IN) and ControlSource of TotalQ-Out to =Sum(Q-Out). Set these controls to invisible. Then make another control that is set to visible in the footer and set it's ControlSource to =Sum(Q-In - Q-Out).
HTH
Chris

 

[Benjamin Bolduc]

Thanks, That makes a lot of sense. I have to get myself out of the Excel Formula mindframe. I tried implementing that formula, which seems pretty simple, But I keep getting sytax and parimeter errors? Should I use expression builder or just type it in, and if so exactly what do I write?
Thanks Alot