Calculating Data in a subform

[bkupper23]

Hello everyone,

I am having all sorts of trouble trying to calculate my data correctly in my subform.

The subform is a continuous form that allows me to select as many labor operations as needed and calculate the cost.



I want to be able to set my labor rate in the forms header and keep that value for all records.

Currently, it resets to null on each new operation that I add.

I tried to add an unbound text box to set the value and then set my LaborCost field to equal the value in that text box.

The problem with that is it kept that value on all new records on the main form.

The other issues I am having is updated all the values on the form.

I want it to update in real time when any of the values are changed in the form, but can't seem to get anywhere close on that task.

I have attached my database for anyone to add any input.

G3 Machining Database_11_19_16211.zip

Thanks in advance!

Brandon

[bkupper23]

So I am trying to add the labor rate to the main form in order to keep that value for the whole subform.

I have never had any success trying to calculate anything using two different forms.

What is my issue with this code.

=Sum([Estimated Labor Hours]*[Quote Table]![Labor Rate])

Basically, trying to grab the value from the main form and multiply it by a field in my subform.

Brandon

[aytee111]

In the AfterUpdate of the txtHours set the labor cost to hours * rate.

[bkupper23]

So that helped my updating my calculations.

My main issue is still lingering!

I want to set the LaborRate once and keep it for all records under that quote number.

If I get a job that is rush rate our hourly rate may be $120/hr compared to normal rate of 80/hr.

Brandon

[aytee111]

I missed looking at your table!

Your table design could do with some changes:
- don't store calculated fields
- it should contain the hours and the rate and that is enough
- the hours should be a number field, not currency - long integer if no decimals, else double
- what is Total Labor Cost? The name says it is some sort of currency, but it is a text field. Name it something different
- do not use the default for the length of your text fields, always design your table with "intellisense", know what is going to be stored and design it accordingly.

That said, in the BeforeInsert event of your subform, set your labor rate to the one in the header, and remove the control source of the one in the header. Header fields are not part of the detail of a form so your control source is being ignored.

[bkupper23]

Thanks a lot for taking the time to look at my issue.

I am going through your advice now and attempting to implement it.

Thanks a million!

Brandon

[bkupper23]

Boom! That did it!

I've been working on this for nearly 10 hours today.

I can't thank you enough!!!

Brandon

[bkupper23]

Now I'm stuck on trying to get the form to update properly.

I want to be able to type in my labor rate into the now unbound Labor Rate in the header and update all the records.

I've tried inputting code into every event that I can think may work with no positive results.

I assume I am not doing something properly.

Brandon

[aytee111]

In the AfterUpdate event of the unbound LaborRate in the header, run a query that updates all the records with that key. Then Me.Requery will re-show the form.