Preventing Run-time error 94 (1 Viewer)

[Sreemike]

I have a database with a form containing three fields (Basal, ctl50_1 and ctl25_1). The user will have to enter blood test results(numerical) to all of the three fields per patient. I am bit of a novice at VBA, but here is the code I wrote to ensure that the ctl50_1 value is always higher than Basal and ctl25_1 lower than ctl50_1 but higher than basal. And it works fine.

Private Sub Ctl50_1_BeforeUpdate(Cancel As Integer)
Dim a As Integer
a = Basal
If Ctl50_1 < Basal Then
MsgBox "Value must be higher than" & " " & a, vbInformation, "Value Lower than Basal"
Cancel = True
End If
End Sub

Private Sub Ctl25_1_BeforeUpdate(Cancel As Integer)
Dim a As Integer
Dim b As Integer
Dim c As String
Dim d As String

a = Ctl50_1
b = Basal

If Ctl25_1 < Basal Then
c = "Value must be higher than" & " " & b
MsgBox (c) & ".", vbInformation, "Value Lower than Basal"
Cancel = True
End If
If Ctl25_1 > Ctl50_1 Then
d = "Value must the lower than" & " " & a
MsgBox (d) & ".", vbInformation, "Value Higher than 50:1"
Cancel = True
End If
End Sub


The problem arises if the user leave the ctl50_1 field blank or go back and delete the field after the completion of all three. When this happens, an error described below is displayed.

Run-time error ‘94’
Invalid use of null

As the database run using a switchboard, I really don't like the idea of users messing around in the VBA area. Is there any way I can prevent this error from occurring? Any advise/help would be much appreciated.

 

[NickHa]

Presumably the error occurs on the line

a = Ctl50_1

This is because the Ctl50_1 field has a null value, which can't be assigned to variable 'a'.
The solution is to use the Nz built-in function

a = Nz(Ctl50_1, 0)

where the 0 is the value passed to variable 'a' when Ctl50_1 is null.

 

[Sreemike]

Yes, that works. Thanks very much NickHa. Never came across the Nz function in any of the vba books. Once again, many thanks.