Validation Rule Problem

[Joe]

I am having a problem with a validation rule that is meant to force users to
enter only upper case in cboDiagnosis which has row source qry_diagnosis.
The validation rule is
=StrComp(UCase([Diagnosis]),[Diagnosis],0)=0

An associated On Not In List event procedure in cbo_diagnosis is
Private Sub cboDiagnosis_NotInList(NewData As String, Response As Integer)
On Error GoTo CboDiagnosis_NotInList_Err
Dim intAnswer As Integer
Dim stSQL As String
intAnswer = MsgBox("The Diagnosis " & Chr(34) & Chr(34) & Chr(34) &
NewData & _
Chr(34) & Chr(34) & Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & NewData & """);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Diagnosis has been added to the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrAdded
Else
MsgBox "Please choose a Diagnosis from the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrContinue
End If
CboDiagnosis_NotInList_Exit:
Exit Sub
CboDiagnosis_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CboDiagnosis_NotInList_Exit
End Sub

On entering lower case data that is not in the list, the user is presented
with the "not in list" error first and then if they click OK to add to list
the data is added as lower case without the validation rule kicking in. What
have I missed here? Do I need some code in Before Update to force the
validation?

Your help is always much appreciated.

 

[Allen Browne]

Regardless of what case you use when you enter the data, JET will not treat
AAA as different from aaa. For example, if you create a unique index on the
field and enter AAA in one row, you will not be able to enter aaa on another
row.

If you need to force an entry to upper case, use the AfterUpdate event
procedure of the control to do so. However, the NotInList will not fire on a
case-sensitive basis.

 

[Ofer Cohen]

Instead of validating the data, you can insert it to the table as Upper case

strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & UCase(NewData) & """);"

 

[Rob Parker]

Hi Joe,

The validation won't be checked until the value has changed. The NotInList
event occurs before that, so the data added to the list is the entered
(unvalidated) data.

The simplest solution would be to apply the UCase function to the data when
it is added. You wouldn't have to bother setting any validation condition.
Change the expression for strSQL to:
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & UCase(NewData) & """);"

You could also use UCase(NewData) in your MsgBox expression.

HTH,

Rob

BTW, in your post, you've declared stSQL in the Dim statement, but are using
strSQL in the code.

 

[Joe]

Thank you Allen, Ofer & Rob,

Your input and help has been great and I've been able to learn from it. I
opted to use the method of inserting directly into the table and modifying
the msg box. The results are very close to what I was trying to achieve. Rob
thanks for picking up the typo in the DIM statement.

Regards,

Joe

Hi Joe,

The validation won't be checked until the value has changed. The NotInList
event occurs before that, so the data added to the list is the entered
(unvalidated) data.

The simplest solution would be to apply the UCase function to the data when
it is added. You wouldn't have to bother setting any validation condition.
Change the expression for strSQL to:
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & UCase(NewData) & """);"

You could also use UCase(NewData) in your MsgBox expression.

HTH,

Rob

BTW, in your post, you've declared stSQL in the Dim statement, but are using
strSQL in the code.

I am having a problem with a validation rule that is meant to force users
to
enter only upper case in cboDiagnosis which has row source qry_diagnosis.
The validation rule is
=StrComp(UCase([Diagnosis]),[Diagnosis],0)=0

An associated On Not In List event procedure in cbo_diagnosis is
Private Sub cboDiagnosis_NotInList(NewData As String, Response As Integer)
On Error GoTo CboDiagnosis_NotInList_Err
Dim intAnswer As Integer
Dim stSQL As String
intAnswer = MsgBox("The Diagnosis " & Chr(34) & Chr(34) & Chr(34) &
NewData & _
Chr(34) & Chr(34) & Chr(34) & " is not currently listed." & vbCrLf
& _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & NewData & """);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Diagnosis has been added to the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrAdded
Else
MsgBox "Please choose a Diagnosis from the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrContinue
End If
CboDiagnosis_NotInList_Exit:
Exit Sub
CboDiagnosis_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CboDiagnosis_NotInList_Exit
End Sub

On entering lower case data that is not in the list, the user is presented
with the "not in list" error first and then if they click OK to add to
list
the data is added as lower case without the validation rule kicking in.
What
have I missed here? Do I need some code in Before Update to force the
validation?

Your help is always much appreciated.