I have a form field in which I enter codes that are a combination of letters and numbers. The codes vary in length and sometimes don't include any letters. How can I use VBA to force the letters in the field to be capitalized, even though there are not always letters in that field?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
AllCaps Question 2
- Thread starter Minuet
- Start date
-
2
- #2
Hi!
One way is to use the controls KeyPressed event. It returns the ascii value of the keypressed, and you can manipulate it.
Private sub yourcontrol_KeyPress(KeyAscii as integer)
if keyascii>=97 and keyascii<=122 then
keyascii = keyascii-32
endif
end sub
Whenever a noncap is pressed, it's converted to cap. If you live in a country like mine, with some additional letters, you'll have to do a bit more;-)
HTH Roy-Vidar
One way is to use the controls KeyPressed event. It returns the ascii value of the keypressed, and you can manipulate it.
Private sub yourcontrol_KeyPress(KeyAscii as integer)
if keyascii>=97 and keyascii<=122 then
keyascii = keyascii-32
endif
end sub
Whenever a noncap is pressed, it's converted to cap. If you live in a country like mine, with some additional letters, you'll have to do a bit more;-)
HTH Roy-Vidar
Wrathchild
Technical User
or you might be able to just use the ">" in the input mask
- Thread starter
- #4
- Thread starter
- #5
RoyVidar, your code works, except it does something I find annoying. When the cursor leaves the field, the letters are capitalized, but when I put the cursor back into the field, the letters are shown lowercase. I need a code that converts lowercase letters into capitals and saves them as capitals. Is this possible?
Hi again!
That's strange. I've just tested (again) on both 2000 and XP, with both bound and unbound text controls, and it works fine.
You sure you haven't any other formatting thingies running (either on the control itself or at table level)?
Report back, and we'll try;-)
Roy-Vidar
That's strange. I've just tested (again) on both 2000 and XP, with both bound and unbound text controls, and it works fine.
You sure you haven't any other formatting thingies running (either on the control itself or at table level)?
Report back, and we'll try;-)
Roy-Vidar
btw - using inputmask as Wrathchild suggests, also need some formatting character (check the help for inputmask) for instance "C" that allows any character or space - entry optional or "&" any character or space - entry required. You'll need to input as many charachters in the inputmask as the users might input...
Roy-Vidar
Roy-Vidar
sometimes one tend to even forget dinner (not just what one had for dinner, but to have dinner)
The simplest approach should be to use the afterupdate event of the control and:
me("txtControlName"
=ucase(me("txtControlName")
Roy-Vidar
The simplest approach should be to use the afterupdate event of the control and:
me("txtControlName"
=ucase(me("txtControlName")Roy-Vidar
missinglinq
Programmer
In repsonse to Minuet's question. The trick to converting the lowercase to Caps and keeping them Caps (even if you re-enter the field either passing thru or to edit the field) is to also use the ">" to format the field in the underlying table. I too find it annoying to have the aps reverting to lowercase under these circumstances.
The Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
The Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
- Thread starter
- #11
RoyVidar, I'm still having trouble with your code! I am wondering if it's because of the name of my field, which is "AxisI(1)." Maybe the fact that it has parentheses screws it up? Anyway, it's not working, and neither does Missingling's suggestion of puting ">" in the underlying table. 
missinglinq
Programmer
I would certainly try renaming your field. I'm guessing that Access is having trouble figuring out exactly what "AxisI(1)" is. Looks more like a function or an element in an array.
Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
Hi again!
Well I'm stumped (too). Your and Minuets suggestion to rename the field might be something. I'm a bit conservative there myself, so i never think about it in other peoples thread's, unless the names are present;-)
Whenever i have a fieldname that I think might be the same as a reserved word, I always prefix them. If my imagination isn't quite working, it ends up for instance like fldTime. And never use anything but letters, numbers and underscore (_), never spaces...
Whenever I'm planning to do anything with any control on either a form or report, I prefix them:
text - txt, combos cbo, lists lst... cause MS always uses the same name on the as the recordsource (if created thru wizard) or the very meaningfull names label1, text23 when created in design view.
Back to the issue - as i mentioned, I've tested this on a couple of my machines, Access 2000 and XP, and it works like a charm. I don't know why it shouln't work for you. Some stupid ideas?
* try a requery in the after update of the control -> after the code excecution
* what happens if you run a query on all your data, which updates the AxisI(1) with ucase(AxisI(1))
* if nothing helps, use ucase(AxisI(1)) wherever you use the field:-(
* (repeated) you're sure there aren't any other formatting thingies running on the form, field or control?
Sorry I couldn't be of any more help.
Roy-Vidar
Well I'm stumped (too). Your and Minuets suggestion to rename the field might be something. I'm a bit conservative there myself, so i never think about it in other peoples thread's, unless the names are present;-)
Whenever i have a fieldname that I think might be the same as a reserved word, I always prefix them. If my imagination isn't quite working, it ends up for instance like fldTime. And never use anything but letters, numbers and underscore (_), never spaces...
Whenever I'm planning to do anything with any control on either a form or report, I prefix them:
text - txt, combos cbo, lists lst... cause MS always uses the same name on the as the recordsource (if created thru wizard) or the very meaningfull names label1, text23 when created in design view.
Back to the issue - as i mentioned, I've tested this on a couple of my machines, Access 2000 and XP, and it works like a charm. I don't know why it shouln't work for you. Some stupid ideas?
* try a requery in the after update of the control -> after the code excecution
* what happens if you run a query on all your data, which updates the AxisI(1) with ucase(AxisI(1))
* if nothing helps, use ucase(AxisI(1)) wherever you use the field:-(
* (repeated) you're sure there aren't any other formatting thingies running on the form, field or control?
Sorry I couldn't be of any more help.
Roy-Vidar
- Thread starter
- #14
Roy-Vidar,
I finally got your code to work! I did have some formatting thingies on that field that were preventing the code from working. Thanks so much for helping me out!
When I first created my Access program, I knew nothing about Access and I created really badly named fields which contain characters (like parentheses) and spaces. I have learned the hard way that renaming fields is not a good thing to do because there are so many different places in which you have to rename the field, like in queries, reports, etc. Your code worked without me having to change the name of the field. Would you recommend that I change all my field names to more acceptable names, or should I just leave them?
I finally got your code to work! I did have some formatting thingies on that field that were preventing the code from working. Thanks so much for helping me out!
When I first created my Access program, I knew nothing about Access and I created really badly named fields which contain characters (like parentheses) and spaces. I have learned the hard way that renaming fields is not a good thing to do because there are so many different places in which you have to rename the field, like in queries, reports, etc. Your code worked without me having to change the name of the field. Would you recommend that I change all my field names to more acceptable names, or should I just leave them?
I would say; next time you build a database;-)
To be a bit serious, Access 2000+ have a "Name autokorrect" thingie (Tools | Options | General) which automaticly change the names of the fields/controsl wherever their'e used when you change them EXCEPT IN YOU CODE, where you'll have to do searc|replace "manually".
Do a cost/benefit analyses for yourself, where you take into consideration how much more thingies you're gonna' add to this system, and the benefits of having "good" naming.
Whatever conclusion - DO NOT CHANGE ANYTHING IN THE ORIGINAL DATABASE - ALWAYS TRY OUT IN A COPY - (yes I do shout, cause I've learned the hard way)
There's a saying; Real men don't backup, but often cry;-)
Roy-Vidar
To be a bit serious, Access 2000+ have a "Name autokorrect" thingie (Tools | Options | General) which automaticly change the names of the fields/controsl wherever their'e used when you change them EXCEPT IN YOU CODE, where you'll have to do searc|replace "manually".
Do a cost/benefit analyses for yourself, where you take into consideration how much more thingies you're gonna' add to this system, and the benefits of having "good" naming.
Whatever conclusion - DO NOT CHANGE ANYTHING IN THE ORIGINAL DATABASE - ALWAYS TRY OUT IN A COPY - (yes I do shout, cause I've learned the hard way)
There's a saying; Real men don't backup, but often cry;-)
Roy-Vidar
- Thread starter
- #16
I know this is a little late, but Mr. Vidar, I just came accross this post, and tried out the CAPS thing myself (afterupdate event of a control), and it worked great! Thanks for posting this one previously.. have a star!
Stephen![[infinity]](/data/assets/smilies/infinity.gif "[infinity] [infinity]")
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
- Status
Similar threads
- Locked
- Question
- Locked
- Question
