ZLS??

[Guest]

On a data entry form I have suddenly developed an error on entering the
surname:

“Function: ProperLookup - Error#: 3219: Invalid operationâ€

When I click ‘OK’ I get another error: “Run-time error ‘3315’: Field
‘tblPatients.Surname’ cannot be a zero-length stringâ€

This is the code

Private Sub txtSurname_AfterUpdate()
Dim strUpper As String
Dim varSurname As Variant
Me!txtSurname = ProperLookup(Me!txtSurname)
If Left(Me!txtSurname, 2) = "Mc" Then
varSurname = Me!txtSurname
strUpper = Mid(varSurname, 3, 1)
strUpper = UCase(strUpper)
Mid(varSurname, 3, 1) = strUpper
Me!txtSurname = varSurname
End If
End Sub

and the first line of the function

Function ProperLookup(ByVal varInText As Variant) As Variant.

On debugging, the surname is picked up by the procedure, but is not being
passed on to the function. I have been using this form for some years and
never had any problems.

Any help much appreciated.

 

[Wayne Morgan]

By chance have you added any surnames to the data that have apostrophes in
them, such as O'Hare?

 

[Graham Mandeno]

Hi Sandy

What is in your txtSurname textbox at the time this error occurs? Is it
blank?

I can't tell what ProperLookup is doing, because you didn't post the code,
but my guess is that you are passing it a Null and it is returning a
zero-length string (""). Note that these might look the same in a textbox,
but they are very different.

A text field in a table has two properties, Required and AllowZeroLength.
Required indicates whether or not the field may contain a Null, while
AllowZeroLength indicates whether it may contain a ZLS. Your Surname field
clearly has AllowZeroLength set to False, so the ZLS returned by your
function cannot be assigned to the textbox which is bound to that field.

Basically, you don't want your code to run at all if the textbox is Null, so
at the beginning of your event procedure, insert the line:
If IsNull(txtSurname) Then Exit Sub

The other possibility is that you are actually passing some text, but your
function is malfunctioning and is returning a ZLS when it should not. If
this is the case, post the code of the function and also tell us what is in
the textbox at the time.

 

[Guest]

Hi Wayne and Graham

Thanks for your help. It turns out that I had linked my front end of my
application to another version of the back end by mistake, hence the error.
Relinking to the correct tables solved the problem.

The ProperLookUp function is one I cribbed and modified from Microsoft. It's
similar to the more commonly used Proper() function, but uses a table to look
up words that don't fit the general formula.

Thanks again.

Hi Sandy

What is in your txtSurname textbox at the time this error occurs? Is it
blank?

I can't tell what ProperLookup is doing, because you didn't post the code,
but my guess is that you are passing it a Null and it is returning a
zero-length string (""). Note that these might look the same in a textbox,
but they are very different.

A text field in a table has two properties, Required and AllowZeroLength.
Required indicates whether or not the field may contain a Null, while
AllowZeroLength indicates whether it may contain a ZLS. Your Surname field
clearly has AllowZeroLength set to False, so the ZLS returned by your
function cannot be assigned to the textbox which is bound to that field.

Basically, you don't want your code to run at all if the textbox is Null, so
at the beginning of your event procedure, insert the line:
If IsNull(txtSurname) Then Exit Sub

The other possibility is that you are actually passing some text, but your
function is malfunctioning and is returning a ZLS when it should not. If
this is the case, post the code of the function and also tell us what is in
the textbox at the time.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

On a data entry form I have suddenly developed an error on entering the
surname:

"Function: ProperLookup - Error#: 3219: Invalid operation"

When I click 'OK' I get another error: "Run-time error '3315': Field
'tblPatients.Surname' cannot be a zero-length string"

This is the code

Private Sub txtSurname_AfterUpdate()
Dim strUpper As String
Dim varSurname As Variant
Me!txtSurname = ProperLookup(Me!txtSurname)
If Left(Me!txtSurname, 2) = "Mc" Then
varSurname = Me!txtSurname
strUpper = Mid(varSurname, 3, 1)
strUpper = UCase(strUpper)
Mid(varSurname, 3, 1) = strUpper
Me!txtSurname = varSurname
End If
End Sub

and the first line of the function

Function ProperLookup(ByVal varInText As Variant) As Variant.

On debugging, the surname is picked up by the procedure, but is not being
passed on to the function. I have been using this form for some years and
never had any problems.

Any help much appreciated.