DLookup and requery - quick question

[ahuvas]

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?

 

[wh00t]

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 & ")

 

[ahuvas]

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.

 

[wh00t]

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

 

[Dennisk]

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

 

[ahuvas]

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

 

[Dennisk]

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

 

[ahuvas]

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.

 

[Dennisk]

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

 

[ahuvas]

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.

 

[ahuvas]

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?

 

[gemma-the-husky]

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

 

[ahuvas]

Sorry can you work through that solution a little slower?

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