Calculated field behind 1 from quantity? (1 Viewer)

[T. McConnell]

Name is confusing sorry, basically I have a query that takes a cost price and multiplies it by the quantity. I then pass that total to an unbound field that then updates a bound field. Yes I know not to store calculated fields in a table, but it seems harder to not store the "sale" of the part. My main issue is when trying to get the fields to update with the correct amount.
What is happening is I have a subform that I select a product, then select the quantity, I have hidden fields which stores the value of the cost and the total of quantity times cost price. It then passes that to an unbound text field on the mainform, this part is working fine, I am trying to get that value to update the bound field on the mainform when the quantity changes on the subform. However when I change the quantity, the unbound field calculates fine, but the bound field seems to be behind one quantity. Almost like it is calculating the quantity minus 1, if I click in another field on the main form (set to update the fields) this works fine, just when trying to get the change in the subform to update the bound field on the mainform.
What I have tried is putting on the afterupdate, beforeupdate, onchange of the quantity field, the subform, the unbound field, using a requery of the fields that calculate the fields to the bound text field. I have even tried putting the code on the mainforms unbound field

Me.BoundField = Me.UnboundField

No matter what I seem to try the calculation is not correct, like it is off by a quantity of 1.
Any help again is greatly appreciated.

 

[arnelgp]

after passing the value to Unbound textbox on main form, do a Recalc on the main form:

Me.Parent.Recalc

 

[T. McConnell]

Would I do this on the afterupdate of the unbound field?

 

[arnelgp]

on the subform (quantity field, afterUpdate event).

 

[T. McConnell]

Other problem doing it that way, is I have other fields on the main form based off calculated fields and quantities. When I use the Me.Parent.Recalc method it clears out the subforms total to 0, and only keeps the other calculated fields cost. :banghead:

 

[arnelgp]

how do you calculate the textbox in main form from unbound textbox?

 

[T. McConnell]

For this particular issue, basically I have a field on the mainform that takes quantity times a set price.
1. Part 1 Quantity * Cost (This is sent to its own unbound text box called txtEstModule)

Then I have a total of 3 Subforms with their own calculations done, these have 2 different calculations in them. On this form I select a part from a combo box and then enter a quantity which is calculated below.
1. Subform 1 Part 1 * Cost
2. Subform 1 unbound field with a Sum of Part 1

=Sum(Part 1 Total)

This is then passed to a different unbound text box on the mainform called PartsTotal.
This is also repeated for the other 2 subforms. (txtOtherCostTotal and txtGenericCostTotals)

I then have a total textbox for all the fields on the form called AllTotal

The code for the AllTotal field is:

=Nz([txtEstModule]+[PartsTotal]+[LaborTotal]+[txtOtherCostTotal]+[txtGenericCostTotals],(0))

Then after this gets the total of all those, this is where I am having issues. I want to pass total to the bound text field on my main form which is called TotalCost.

The only way I can currently get the update total is on an ActualCharge field is have it requery when focus is entered in that field. Only problem with this is an Actual Charge may not be entered at time of submitting the order. Here is that code to requery the one field with all the totals.

Me.TotalCost = Me.AllTotal

But when I change the quantity of the subform the calculation is correct in the AllTotal Field but the TotalCost field (bound field) doesn't update unless I click the focus driven field.

I know this is a lot of weird code I am sure, but this one is an odd go around to get all this to work somehow.
My apologies if this is more confusing.

 

[arnelgp]

can you directly requery it on the subforms qty afterupdate event:

me.parent!actualCharge.Requery or Recalc

 

[T. McConnell]

Unfortunately that didn't do it
I don't see why when the total field that sums everything up changes, I can't get the bound text field to update correctly.
The first pic TotalCostSum is the amount that is stored in the unbound field and is the sum of all fieds on the form, the second pic is the bound field where I am wanting to update, no matter what I do, I cant get the amounts to recalculate properly and be the same.

 

[T. McConnell]

I was able to get it to work on the LostFocus event of the Quantity Combo box.
I used to code below to make it work, it currently suites the need I am having

Private Sub Quantity_LostFocus()
    Forms![frmAdminOrders]!EstJobCost = Forms![frmAdminOrders]!txtEstCost
    Forms![frmAdminOrders]!txtEstCost.Requery
End Sub

frmAdminOrders is my Main Form
EstJobCost is my bound text field that shows the value of the unbound field
txtEstCost is my unbound text field with the sum from all the fields

Thanks for the suggestions!