Calculate value of unbound field for each record on continuous subfrm, vs all records on the subfrm

[SusanCoder]

I appreciate your assistance. When the value for the ChildAge field (unbound) is calculated from code in the After_Update event of the ChildDOB field (bound), the ChildAge value is updated on all the records on the continuous subform rather than being calculated for each record. I suspect I need a loop in the event VBA that does the calculation for each record. Can you confirm? I could use some help with the VBA syntax.

The back story...

Parent form frmYouthPersonalInformation and child continuous form subfrmPregnancy are linked by UniqueID.

Parent table tblYouthPersonalInformation contains name, DOB, etc for each teen by PK UniqueID.
Parent table (one) to child table (many).
Child table tblPregancy contains pregnancy and children data for each teen. The UniqueID is a foreign key, and PregnancyID is the PK.

frmYouthPersonalInformation contains PK UniqueID LastName (for teen) DOB, etc.
subfrmPregnancy contains PregnancyID UniqueID ChildDOB ChildAge (unbound) ChildLastName, etc.



Imagine Sara is the teen on the parent frmYouthPersonalInformation, and she has 3 children records on the subfrmPregnancy. The forms are linked by Sara's UniqueID, and each of the three records on the subfrmPregnancy have a unique PregnancyID plus Sara's UniqueID. When I update the ChildDOB value for one of the children, the ChildAge value for all three children updates to the same age.

Thanks! It's great to have help.

[Gina Maylone]

Hi!
You should bind the field "childage".

HTH

[aytee111]

You can't mix bound and unbound fields on a subform. Any value in an unbound control is applied to all records. You could change the subform to a listbox, you could also create a temporary table for the subform.

[aytee111]

You could make it a calculated field, instead of doing the calc in VBA, set it as the control source for the field : =DateOfBirth/12 or whatever.

[Gina Maylone]

You can't mix bound and unbound fields on a subform. Any value in an unbound control is applied to all records. You could change the subform to a listbox, you could also create a temporary table for the subform.

Why can't you have bound and unbound fields on a subform?

[aytee111]

As stated, any time you put a value into an unbound field it will show in that text box for every row as the value is being applied to the name of the textbox, which is the same on every row. However, if you fill the control source with something, in this case a calculation, it will behave just like a bound field and apply to the one row only.

[Gina Maylone]

Right, if you bind the field to the underlying table field "childage" it will hold the data for that child only.

[aytee111]

Age is a calculated field and as such should never be stored on a table.

[Gina Maylone]

Age is a calculated field and as such should never be stored on a table.

Good point, I didn't think that part through, was just looking at avoiding updating all of the related records. Duh!

[SusanCoder]

Thanks for the discussion. As always, I learned something from this forum - an event procedure used to calculate an unbound field on a continuous form will populate all of the records, not just the individual record.

I still needed to solve my coding issue and calculate the age of the child for each record on my continuous form. I placed the code below in the control source of the ChildAge field. The code checks first to see if the ChildDOB is null and if so, sets the default value to 0. Otherwise, the formula showed in the ChildAge field on the blank record.

=IIf(IsNull([ChildDOB]),0,Year(Now())-Year([ChildDOB])+(DateSerial(Year(Now()),Month([ChildDOB]),Day([ChildDOB]))>Now()))

[Gina Maylone]

Thanks for the discussion. As always, I learned something from this forum - an event procedure used to calculate an unbound field on a continuous form will populate all of the records, not just the individual record.

I still needed to solve my coding issue and calculate the age of the child for each record on my continuous form. I placed the code below in the control source of the ChildAge field. The code checks first to see if the ChildDOB is null and if so, sets the default value to 0. Otherwise, the formula showed in the ChildAge field on the blank record.

=IIf(IsNull([ChildDOB]),0,Year(Now())-Year([ChildDOB])+(DateSerial(Year(Now()),Month([ChildDOB]),Day([ChildDOB]))>Now()))

GREAT! Stick with us and soon you will be a Sometimes Competent Performer At times! :-)