Using sum function between forms

[scottappleford]

Hi

I have a main form and two sub forms for a stock control db.

The main form shows the product with controls for units on order and units on hand.

The first sub form handles stock transaction from a seperate purchase order form. This sub form has 'units ordered' and 'unit received' fields, which the relate back to the main form to calculate the units on order/on hand etc. So when a transaction is made on the purchase order the units on order will show say 100 until 100 is entered into the received field to update the units on hand etc.

Now the problem is that i have another sub form which basically is for stock going out once the order arrives in the store. I have a quantity out field but can not get the units on hand in the main form to sum the columns and calculate what is left when goods leave.

In the fisrst sub form (transactions) I have a units sold but it is hidden as it is needed. I could use this to sum the quantity out in the seconed stock control sub form but can not get it to do it.

The db has no data in it at present so i could post it?? but i really need someones help with this as the db is needed very shortly.

many thanks and i hope i explained myself properly.

 

[aleb]

Try to post your db.

 

[scottappleford]

please find db attached

hi

would be really grateful if you can help me with this.

open db - enter/view stock control and the bottom sub form needs to calculate with the units on hand in the main form.

many thanks

 

[aleb]

In on hand field paste this expression

=[Products Subform].[Form]![UnitsOnHand]-nz(DSum("[QuauntityOut]","IntStockControl","Forms!Products.[ProductID] = [IntStockControl]![ProductID] and isnull([IntStockControl]![DateIn]) = true "))

 

[scottappleford]

hi

Thanks so much for looking at the for me.

The formula works but it only updates after you close the form then open it again? is there something i can do to make this update straight away?

thanks again - you have been a great help.

 

[scottappleford]

hi

Thanks so much for looking at that for me.

The formula works but it only updates after you close the form then open it again? is there something i can do to make this update straight away?

thanks again - you have been a great help.

 

[scottappleford]

hi

Thanks so much for looking at that for me.

The formula works but it only updates after you close the form then open it again? is there something i can do to make this update straight away?

thanks again - you have been a great help.

 

[aleb]

The field is updated every time when the data on the main form gets loaded.
So ... you can put a button on the main form with onclick property me.requery , which will recalculate the field on demand otherwise you will need to track all events which effect the calculation. I mean if you add a line in sold subform you need to recalculate the formula, if you update the stock ... and so and so forth. Easier to put a button on the main form.

BTW the formula recalcs if you jump from one record to another.

 

[scottappleford]

Thanks - last question

Thanks again for the reply - that works fine - by jumping between records.

One last question.

In the stock control how do i stop a record being entered if there is not a product and transaction record?

At the moment if you click to enter a new product record it is possible to put data into the stock control form (the bottom form)

Thanks again - will leave you in peace now.

scott

 

[aleb]

Go to the tables tab open instockcontrol table in design view and set property of ProductID field : requered - yes.
Would be good to set the same property for all the fields you think are required to have values in order to avoid the junk records.
As for preventing the records being entered if there is no transaction lines.
In "On current" event of your "Products" form put

Private Sub Form_Current()
If IsNull(Me![ProductID]) Then
DoCmd.GoToControl "ProductName"
End If
If DCount("[productid]", "Inventory Transactions", "products.[productid] = [productid]") > 0 Then
Me.IntStockControl subform.Locked = False
Else
Me.IntStockControl subform.Locked = True
End If
End Sub

 

[scottappleford]

Thank you

Thanks you have been a great help!!

Scott

 

[scottappleford]

Adding another field to calculation

Hi

Further to the posts above - i would like to add a quantity back in to the on hand calculation field.

The formula above works but i would like to add this extra field to the calc.

Thanka for your help!

scott