Continuous Form Duplicating values in unbound textbox

[spartansFC]

Hi

I'm trying to show the Age of a child in a continuous subform but the subform is only duplicating the first value and repeating that no matter
how many children are in the list or how many different dates of birth there are.

the way i've worked out the Age is, on the main form i have:

Months_txtbox

=DateDiff("m",frmChildFormSubNames.Form!dteParentChildDOB,[today])+Int(Format([today],"dd")<Format(frmChildFormSubNames.Form!dteParentChildDOB,"dd"))-([Year_txtbox]*12)

Years_txtbox

=DateDiff("yyyy",frmChildFormSubNames.Form!dteParentChildDOB,Date())
-IIf(Format(frmChildFormSubNames.Form!dteParentChildDOB,"mmdd")>Format(Date(),"mmdd"),1,0

date

=today()

on the subform i have:

Childage_txtbox

=Forms!frmChildForm!Year_txtbox & "." & Forms!frmChildForm!Months_txtbox

I've attached a screen shot to show what happens, as you can see the age "3.1" is shown for
both children, but i've highlighted the 2nd child "jordan" who's age is shown correctly on the main form.

I just need to tell the continuos form to look at the childID somehow
or tell the database to loop through the different DOB to show the correct age for each child.

Any ideas?

Mikie

 

[TheAceMan1]

How are ya spartansFC . . .

You have circular calculations where the mainform is dependent on the subform and vice-versa. You also have the [blue]Months_txtbox[/blue] dependent on the [blue]Years_txtbox[/blue].

You need to set a function in the subform. So ... in the code module of the subForm, copy/paste the following function:

[blue]Public Function curAge()
   Dim Yrs As Integer, Mths As Integer, Tdy As Date
   
   Tdy = Date
   
   Yrs = DateDiff("yyyy", Me!dteParentChildDOB, Date) _
         - IIf(Format(Me!dteParentChildDOB, "mmdd") > Format(Date, "mmdd"), 1, 0)

   Mths = DateDiff("m", Me!dteParentChildDOB, Tdy) _
          + Int(Format(Tdy, "dd") _
          < Format(Me!dteParentChildDOB, "dd")) _
          - (Yrs * 12)
   
   curAge = Yrs & "." & Mths

End Function[/blue]

[blue]Perform your testing[/blue] and let me know ...

[blue]Your Thoughts? . . .[/blue]

BTW: it appears the mainform is set by the selection in the subform! Speak on this!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[spartansFC]

Hi TheAceman1

thanks for the quick response, and for the module coding, i found a solution. I just put all the:

date_txtbox, Months_txtbox and Year_txtbox into the subform and did the calculation in there, not sure why i did it the other way round but it works which is the main thing.

On the module code, how would i call that function in a txtbox as i may use your better way as i have more than one form that i want to add children's ages to and i don't want to keep re-doing the same textboxes if i can just call up a function.

thanks again for your help

Mikie

 

[TheAceMan1]

spartansFC . . .

The same as your [blue]today()[/blue] function for the [blue]CurrentDate[/blue]:

[blue]   =curAge()[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[spartansFC]

My thoughts are that i think i need to consider looking at
some VBA coding sometime.

I haven't used your function method this time, but i will keep it for any future databases as it's a much more simpler and effective way of doing what i need.

thanks again for your help

Mikie