Form field validation error. (1 Viewer)

[Sreemike]

Please help:
I have three fields in a form;
Basal (this is the background reading of a blood test)
Ctl50_1 (this should give the highest reading in all patients tested and must be more than basal)
Ctl25_1 (this must be a value less than 50_1 but more than basal)
Ctl12_1 (must have a value less than 25_1 but more than basal)
I have used the “IF” statements to ensure the user is entering validated values to all three fields.
I wish to ensure that when (Ctl25_1 – basal) is 2, the user must be aware that the value in the Ctl12_1 field can only be (basal + 1). Using my code shown below, I am getting multiple validated statements rather than just the first msgbox displaying “Please enter …. “ Also ,even entering the correct value will not let me proceed further as the curser stays on the ctl12_1 field.
When Ctl25_1 – Basal = 2, I just want it to display “Please enter (basal+1) and only proceed further when the user enter (basal+1) value. No further msgboxes should be displayed.
Any help is greatly appreciated.


Private Sub Ctl50_1_BeforeUpdate(Cancel As Integer)
Dim a As Integer
Dim b As String

a = Nz(Basal, 0)
b = "Value entered must be higher than" & " " & (Basal + 2)
If Ctl50_1 < (a + 3) Then
MsgBox (b) & ".", vbInformation, "Value Too Low"
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 Integer
Dim d As String
Dim e As String

a = Nz(Basal, 0)
b = Nz(Basal + 2, 0)
c = Nz(Ctl50_1 - 1, 1000)
d = "Ensure value is between" & " " & (b) & " and " & (c)

If Ctl25_1 < (Basal + 2) Then
MsgBox (d) & ".", vbInformation, "Value Too Low"
Cancel = True
End If
If Ctl25_1 >= Ctl50_1 Then
MsgBox (d) & ".", vbInformation, "Value Higher than 50:1"
Cancel = True
End If
End Sub
__________________________________________________ _____________________________

Private Sub Ctl25_1_AfterUpdate()
If Ctl25_1 - Basal = 2 Then
MsgBox "12:1 value must be" & " " & (Basal + 1), vbInformation, "12:1 Value"
End If
End Sub
__________________________________________________ ________________________________
Private Sub Ctl12_1_BeforeUpdate(Cancel As Integer)
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As String

a = Nz(Basal, 0)
b = Nz(Basal + 1, 0)
c = Nz(Ctl25_1 - 1, 1000)
d = "Ensure value is between" & " " & (b) & " and " & (c) & "."
If (Ctl25_1 - Basal) = 2 Then
MsgBox "Please enter" & " " & (Basal + 1)
Cancel = True
End If

If Ctl12_1 < b Then
MsgBox (d), vbInformation, "Value Too Low"
Cancel = True
End If
If Ctl12_1 >= Ctl25_1 Then
MsgBox (d), vbInformation, "Value Higher than 25:1"
Cancel = True
End If
End Sub

 

[pbaldy]

You can add this after the Cancel = True which will make it skip all further code:

Exit Sub

 

[Sreemike]

Thanks for the reply pbaldy.Tried it but even when I type in the correct value in the Ctl12_1 field of the form, the msgbox appears and the curser stays on the field, thereby preventing me from going to the next field of the form. I am convinced the problem is the code below highlighted brown:

Private Sub Ctl12_1_BeforeUpdate(Cancel As Integer)
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As String
a = Nz(Basal, 0)
b = Nz(Basal + 1, 0)
c = Nz(Ctl25_1 - 1, 1000)

d = "Ensure value is between" & " " & (b) & " and " & (c) & "."

If (Ctl25_1 - Basal) = 2 Then 'am I going wrong here with the code??
MsgBox "Enter" & " " & (Basal + 1)
Cancel = True
Exit Sub
End If

If Ctl12_1 < b Then
MsgBox (d), vbInformation, "Value Too Low"
Cancel = True
End If

If Ctl12_1 >= Ctl25_1 Then
MsgBox (d), vbInformation, "Value Higher than 25:1"
Cancel = True
End If

End Sub


As long as the (Ctl25_1 field - basal) is 2, I am stuck in the Ctl12_1 field despite entering the validated number. I can overcome this by deleting the Cancel=True, but this would allow the user to enter any number they choose in the Ctl12_1 field. Any thoughts on this matter would be a great help.

 

[pbaldy]

Do you mean to be testing a different field there? What values would be in the relevant fields at that point?

 

[Sreemike]

Basal field, for example, can be 6. Then the Ctl50_1 field must be at least more than 8. If we say that the Ctl50_1 value was 9, then the Ctl25_1 field must have a value of 8. The Ctl12_1 value can only be 7.
But I think I solved it by modifying the code of Ctl12_1 before update as follows:

If (Ctl25_1 - Basal) = 2 And ((Ctl12_1 - Basal) >= (Ctl25_1 - Basal) Or (Ctl12_1 <= Basal)) Then
MsgBox "Enter" & " " & (Basal + 1)
Cancel = True
Exit Sub
End If

This seems to solve my validation problems.

 

[pbaldy]

Glad you got it sorted out.