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.
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.