Enable a field Based on the Length of another field

[Guest]

In a subform I am inputting records. If the length of alngzPatientNumber
field is 7 then I want to enable the chrPatientName field which is the last
field in the record, otherwise I do not want it enabled. Here is the code I
am using but when I run it the debug error window comes up.

Private Sub txtPatientNumber_Exit(Cancel As Integer)
If (Len(Me!lngzPatientNumber) = 7) Then
Me.chrPatientName.Enabled = True
Else
Me.chrPatientName.Enabled = False
End If

End Sub

 

[John Spencer]

I would try the following. You should be testing the control's value not
the field's value. Also, you had alngzPatientNumber and lngzPatientNumber
for the field name in your posting.

Private Sub txtPatientNumber_Exit(Cancel As Integer)
If (Len(Me!txtPatientNumber & "") = 7) Then
Me.chrPatientName.Enabled = True
Else
Me.chrPatientName.Enabled = False
End If

End Sub

You said the debug window comes up, but you didn't tell us which line is
causing the error and you didn't tell us what error message you were
getting. That information is helpful and often makes a difference in the
advice you get.

 

[Guest]

Thanks John.

I actually ended up changing all of the chrPatientName field names to
txtPatientName and it worked.

All the best,

 

[Guest]

John, what does the & "" do after the field name?

 

[John Spencer]

It forces the argument passed to the Len function to be a string of at
least zero characters in length.
--

 

[Terry Kreft]

Mary to expand on what John says.

If you pass a Null value to the Len function this returns a Null, if you
compare a Null to another value this will return a Null, by concatenating
the "" to the value John is forcing the value to be a string the length of
which can then be tested properly.

e.g. If you had the following function

Function TestEmptyString(ValIn As Variant) As Boolean
If Len(ValIn) < 1 Then
TestEmptyString = True
Else
TestEmptyString = False
End If
End Function

If you then called the function from the debug window with
?TestEmptyString(Null)
False

if you now do this (as John recommends)
?TestEmptyString(Null & "")
True

Which is probably the answer you want.

 

[Guest]

Cool trick. Thanks John

It forces the argument passed to the Len function to be a string of at
least zero characters in length.

 

[Guest]

I appreciate clarification. It makes perfect sense now. All the best.