Initial Capital or Proper Case

[ygreenid]

The field that I want to capitalize is "LOCATION"; I also want a second text field "Name" to include initial caps for the first word & the second word. I have tried creating the following in the module and name it "uppdercasetext":
Public Sub UpperCaseText(ctr As Control)
Dim intLen As Integer
Dim x As Integer
Dim stString As String
DoCmd.RunCommand acCmdSaveRecord
stString = ctr.Text
intLen = Len(stString)
For x = 1 To intLen
Select Case x
Case 1
stString = UCase(Left(stString, 1)) & Mid(stString, 2)
Case Else
stString = UCase(Left(stString, 1)) & Mid(stString, 2)
If Mid(stString, x - 1, 1) = " " Then
stString = Left(stString, x - 1) & UCase(Mid(stString, x, 1)) &
Mid(stString, x + 1)
End If
End Select
Next x
ctr = stString

End Sub

I'm not sure how to add it to the "After Update".

Can someone explain the steps?

 

[Pat Hartman]

If you don't need to worry about special case words like O'Connell or McCarthy or IBM, then just use the StrConv() function with the ProperCase parameter. You want to put this code in the BeforeUpdate event of the Form NOT the AfterUpdate event. The BeforeUpdate event is the last event fired before the record is actually saved. The AfterUpdate event fires AFTER Access saves the record which is too late for your purpose. Update code here will put Access into a loop since it dirties the record which causes the Before and AfterUpdate events to fire again, which dirties the record,etc. Get the picture? Make sure that the control name is different from the column name for the datasource.

Me.LOCATION = StrConv(Me.txtLOCATION,vbProperCase)