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 the fields 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 ParticipantHeight <> Null then Me.Requery in the after update event of Participant weight and vice versa but this didnt work.

Hmm What to do?

try

If NOT IsNull(ParticipantHeight) Then
me.requery
end if


or you could remove the dlookup from the control and replace the me.requery with ControlName = DLookUp("[BMICalculation]","qryBMICalculation","[ParticipantID]= " & Me.ParticipantID & ")

Thanks Whoot

I am still having issues using the first method.


For new records:

If I do If is not null etv (Participant Height), Me.Requery method in weight field and vice versa, I get an error when trying to enter data in second field (although I do not get an error when adding data to the first field) and the BMI field does not calculate BMI -- Instead the Me.Requery causes the records to jump ot the first record.

fOR EXISTING RECORDS:
The BMI field updates nicely

Basically I :

1. Just want to calculate a person's BMI using fields in the same form
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.

If NOT IsNull(ParticipantHeight) AND NOT IsNull(ParticipantWeight) Then
Me.Requery
End If

put your BMI calculation in a function. then create an unbound text box on your form and set the control source to the function.

This is the technique I use myself. see attachment

Sorry Whoot,

It doesnt seem to be working. The fields that are required and havent yet been filled trip it up since requery causes it to "refresh" but I havent yet filled out the other data.

Dennis - If u have time can you please walk me through how to do this?

I wouldnt have thought it would be this difficult. Isnt this the same thing as any calculated field that relies on the manipulation of two other fields?

Thanks

Basically I have a function created in a module


Public Function BMI(Weight As Variant, Height As Variant) As Single
If Weight > 0 And Height > 0 Then
BMI = Weight / (Height ^ 2)
End If
End Function



the line =nz(BMI(Weight,Height),0)

calls the function and returns 0 if aither of the two parameters is null

Sorry to be slow but I cant get it to work.

In the module code I need to Write

Public Function BMI(Weight As Variant, Height As Variant) As Single

If Weight > 0 And Height > 0 Then
BMI = Weight / (Height ^ 2) (in my case --> BMI = Weight / ((Height/100)*(Height/100))
End If
End Function

and in the Nz control source -

=nz(BMI(WeightControlName,HeightControlName),0) <Do I put in my control names for weight and height? I keep getting an #Name? error

I also tried to test in the immediate window and got a message about expecting a function bot a module or somehting like that. I cant see how your directions are any different than the ones of I have for a tutorial on creating a public function so I am not sure what I am doing wrong.

the function is called from a text box on your form.
The easiest way to test it is to use the function in a query.

Use you own control names for weight and height

THat is so weird. I tried the same thing over and over (the definition of insanity) and it didnt work. I tried it again when I saw your post because I knew I was doing the right thing and it worked fine. How weird. Oh Well.

Thank-you for your help.

Hi I am trying to build another module and am coming up with the name error again :)

This module is to report the total caffeine intake of a participant in a study. The participant reports how many cups of coffee, tea and so on they drink and I need to calculate (based on standard vlaues) how much caffeine they drink.

They might enter 2 for the coffee field
3 for the tea field

so I have to calculate (2 X 75) + (3 X 30) and so on and report this total value in an unbound text field

I thought I could use a public function like I did for BMI but I am getting a #Name error again so I am probably doing something wrong.

I created a new module with the text:

Public Function Caffeine(Instant As Variant, Roasted As Variant, Decaffeinated As Variant, Tea As Variant, Soft As Variant, Energy As Variant) As Single

Caffeine = (Instant * 75) + (Roasted * 80) + (Decaffeinated * 3) + (Tea * 30) + (Soft * 30) + (Energy * 80)

End Function

And in the control source of the unbound field I wrote:

=nz(Caffeine([Forms]![frmStaffDataEntry]![txtCaffeine1],[Forms]![frmStaffDataEntry]![txtCaffeine2],[Forms]![frmStaffDataEntry]![txtCaffeine3],[Forms]![frmStaffDataEntry]![txtCaffeine4],[Forms]![frmStaffDataEntry]![txtCaffeine5],[Forms]![frmStaffDataEntry]![txtCaffeine6]),0)

where txtCaffeine1 through 6 are the values for instant, rpasted etc..

Anyone have any ideas?

personally i dont think you should wrap the function in nz - i would tends to wrap the passed parameters with nz instead, and then the function can have properly typed parameters, rather than variants, which is more efficient

the function should return a value as you have done - which i would make this a double rather than a single - i always err on the side of caution with variable declarations though



so call the function with
caffeinevalue = caffeine(nz(caff1,0), nz(caff2,0), nz(caff3,0) etc

and then the function would be

caffeine(caff1 as double, caff2 as double etc) as double

------
having said all this, the name eror most likely refers to a parameter error - double check that your textboxes ARE txtcaffeine1 through txtcaffeine6

Sorry can you work through that solution a little slower?

I am definitely sure that the fields are name txtCaffeine1 and so on..