DLookup and updating query/calculated fields

[huv123]

THis is probably a simple question but I cant seem to get everything coordinated.

I have a form that collects information about participant's in a study including height, weight etc.

I need to automatically calculate BMI once a person has placed the information in to the form so I created a query and used =DLookUp("[BMICalculation]","qryBMICalculation","[ParticipantID]= Form![ParticipantID]") in an unbound field called BMI and then placed Me.Requery in the after update event of the height and weight fields.

The query works beautifully if I try to update existing data but I get a error re:
Me.Requery when I am adding new participant details because all the fields in the form are set as required and it sets off some error.

How do I make sure that all changes are updated but can still add in new records?

I tried If is not null 9ParticipantHeight) then Me.Requery in the after update event of Participant weight and vice versa but this didnt work either - it works well when updating records but not when adding new data - it sets off the error.

Basically I :

1. Just want to calculate a person's BMI straight away (without having to close and reopen) using fields in the same form - don't care is by dlookup or not
2. Have this updated if I ever change anything in these fields in the form.
3. I cant assume that the person entering the data will do weight first or vice versa and that once they enter the data they will not try to change it.

 

[RivetHed]

Something like IIF(ISERROR(WeightControl/HeightControl^2),"",WeightControl/HeightControl^2) Would do it wouldn't it? I imagine the form should recalculate when ever the user tabs off a control but if not force it with Recalc rather than Requery

 

[dhookom]

I wouldn't store the value. Set the control source of the text box to:
=IIf(IsNull([txtHeight]+[txtWeight],Null, (txtWeight*703)/(txtHeight^2))

Your IIf() should always return the same data type (or Null) for both the true and false arguments.

Duane MS Access MVP

 

[huv123]

Thanks Ill give it a try.