Function as event handler

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I thought I could code a function reference in the event-handler box for a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and tab
out, they remain lower-case.

I know I've seen this done before!

Gary
 
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.
 
Hi,
Why not just select [event handler]
click on the ...
and enter this into the event:
Me.txtFld = UCase(Me.txtFld )
 
Ken,

I copied your code into my general module and then used the statement:

=MyUcase()

in the AfterUpdate field for the text box.

I get no run-time error, but it doesn't convert my lowercase characters to
uppercase, either.

This is frustrating: If I use a macro with a Setval action in it, it works,
but then I have to write a separate macro for each control I want to convert
to upper case.

Gary

Ken Snell said:
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

Gary Schuldt said:
I thought I could code a function reference in the event-handler box for a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
 
Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ken Snell said:
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

Gary Schuldt said:
I thought I could code a function reference in the event-handler box for a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
 
Gary Schuldt said:
I thought I could code a function reference in the event-handler box
for a control, but I'm missing something, since I can't get it to
work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
and tab out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...

You need to invoke a function that actually updates the field as a side
effect, not by virtue of its return value. As it is, the Ucase function
will return the up-cased value, but nothing will be done with it.

If you want a general-purpose function that will up-case whatever
control has the focus, try this:

'----- start of function code -----
Function UcaseActiveControl()

With Screen.ActiveControl
.Value = UCase(.Value)
End With

End Function
'----- end of function code -----

Now you can call this from the AfterUpdate event of your text box:

=UcaseActiveControl()

Of course, you could also make the function take an argument, the
control to be upcased, but that just adds complexity to something that
is pretty simple.
 
Dan,

sorry, I couldn't get that approach to work.

Gary

Dan Artuso said:
Hi,
Why not just select [event handler]
click on the ...
and enter this into the event:
Me.txtFld = UCase(Me.txtFld )



--
HTH
Dan Artuso, Access MVP


I thought I could code a function reference in the event-handler box for a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
 
Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Douglas J. Steele said:
Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ken Snell said:
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.
Click to expand...
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
Click to expand...
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
Hey, Dirk,

Thanks again for bailing me out. I copied your code and it seems to work
fine. Now I've got general functions having the same pattern for lower case
and proper (title) case as well, which will really speed up data entry.

(FYI, the plant botanical names, consisting of Family, Genus, species, and
Cultivar have their case rules set by an international standard. Lucky me!
I can auto-correct whatever the user types, at least as far as case goes.)

Gary

Dirk Goldgar said:
Gary Schuldt said:
I thought I could code a function reference in the event-handler box
for a control, but I'm missing something, since I can't get it to
work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
and tab out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...

You need to invoke a function that actually updates the field as a side
effect, not by virtue of its return value. As it is, the Ucase function
will return the up-cased value, but nothing will be done with it.

If you want a general-purpose function that will up-case whatever
control has the focus, try this:

'----- start of function code -----
Function UcaseActiveControl()

With Screen.ActiveControl
.Value = UCase(.Value)
End With

End Function
'----- end of function code -----

Now you can call this from the AfterUpdate event of your text box:

=UcaseActiveControl()

Of course, you could also make the function take an argument, the
control to be upcased, but that just adds complexity to something that
is pretty simple.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Gary Schuldt said:
Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context
Click to expand...

Actually, it doesn't. For example, I sometimes test events by putting

=MsgBox("Boo!")

in the event property. But aside from that, only a user-defined
function is likely to be of much use.
 
Gary Schuldt said:
(FYI, the plant botanical names, consisting of Family, Genus,
species, and Cultivar have their case rules set by an international
standard. Lucky me! I can auto-correct whatever the user types, at
least as far as case goes.)
Click to expand...

That is handy. The names of people are not so easy to get right.
 
re: names of people

Like e e cummings . . . or my former colleague III (pronounced like the
numeral 3) . . . or my hometown next door nabors the VanTines . . . as
opposed to the von Trapps . . . but all of them would be offended by my
PCase function. Fortunately, the plants are more orderly!

Gary
 
"doh"! Thanks, Doug...got my fingers tongue-tied on this one!

--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ken Snell said:
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.
Click to expand...
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
Click to expand...
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
Hello Group

Maybe wrong thinking or not but why not just format the field which must
have uppercases with >

Herman
 
Herman,

Actually, what I really wanted to do was more complex than that . . . I just
didn't admit it! I had several different controls with different case
requirements, including the need to capitalize the first letter of every
word the user typed in on one of the controls.

So I got what I wanted--a way to manipulate the active control with a
function reference, something I can reuse in the future.

Thanks for your suggestion.

Gary

hermie said:
Hello Group

Maybe wrong thinking or not but why not just format the field which must
have uppercases with >

Herman

Gary Schuldt said:
I thought I could code a function reference in the event-handler box for a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
 
I cannot get this code to work. Please tell me what I am doing wrong.

I opened the properties for the field that I wish to have converted to
uppercase, Clicked the "..." button on the "After Update" line under "Event"
tab.
Selected "Code Builder" from options.
Pasted the suggested code but it still did not work. Am I supposed to
replace "Screen", "ActiveControl" and/or "Value" with specific information?

Trina Gray


Gary Schuldt said:
Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Douglas J. Steele said:
Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ken Snell said:
I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I thought I could code a function reference in the event-handler box
Click to expand...
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
You don't put the function in the form's module.

Create this function in a regular module:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function

Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

Trina Gray said:
I cannot get this code to work. Please tell me what I am doing wrong.

I opened the properties for the field that I wish to have converted to
uppercase, Clicked the "..." button on the "After Update" line under "Event"
tab.
Selected "Code Builder" from options.
Pasted the suggested code but it still did not work. Am I supposed to
replace "Screen", "ActiveControl" and/or "Value" with specific information?

Trina Gray


Gary Schuldt said:
Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Douglas J. Steele said:
Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I thought I could code a function reference in the event-handler
Click to expand...
box
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the
Click to expand...
field
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
By regular module are you refering to creating a new module? Please explain
how I "use MyUcase() as the event handler function".

Thanks,
Trina Gray

Ken Snell said:
You don't put the function in the form's module.

Create this function in a regular module:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function

Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

Trina Gray said:
I cannot get this code to work. Please tell me what I am doing wrong.

I opened the properties for the field that I wish to have converted to
uppercase, Clicked the "..." button on the "After Update" line under "Event"
tab.
Selected "Code Builder" from options.
Pasted the suggested code but it still did not work. Am I supposed to
replace "Screen", "ActiveControl" and/or "Value" with specific information?

Trina Gray


Gary Schuldt said:
Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I thought I could code a function reference in the event-handler box
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
Regular module means a module that you create from the database window.

To use the function as the event handler, type this in the property box next
to After Update event on the Event tab for the textbox's Properties:

=MyUcase()

--

Ken Snell
<MS ACCESS MVP>

Trina Gray said:
By regular module are you refering to creating a new module? Please explain
how I "use MyUcase() as the event handler function".

Thanks,
Trina Gray

Ken Snell said:
You don't put the function in the form's module.

Create this function in a regular module:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function

Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

Trina Gray said:
I cannot get this code to work. Please tell me what I am doing wrong.

I opened the properties for the field that I wish to have converted to
uppercase, Clicked the "..." button on the "After Update" line under "Event"
tab.
Selected "Code Builder" from options.
Pasted the suggested code but it still did not work. Am I supposed to
replace "Screen", "ActiveControl" and/or "Value" with specific information?

Trina Gray


:

Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Actually, you need to set the value of Screen.ActiveControl. It doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I thought I could code a function reference in the
Click to expand...
event-handler
box
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the field
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
Thank you! Thank you! Thank you! I put everything in place per your
instruction and it worked perfectly.

Thanks again,
Trina Gray

Ken Snell said:
Regular module means a module that you create from the database window.

To use the function as the event handler, type this in the property box next
to After Update event on the Event tab for the textbox's Properties:

=MyUcase()

--

Ken Snell
<MS ACCESS MVP>

Trina Gray said:
By regular module are you refering to creating a new module? Please explain
how I "use MyUcase() as the event handler function".

Thanks,
Trina Gray

Ken Snell said:
You don't put the function in the form's module.

Create this function in a regular module:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function

Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I cannot get this code to work. Please tell me what I am doing wrong.

I opened the properties for the field that I wish to have converted to
uppercase, Clicked the "..." button on the "After Update" line under
"Event"
tab.
Selected "Code Builder" from options.
Pasted the suggested code but it still did not work. Am I supposed to
replace "Screen", "ActiveControl" and/or "Value" with specific
information?

Trina Gray


:

Ah, that's the trick! I remember now:

1. It HAS to be a user function to be used in one-liner event handler
context

2. It doesn't matter what the function value is set to

3. The function code (or something it calls, like a Sub) must set the
value(s) you want.

How COULD I have forgotten?? <g>

Thanks,

Doug

Actually, you need to set the value of Screen.ActiveControl. It
doesn't
matter whether or not the function returns a value:

Public Function MyUcase() As String
Screen.ActiveControl.Value = UCase(Screen.ActiveControl.Value)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I believe you must create your own function and call it, not use the
built-in function that requires an argument.

Create this function in a regular module:

Public Function MyUcase() As String
MyUcase = UCase(Screen.ActiveControl.Value)
End Function


Then use MyUcase() as the event handler function.

--

Ken Snell
<MS ACCESS MVP>

I thought I could code a function reference in the event-handler
box
for
a
control, but I'm missing something, since I can't get it to work.

I want to make a text field all upper case AfterUpdate.

The Expression Builder guides me through to yield the entry:

=Ucase([txtFld])

but it doesn't work. When I type lower-case letters into the
field
and
tab
out, they remain lower-case.

I know I've seen this done before!

Gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top