How to save a calculated value based on two textboxes to the db?

[rigid_designator]

I have a simple question that probably has an obvious answer.



I have a form in which the user can enter, among other things, a person's height. I would like to have a textbox for feet and a textbox for inches. Then, I would like to calculate the person's height in inches and save that number to the database.

I've tried accomplishing this in a number of ways but can't get it quite right. The closest I've gotten by having an invisible text box named "Text_Height" which is bound to a "Height" field in the database. When a value is entered or changed in either the feet or inches textbox, the AfterUpdate() event calls a subroutine which calculates and sets the value of Text_Height. But I've noticed that it doesn't always work correctly, and, further, it seems like a rather backward way of going about it.

I've done quite a bit of searching, but I'm having trouble finding advice. Is there a better way? Can anyone shed some light on a best practice? It would be much appreciated.

[rigid_designator]

Illustrating the only way I've been able to get it to work:

Private Sub Update_Text_Height()
    Text_Height = Text_Feet * 12 + Text_Inches
End Sub

Private Sub Text_Feet_AfterUpdate()
    Call Update_Text_Height
End Sub

Private Sub Text_Inches_AfterUpdate()
    Call Update_Text_Height
End Sub

This works reasonably well, with the exception of the following behavior:

If I enter a number in Text_Feet, then move focus to Text_Inches, I would expect a number to appear in Text_Height... but nothing does. Only after also entering a number in Text_Inches and focusing away from it does the calculated value appear in Text_Height.

This wouldn't bother me, except what if I person is, say, exactly 5 feet? The user may enter 5 in Text_Feet, and skip right over Text_Inches. Then, no height would be recorded.

I can't help but think there's a proper way to accomplish this that I'm overlooking.

[rigid_designator]

Well, I figured out the problem was in my arithmetic.

Text_Height = Text_Feet * 12 + Text_Inches

If a text box is empty, it returns null. Although I'm not entirely sure what's going on under the hood, but my guess is that because vb is dynamically-typed, I can do arithmetic with that null value, which ostensibly results in the value of Text_Height also being null.

I tried this for poops and giggles, but it yelled at me at runtime:

Text_Height = CInt(Text_Feet) * 12 + CInt(Text_Inches)

So this was the very verbose solution:

   Dim feet As Integer
   If IsNull(Text_Feet) Then
        feet = 0
    Else
        feet = Text_Feet
    End If
    
    Dim inches As Integer
    If IsNull(Text_Inches) Then
        inches = 0
    Else
        inches = Text_Inches
    End If    
    
   Text_Height = feet * 12 + inches

On a related note, this much more compact code gives me an "Invalid use of null" error:

feet = IIf( IsNull(Text_Feet.Value), 0, CInt(Text_Feet) )
inches = IIf( IsNull(Text_Inches.Value), 0, CInt(Text_Inches) )

I like it much better and I wish I knew why.

And after doing lots more reading, this apparently is in fact the standard way to save a calculated value to a table. I think it's weird. Oh well.

Have a nice day.

[rigid_designator]

Ok, one last note regarding this:

feet = IIf( IsNull(Text_Feet.Value), 0, CInt(Text_Feet) )
inches = IIf( IsNull(Text_Inches.Value), 0, CInt(Text_Inches) )

This does not work, because (according to http://msdn.microsoft.com/en-us/libr...=vs.71%29.aspx):

The expressions in the argument list can include function calls. As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression.

Which I suppose is the side-effect of not having a actual ternary operator, but faking it with a function instead. CInt() will not take a null value as an argument, and so the code throws a runtime exception.

But as it turns out, and again because VBA is so loose with typing, the CInt() isn't even necessary, so my final solution is this:

Private Sub Update_Text_Height()
     Text_Height = IIf(IsNull(Text_Feet), 0, Text_Feet) * 12 + IIf(IsNull(Text_Inches), 0, Text_Inches)
End Sub