Sub-form get value from main form

[appelq]

Looking for guidance on how best to do the following:
I have frmServiceHeader and a sub form sfrmServiceDetail.

frmSeriveHeader has various information including the TaxRate and LaborRate for the particular customer.

When I add a Row to the sfrmServiceDetail, I want to enter thing like Labor Hrs and Parts total and then
have the system pull in the Labor Rate and Tax Rate from the frmServiceHeader form, save that info to the detail row,
and finally do some math.

What is the best way to pull in the two field from the header form when adding a row to the detail form?

Thanks in Advance

 

[MajP]

Several ways.
Since the subform is related to the mainform, you will have some foreign key in the rowsource of the subform. You can then use a dlookup something like
=dlookup("taxRate","parentTable","parentID = " & [childID]

or it can be done in code to reference the parent form
me.parent.TaxRate

or you could do it purely in sql by basing the subform off of a query that includes the tax rate and labor rate. Make sure it is a right or left join to include all child records. If not it will not allow additions.

 

[dhookom]

I'm not sure it's a good idea to save the same information in two tables. I assume you understand your tables better than I do.

Setting the Link Master and Link Child properties will default the values in the subform to the values in the main form.

You could also set the default value of the control on the subform to pull the value from the main form. MajP's DLookup() is missing a closing ")".

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I missed the "save that info" part. I was assuming you would pull that info and do some calculations with it. Also on that note, you may not want to even save the calculated value, unless there is some historical and changing rates. You may just want to display the calculated values. Normally if you can always calculate the value, you never store it. You just recalculate in a query at a later time. Only under certain exceptions is it wise to save calculated values.

 

[appelq]

Thanks.
So DLookup is the right way to go.
And actually I really just need to pull in the Tax Rate to calculate the Tax Amount, and Labor Rate to calculate total Labor Amount, based on the other fields. I don't want to keep that data on the table, just pull into the Sub-form for the calculation.

Thanks for your help!

 

[dhookom]

Are you planning on storing the Tax Amount and Labor Amount?

Duane
Hook'D on Access
MS Access MVP

 

[appelq]

Yes, I was planning on storing the calculated values for Tax Amount and Labor Amount on the Estimate Detail Line record.

 

[dhookom]

I would add code to after update of some controls in the subform to use values from both the subform and main form to place a value in the Tax Amount and Labor Amount controls.

If you can't figure this out, come back with the significant control names and calculations.

Duane
Hook'D on Access
MS Access MVP