Proper Case function

[access7]

Morning

Can anyone help me, I would like to make certain fields on my forms have the first letter of each word as upper case. U have used

Me.TextBox = StrConv(Me.TextBox,vbProperCase)

and it is working really well. However, rather than list all the controls that I would like to use this on, is there a way this will work with an array / For Each / Loop or something?

I am struggling with how to get the syntax correct if it is possible??

Thanks

 

[PeterF]

Put a string like ChangeProppercase in the tag of the controls you wish to change and use code to loop the controls like this.

        Set ctl = Me.Controls
        For Each F In ctl
            If F.Tag = "ChangeProppercase" Then
                 Me(F.Name) = StrConv(Me(F.Name), vbProperCase)
            End If
        Next

 

[NickHa]

How about this?

Dim ctl As Control
For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then ctl.Text = StrConv(Me.Text, vbProperCase)
Next

 

[access7]

Thanks, would this go on the forms load event or on the before / after update event??

 

[NickHa]

... oops! Should be:

Dim ctl As Control
For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then ctl.Text = StrConv([COLOR=red]ctl[/COLOR].Text, vbProperCase)
Next

 

[NickHa]

Put it in the Form_BeforeUpdate event.

 

[access7]

its telling me I cant reference a property or method for a control unless it has the focus

 

[NickHa]

Try this, then:

Dim ctl As Control
For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    ctl.SetFocus
    ctl.Text = StrConv(ctl.Text, vbProperCase)
  End If
Next

HTH!

 

[access7]

Thanks, I really appreciate the help, I am still quite new to this. I have used the code above, and put it on the before update event as suggested... it doesnt appear to be working for some reason.. I have put a break point in the code and it is not even going to that? Not sure why? This is what I have on that event. Is it something to do with the ctl.Text - do I need to change that to something else?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control

Me.ModifiedBy = SUser
Me.ModifiedWhen = Now()

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.SetFocus
ctl.Text = StrConv(ctl.Text, vbProperCase)
End If
Next

If CancelPressed = True Then
Me.Undo
End If

End Sub

 

[NickHa]

I have used the code above, and put it on the before update event as suggested... it doesnt appear to be working for some reason.. I have put a break point in the code and it is not even going to that? Not sure why? This is what I have on that event. Is it something to do with the ctl.Text - do I need to change that to something else?

No reason I can think of that the event is not firing. Do other events fire OK (if you have any?). If not, have a look at Access Options > Trust Center > Trust Center settings ... and check Enable all macros (and heed the warning!).
In your code, what do you expect 'If CancelPressed = True' to do? Normally, you would carry out some editing on your form fields and then set 'Cancel = True' if any fail validation. So the code would look more like:

If flgFailedEdit Then
  Me.Cancel = True
  Me.Undo
End If

Where flgFailedEdit is set in your validation checks.

 

[access7]

Thanks, I got the event to fire however it is now having problems in setting the focus to certain controls - possibly because they are disabled / invisible I'm not sure. I'm thinking now of rather than having a For / Next whether to have it check the previous control...
I have tried me.activecontrol.setfocus but that's not working. I'm just looking into how to reference / set focus to the previous control.
Thanks again for all the suggestions

 

[access7]

I now have this

Private Sub SetProperCase()
Dim ctl As Control

For Each ctl In Me.Controls
'set a property of the control based on the value
'saved in the "Tag" property of the control
Select Case ctl.Tag
Case "proper"
'if the "Tag" property of the control has a value of "proper" - change the text in field to Proper Case
If ctl.ControlType = acTextBox Then
ctl.SetFocus
ctl.Text = StrConv(ctl.Text, vbProperCase)
End If
End Select
Next

End Sub

which is working in terms of setting those fields to 'Proper Case' - however it appears to be stuck in the loop and wont let me enter any of the other fields... any clue??

 

[NickHa]

You can always add further checks to the control being referenced, to check its visibility and enabled states.
Determining the 'previous' control can be difficult - the form's tab order defines this, unless you do it by control name - which defeats the point of a generalised loop.
Don't forget the suggestion from PeterF about using the control tag attrbute - that might help your logic to determine the controls which are of specific interest.

 

[access7]

Thanks for the advice NickHa. I have followed the suggestion from PeterF, I already had something similar set up for both the visible and enabled properties so it is useful stuff. I just cant work out now why I am getting stuck on the controls and it wont let me enter another field.

 

[NickHa]

You need a Case Else catch-all for those controls which don't have the tag set and you can add the visibility and enabled checks to the if statement:

Private Sub SetProperCase()
Dim ctl As Control
For Each ctl In Me.Controls
  'set a property of the control based on the value
  'saved in the "Tag" property of the control
  Select Case ctl.Tag
  Case "proper"
    'if the "Tag" property of the control has a value of "proper" - change the text in field to Proper Case
    [COLOR=blue]If ctl.ControlType = acTextBox _[/COLOR]
[COLOR=blue]   And ctl.Visible _[/COLOR]
[COLOR=blue]   And ctl.Enabled Then[/COLOR]
      ctl.SetFocus
      ctl.Text = StrConv(ctl.Text, vbProperCase)
    End If
[COLOR=blue] Case Else[/COLOR]
  End Select
Next
End Sub

Other than that, I can't think of an obvious reason for it not to work. Anyone else have clues?
Is this subroutine in the form? It needs to be because of the reference to Me.Controls.

 

[access7]

Thanks! I have sorted it now, its working really well! Thank you for all your time and advice today