Strings, proper case, & stripping spaces 1

[judgehopkins]

This code below strips all leading and following spaces from a string and also “proper cases” that string.

I have two questions:
(1) How can I make this procedure work on all the text box controls in the form?
and
(2) How can I strip unwanted spaces from inside the string, e.g., changing “John son” to “Johnson”?

'Private Sub Form_Current_AfterUpdate()
Private Sub BuyerOwnerFirstName_AfterUpdate()
    Me.BuyerOwnerFirstName = Trim([BuyerOwnerFirstName])
'trims all spaces from entry into control
    Me.BuyerOwnerFirstName = StrConv([BuyerOwnerFirstName], vbProperCase)
'capitalizes first letter of entry
End Sub

Thanks!
judgehopkins@yahoo.com

 

[bsupport]

Judgehopkins,

See this link for enumerating through the all controls on a form,

http://www.mvps.org/access/forms/frm0004.htm.

You could remove all Case statements except the one for "actextbox". Then, under "Print out the array..." remove the first two "Debug.print..." lines completely, and replace the "Debug.print" line in the For loop with your own Me.Buyerowner..., or with the function found at this link,

http://www.mvps.org/access/strings/str0005.htm

which capitalizes the first character of every word or at this link

http://www.mvps.org/access/strings/str0008.htm

which capitalizes and corrects mixed case names.

Hope this helps. Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[judgehopkins]

Would this work?

In the after update event of every control put

Me.ControlName.Value=ProperNoSpace(Me.ControlName.Value)

Add this function to a module

Public Function ProperNospace(ByVal strString As String) As String
  strString = Replace(strString, " ", "", 1, -1, vbTextCompare)
  strString = StrConv(strString, vbProperCase)
  ProperNospace= strString
End Function

 

[judgehopkins]

No nulls are allowed and validation must occur after every entry so I use this:

Private Sub CustomerName_AfterUpdate()

    If Me.CustomerName.Value <> &quot;&quot; Then

    Me.CustomerName.Value = ProperNoSpace(Me.CustomerName.Value)

    End If

End Sub

Public Function ProperNoSpace(ByVal strString As String) As String

        strString = Replace(strString, " ", "", 1, -1, vbTextCompare)

        strString = StrConv(strString, vbProperCase)

        ProperNoSpace = strString

End Function