Calculating records in a mainform and subform

[tomanddani via AccessMonster.com]

Hi,
I have a form with one subform. The form is from tblBusiness and the subform
is from tblWorkorders. They have a one to many relationship. One business can
have several work orders.
The main form has a textbox from a tblBusiness field called Cycle. This field
contains a number that is associated with the business. The subform has a
textbox from a tblWorkorders field called VisitDate. This field contains a
date associated with the workorder.

I need to have a textbox on the form that adds the Cycle number from the main
form to the year of the VisitDate from the subform. This is what I tried:
" =DateAdd("yyyy",[Cycle],[frmWorkordersSub].[Form]![VisitDate])".

This works except that I need it to add the number to the latest or largest
date value of that businesses records. This formula adds it to the record
displayed. Can I change this so it works on the last date instead of each
record?

Maybe a Max function or something?

Thanks,
Tom

 

[John W. Vinson]

Hi,
I have a form with one subform. The form is from tblBusiness and the subform
is from tblWorkorders. They have a one to many relationship. One business can
have several work orders.
The main form has a textbox from a tblBusiness field called Cycle. This field
contains a number that is associated with the business. The subform has a
textbox from a tblWorkorders field called VisitDate. This field contains a
date associated with the workorder.

I need to have a textbox on the form that adds the Cycle number from the main
form to the year of the VisitDate from the subform. This is what I tried:
" =DateAdd("yyyy",[Cycle],[frmWorkordersSub].[Form]![VisitDate])".

This works except that I need it to add the number to the latest or largest
date value of that businesses records. This formula adds it to the record
displayed. Can I change this so it works on the last date instead of each
record?

Maybe a Max function or something?

DMax() should do it, but you'll need to base it not on the Subform but on the
subform's recordsource table. Referencing a subform control will reference the
currently open record on the subform - the first record in the recordset if
the user hasn't selected one, or maybe no record at all if the form is at the
New record.

Try

=DateAdd("yyyy", [Cycle], DMax("[VisitDate]", "[tblWorkorders]", "[BusinessID]
= " & txtBusinessID)

assuming you have a control on the mainform named txtBusinessID which contains
the master link field's value.

 

[tomanddani via AccessMonster.com]

I got this to work, what do you think, right or wrong approach?

=DateAdd("yyyy",[Cycle],(Max(DLookUp("[VisitDate]","[tblWorkorders]","
[BusinessID]=[frmWorkordersSub].[Form]![BusinessID]"))))

Tom

 

[John W. Vinson]

I got this to work, what do you think, right or wrong approach?

=DateAdd("yyyy",[Cycle],(Max(DLookUp("[VisitDate]","[tblWorkorders]","
[BusinessID]=[frmWorkordersSub].[Form]![BusinessID]"))))

Tom

I'd use DMax rather than Max; and if the BusinessID exists on the mainform (I
presume it's the master link field for the subform?) I'd reference the
mainform rather than the subform. Perhaps just

"[BusinessID] = " & [BusinessID]

would be sufficient as the third argument to DLookup.

 

[tomanddani via AccessMonster.com]

Thank you !

You people who post anwsers to this site are an invaluable resource to people
like me.

Tom