Forcing Capital Letters in Text Box

[Richie2837]

I have a contacts database set up to record all our charities' clients. I have set up the relevant fields to force first-letter capitals for their first name, surname and so on, but how do I do this for their address, where there can be a mixture of numbers and text with spaces in-between (i.e. 57 The Highway) - how do I set the input mask to force capitals for the first letter of each word while not getting freaked out by the numbers?

 

[CraigDolphin]

One way to do this is to use a module to convert the text into Proper case:

Place the following in a module:

' Convert the text into proper case.
Function ProperCase(ByVal MyString As String) As String

    Dim strArr() As String
    strArr() = Split(MyString, " ")
    
    'After creating an array to include all the words in the selected text...
    For X = LBound(strArr) To UBound(strArr)
      '...Set each word to lower case
      strArr(X) = StrConv(strArr(X), vbLowerCase)
       
      'Skip unimportnt words
      Select Case strArr(X)
        Case "a"
        Case "the"
        Case "and"
        Case "or"
        Case "of"
        Case "for"
        Case "by"
        Case "to"
        Case "on"
        Case "but"
        Case "in"

                
        'Capitalize other, important words
        Case Else
          strArr(X) = StrConv(strArr(X), vbProperCase)
      End Select
    
    Next X
       
      'Capitalize the first word
      strArr(0) = StrConv(strArr(0), vbProperCase)
    txt = ""
    For X = LBound(strArr) To UBound(strArr)
      If X = UBound(strArr) Then
      txt = txt & strArr(X)
      Else
      txt = txt & strArr(X) & " "
      End If
    Next X

    ProperCase = txt
        Saved = False

End Function

And this in the after_update event for your address control

Me.[Address_Control_Name_Here] = ProperCase([Address_Control_Name_Here])

And obviously, substitute the name of your control as indicated...

 

[CEH]

To make first letter of each word upper case
ON AfterUpdate

[MyFieldName]=StrConv([MyFieldName],vbProperCase)

 

[Moniker]

Sometimes it's a little too easy.

 

[Richie2837]

Thanks for that Curtis, I have applied the code to the AfterUpdate, but when I type into the field now I get this message:

"Database can't find the macro '[fieldname]=StrConv([fieldname], vbProperCase)'. The macro (or its macro group) deosn'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."

Sorry if this is a basic mistake with a really obvious answer, but I'm a mere novice at all this!

 

[Rickster57]

forms

In design view of that form click in the After Update property of the [myfieldname] textbox. You will then see a down arrow to the far right. Click on that and click on Event Procedure. You will see a button to the far right with dots on it. Click on that and then paste CEH's code in there.

 

[Brianwarnock]

This issue was tackled in a thread in Theory of Database Design, Dreamweavers solution capitalises after hyphens, I guess a combination of craigs and his is the rolls royce solution. Using vbProperCase is a little simplistic but if it gives you what you want fine. BTW you may have to code

[MyFieldName]=StrConv([MyFieldName],3)

instead of

[MyFieldName]=StrConv([MyFieldName],vbProperCase)

Brian

 

[Dennisk]

What you require is a Proper function - there are many available

then in the After_update for each control

Controlname=Proper(ControlName)

A good function will take care /ignore numbers etc

 

[neileg]

Thanks for that Curtis, I have applied the code to the AfterUpdate, but when I type into the field now I get this message:

"Database can't find the macro '[fieldname]=StrConv([fieldname], vbProperCase)'. The macro (or its macro group) deosn'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."

Sorry if this is a basic mistake with a really obvious answer, but I'm a mere novice at all this!

Err, you have to substitute your own field name into that code.

 

[Moniker]

What you require is a Proper function - there are many available

then in the After_update for each control

Controlname=Proper(ControlName)

A good function will take care /ignore numbers etc

I've never had any issues using StrConv([strName],vbProperCase), at least when it comes to numbers and symbols. It won't handle things like this:

mike mcdonald --- becomes --- Mike Mcdonald
martin van buren --- becomes --- Martin Van Buren

However, the names like that are few and far between and you can usually write a few "cleanup" queries to fix those. Still, vbProperCase will work as expected 99% of the time, at least for the English language. Perhaps there's an issue with Dutch or another language that I'm not aware of.

 

[ghudson]

Searching is a great way to discover the answers to your Access programming questions.

The Capital Problem thread has the fix that will account for those odd sur names and special characters.

 

[CEH]

Heres one of interest.... I have fields set to convert to proper case when you tab to the next field.... But there are times when you want it left exactly as you type it. So..... Set the tab to execute the "vbProperCase" and if you want it left alone use the Enter key (Of course you need to put the word "Cap" in each fields "Tag" property that you want to enable this on)
I haven't had any trouble with this... I believe it is done correctly (coding that is)


Private Sub Form_Load()
Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyTab And Screen.ActiveControl.Tag = "Cap" Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If
End Sub

 

[mloucel]

To make first letter of each word upper case
ON AfterUpdate

[MyFieldName]=StrConv([MyFieldName],vbProperCase)

Thanks, just what I needed.. I just did vbUpperCase which is what I need

 

[cheekybuddha]

I just did vbUpperCase which is what I need

If you want all upper case you can also use:

[MyFieldName]=UCase([MyFieldName])

 

[Gasman]

Thanks, just what I needed.. I just did vbUpperCase which is what I need

18 year old thread?

 

[cheekybuddha]

Is it so bad to necro a thread to show your appreciation? It's different from offering another solution as if the participants are still watching it actively. I suppose it also validates the given answers for others who find the thread through the search.

 

[Gasman]

Is it so bad to necro a thread to show your appreciation? It's different from offering another solution as if the participants are still watching it actively. I suppose it also validates the given answers for others who find the thread through the search.

Just pointing out to the poster the age of the thread?
Unlikely to get a 'You are welcome' response.?

 

[mloucel]

18 year old thread?

Hey, I'm a newbie, a rookie, and all this threads are like miracles to me. In this forums I've found the most incredible helpful programmers ever, I am so grateful, in my old age I am progressing a lot thanks to you guys and your wisdom, thanks for not being selfish.