How to Ucase(textbox value)...

[zSplash]

I am an Access novice, and use a form to fill in my tables. I want a
textbox to always be uppercase, no matter what is entered. How do I do
that?

TIA

 

[Peter Hibbs]

You could just enter > in the Format property of the text box.

However, if you always want to have upper case in this field it looks
a bit messy because it only converts the text to upper case after the
user leaves the field and if they move the cursor back to the field
the text reverts to lower case again.

A better method is to enter the following code in the On Key Press
event of the text box. This converts the text to upper case as the
user enters the text.

Private Sub YourTextBoxName_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Peter Hibbs.

 

[zSplash]

Thanks for responding to my question, Peter. What you say makes sense, but
that code doesn't work for me. I put your code on the line for the
textbox's OnKeyPress event, and got this error message:
"Microsoft Access can't find the macro 'Private Sub
tbx1_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
The macro (or its macro group) doesn't exist, or the macro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Realizing that perhaps I don't know how to enter the code as an event, I
left [Event Procedure] on the OnKeyPress event line, and copied your code to
the VBA editor, resulting in this error message:
"The expression On Key Press you entered as the event property setting
produced the following error: Member already exists in an object module
from which this object module derives.
*The expression may not result in the name of a macro the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaulating the function event, or macro."

I've had a lot of practice with the VBA Editor in Word and Excel, but, as I
said, am a complete novice to Access VBA.

Help, please!

TIA

 

[Peter Hibbs]

OK, No problem z (can I call you z).

It sounds to me as if you are trying to enter this code into a Macro.
Anyway, undo any changes you made so that you can start again from
scratch.

1. Open your form in Design mode.
2. Click on the text box (I assume it's called tbx1).
3. Show the Properties list (if not already showing).
4. Click on the On Key Press property and then click on the ... button
on the extreme right.
5. You will probably see a pop up form called Choose Builder (if you
don't then ignore this bit). Click on Code Builder and then click the
OK button.
6. You should then see the VBA window with the code below already
entered :-

Private Sub tbx1_KeyPress(KeyAscii As Integer)

End Sub

7. Enter (or copy and paste) the
KeyAscii = Asc(UCase(Chr(KeyAscii)))
code between these two lines.
8. Click the Save icon on the Access tool-bar and close the window to
return to Design mode.
9. Run the form to test it.

If you still have problems then post back.

Hope that helps.

Peter Hibbs.

Thanks for responding to my question, Peter. What you say makes sense, but
that code doesn't work for me. I put your code on the line for the
textbox's OnKeyPress event, and got this error message:
"Microsoft Access can't find the macro 'Private Sub
tbx1_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
The macro (or its macro group) doesn't exist, or the macro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Realizing that perhaps I don't know how to enter the code as an event, I
left [Event Procedure] on the OnKeyPress event line, and copied your code to
the VBA editor, resulting in this error message:
"The expression On Key Press you entered as the event property setting
produced the following error: Member already exists in an object module
from which this object module derives.
*The expression may not result in the name of a macro the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaulating the function event, or macro."

I've had a lot of practice with the VBA Editor in Word and Excel, but, as I
said, am a complete novice to Access VBA.

Help, please!

TIA

You could just enter > in the Format property of the text box.

However, if you always want to have upper case in this field it looks
a bit messy because it only converts the text to upper case after the
user leaves the field and if they move the cursor back to the field
the text reverts to lower case again.

A better method is to enter the following code in the On Key Press
event of the text box. This converts the text to upper case as the
user enters the text.

Private Sub YourTextBoxName_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Peter Hibbs.

 

[zSplash]

Beautiful, Peter. Thank you so much.

Probably a dumb question, but when would you use the Macro Builder (as
opposed to the Code Builder)? (My feeble mind thinks of a sub as the same
as a macro.)

TIA

OK, No problem z (can I call you z).

It sounds to me as if you are trying to enter this code into a Macro.
Anyway, undo any changes you made so that you can start again from
scratch.

1. Open your form in Design mode.
2. Click on the text box (I assume it's called tbx1).
3. Show the Properties list (if not already showing).
4. Click on the On Key Press property and then click on the ... button
on the extreme right.
5. You will probably see a pop up form called Choose Builder (if you
don't then ignore this bit). Click on Code Builder and then click the
OK button.
6. You should then see the VBA window with the code below already
entered :-

Private Sub tbx1_KeyPress(KeyAscii As Integer)

End Sub

7. Enter (or copy and paste) the
KeyAscii = Asc(UCase(Chr(KeyAscii)))
code between these two lines.
8. Click the Save icon on the Access tool-bar and close the window to
return to Design mode.
9. Run the form to test it.

If you still have problems then post back.

Hope that helps.

Peter Hibbs.

Thanks for responding to my question, Peter. What you say makes sense, but
that code doesn't work for me. I put your code on the line for the
textbox's OnKeyPress event, and got this error message:
"Microsoft Access can't find the macro 'Private Sub
tbx1_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
The macro (or its macro group) doesn't exist, or the macro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's macro group was last saved
under."

Realizing that perhaps I don't know how to enter the code as an event, I
left [Event Procedure] on the OnKeyPress event line, and copied your code to
the VBA editor, resulting in this error message:
"The expression On Key Press you entered as the event property setting
produced the following error: Member already exists in an object module
from which this object module derives.
*The expression may not result in the name of a macro the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaulating the function event, or macro."

I've had a lot of practice with the VBA Editor in Word and Excel, but, as I
said, am a complete novice to Access VBA.

Help, please!

TIA

You could just enter > in the Format property of the text box.

However, if you always want to have upper case in this field it looks
a bit messy because it only converts the text to upper case after the
user leaves the field and if they move the cursor back to the field
the text reverts to lower case again.

A better method is to enter the following code in the On Key Press
event of the text box. This converts the text to upper case as the
user enters the text.

Private Sub YourTextBoxName_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Peter Hibbs.

On Fri, 5 Jan 2007 12:58:21 -0900, "zSplash"

I am an Access novice, and use a form to fill in my tables. I want a
textbox to always be uppercase, no matter what is entered. How do I do
that?

TIA

 

[John Vinson]

Probably a dumb question, but when would you use the Macro Builder (as
opposed to the Code Builder)? (My feeble mind thinks of a sub as the same
as a macro.)

It's confusing, because in other Office applications (e.g. Excel) VBA
routines are referred to as "macros". In Access, VBA routines are in
Modules (either in the Modules collection on the database tab, or as
Form or Report Modules attached to those objects). Macros are MUCH
more limited, non-procedural, step by step representations of form
actions; they're maybe a bit easier to start with for simple tasks,
but their limitations get obvious pretty quickly.

John W. Vinson[MVP]

 

[Peter Hibbs]

Hi
As John says, Macros are very difficult to debug and are very limited.
Most Access Developers do not use Macros at all (except for a special
one called AutoKeys, look up Help or post a new question if you would
like more info on this one).

If you are serious about writing databases I would strongly recommend
that you buy a book on VBA and learn up about it, you will find it
very rewarding in the long run. Also you would need to learn about SQL
but either language is not really difficult.

If you do use VBA a lot remember to tick the 'Always use event
procedures' box on the Forms/Reports tab of the Options form to stop
the Choose Builder form being displayed every time you want to write
some VBA code.

Good luck with your project.

Peter Hibbs

 

[zSplash]

Thanks, John and Peter. I really love VBA, and am starting to get attached
to Access, as well. I'll take your suggestions to heart!

Really appreciate your help...

st.