Making capital each word in field

[megatronixs]

Hi all,

I have now a macro that corrects the word in a field to proper case when it is copy paste and all letters are upper case. This works for normal fields in the form, but not in the subform. Is ti possible to fix it so it also works in the subform?

Private Sub ClientCountry_Exit(Cancel As Integer)
    Dim i As Long
If (StrComp(ClientCountry, UCase(ClientCountry), vbBinaryCompare) * StrComp(ClientCountry, LCase(ClientCountry), vbBinaryCompare)) = 0 Then
    i = InStr(1, ClientCountry, " ")
    ClientCountry = UCase(Mid(ClientCountry, 1, 1)) & LCase(Mid(ClientCountry, 2))
    While i > 0
    ClientCountry = Left(ClientCountry, i) & UCase(Mid(ClientCountry, i + 1, 1)) & Mid(ClientCountry, i + 2)
    i = InStr(i + 1, ClientCountry, " ")
    Wend
End If
End Sub

Greetings.

 

[Roku]

Hi
The code you posted shows the Exit event for a specific field, but your question suggests you do the same thing for multiple fields?
The simplest way to achieve the conversion is to put the code in the subform's field event handlers in the same way as the parent form.

You could also iterate all field in the parent Form (say from its BeforeUpdate event) and convert all text fields there. When you encounter the SubForm field, use its Form property to address its fields in the same way (Me.SubFormControl.Form) (Where SubFormControl is the name of your subform).
Here's a way that works:

Private Sub ConvertTextFields(ByRef pForm As Object)
Dim ctl As Control
For Each ctl In pForm
  Select Case ctl.ControlType
  Case acTextBox
    If Not ctl = "" Then ctl = StrConv(ctl, vbProperCase)
  Case acSubform
    ConvertTextFields (ctl.Form)
  Case Else
    Rem ignore other control types
  End Select
Next
End Sub

Call this subroutine from the parent form BeforeUpdate event like this:

Private Sub Form_BeforeUpdate(pCancel As Integer)
ConvertTextFields Me
End Sub

Note I have used the StrConv function to convert the text case - I think this does what you need?
Cheers

 

[megatronixs]

Hi,

Thanks, I will try this and see if I actually can get everything on the right place.

Thanks for the help.