AfterUpdate Expression

  • Thread starter Thread starter Christopher Glaeser
  • Start date Start date
C

Christopher Glaeser

I am using a fuction Proper() that changes the first letter of each word to
upper case. The code works fine if I use the codebuilder to call an
AfterUpdate subroutine, but I would prefer to put an expression in the
AfterUpdate property field, as in ...

=Proper([City])

Setting a breakpoint in the function Proper() shows that it is working
correctly, but the [City] field is not correctly updated. Any ideas why?

Best,
Christopher
 
Hi Christopher

When you specify a function for an event, instead of an event procedure, the
function gets called, but the return value is ignored. There is no possible
logical action to take with it! For this reason, such a function must
behave like a Sub, although it is still requires to be declared as a
Function.

You could do this two ways:

1. Pass the control (not the value in the control) to your function:

=ProperTextBox([City])

Public Function ProperTextBox (ctl as Control)
On Error Resume Next
With ctl
If .ControlType = acTextBox Then
.Value = Proper(.Value)
End If
End With
End Function

2. Write a general-purpose function which calls Proper on the active
control. Then you don't need to pass it anything:

=ProperActiveTextBox()

Public Function ProperActiveTextBox ()
On Error Resume Next
With Screen.ActiveControl
If .ControlType = acTextBox Then
.Value = Proper(.Value)
End If
End With
End Function
 
2. Write a general-purpose function which calls Proper on the active
control. Then you don't need to pass it anything:
Click to expand...

Thanks Graham, I like them apples! :)

Best,
Christopher
 
Back
Top