Strconv Function

[Paul Cooke]

Not only that but it should also be pointed out that running that code in the Lost Focus and/or Got Focus event(s) of the control is redundant and unnecessary. The OP should perform an UPDATE to the records that need converting and ONLY apply this code in the Before Update event of the control.

Also, the code assumes that ALL textboxes require the ProperCase applied to it. So the OP should be made aware of this. If you want to limit it to certain textboxes, maybe all textboxes in the Detail section, then it should be looping in that section only. If you want specific textboxes in different sections, then use the Tag property to identify the controls.

This is all really interetesting and helpful stuff ! Would mind explaining waht you have written in more basic terms as I am really new to Vb - thanks

 

[vbaInet]

Which part are you unsure about? The Update query or identifying the controls that require Propercase? Or both?

 

[Paul Cooke]

Thanks for the reply.. I will only need to convert text in the details section as the form (s) will all have buttons and form title's ect in the header and footer bits.

So If I am correct in understadning you I would put the code in the BeforeUpdate box on the Details Section?

As to what I am trying to convert there are various fields on the form

1.FirstName
2.Surname
3.Occupation
4.DateOfBirth
5.Emailaddress
6.TelephoneNumber
7.Postcode

So.. I would like to be able to convert fields 1,2,3 to Propercase
Field 5 to lower case and field 6 to Uppercase

As previousley written earlier in the thread I have already got the simple StnConv code on each of the relevant fields in the On lostfocus bit. but as the DB developes getting a dinfinitive answer to this poost may save me (and others) a lot of time.

Thanks again

 

[DCrake]

As previously mentioned you may need to bing into use the Tag Property of the fields that you want to convert.

For Example

email address ned to be in lower case

In the Tag Property you would enter 2 (code for lowercase)

Then in your loop you would test for something in the tag property of the control and if found use it

If Fld.Name.Tag <> "" then
  StrConv(Fld.Name,Fld.Name.Tag)
End If

Aircode for brevity only

 

[Paul Cooke]

sorry for being a right pain in the ... !!

I actually understand what you mean by putting 2 in the tag (so if I wanted it to be upper case i would put 3 in it instead?)

But I don't unserdstand what you mean by "In your loop"

Sorry ! I do really appriciate your patience and help

 

[DCrake]

dim ctl as Control
 
for each Ctl in Me.Controls
  if Ctl.ControlType = acTextBox then
    Ctl.OnLostFocus = "=fnLostFocusFunction(" & Ctl.Name & ")"
  end if
next Ctl
 
end Sub
 
Private Function fnLostFocusFunction(CtlName as string)
 
me.Controls(CtlName) = StrConv (CtlName, [B]me.Controls(CtlName).Tag[/B])
 
end Function

 

[Paul Cooke]

Thank you and everyone else so much for your time. and what makes is better is that I actually understand what the code means now and can relate to it.

Paul

 

[vbaInet]

To be honest, you haven't got many controls for you to need a loop of the Controls collection. We mentioned using loops because we were under the assumption that you have many controls (based on smig's code). In each of those control's After Update event (not Before Update, sorry), apply the StrConv() there:

Private Sub [COLOR=Red][B]ControlName[/B][/COLOR]_AfterUpdate()
     Me.[COLOR=Red][B]ControlName[/B][/COLOR].Value = StrConv(Me.[COLOR=Red][B]ControlName[/B][/COLOR].Value, 3)
End Sub

No need for the loop.

For Uppercase, just use the Format property > and LowerCase use < in the Format property.

If you already have data that needs converting, perform an Update to those records.

 

[Paul Cooke]

Thanks again.. The DB is in the early stages of design (its actually a complete revamp of an old one) so whilst there are not that many fields at the moment as the design goes on this thread and the help you guys have given will come in very very handy.

Paul

 

[vbaInet]

Just for completeness I will alter the code to convey my thoughts on the use of the Tag property. Note, my other suggestion is still preferred for your current situation.

So let's say you have 20 controls but you want 18 of them to StrConv to propercase. In each of those 18 textboxes, you can put something like "proper" (without the quotes) in the Tag property and the code will look like this:

Private Sub Form_Load()

    dim ctl as Control

    for each ctl in Me[B][COLOR=Red].Detail[/COLOR][/B].Controls
        if ctl.ControlType = acTextBox then
            Select Case ctl[COLOR=Red][B].tag[/B][/COLOR]
                Case "proper"
                    ctl.AfterUpdate = "=fnAfterUpdate(" &[B][COLOR=Red] Chr(34)[/COLOR][/B] & ctl.Name &[COLOR=Red] [B]Chr(34)[/B][/COLOR] & ")"
                Case Else
                    ' do nothing
            End Select
        end if
    next Ctl

end Sub


Private Function fnAfterUpdate(ctlName as string)
 
    me.Controls(ctlName) = StrConv(ctlName, me.Controls(ctlName))
 
end Function

You can see that the Select Case allows you to add more conditions in the future if you wish.

Remember, you can set the Tag property in one go. Select the required controls whilst holding down the Ctrl key and just type in proper. It will apply to all selected controls.

 

[Paul Cooke]

Thank you very much

 

[vbaInet]

Glad we all could help!

 

[Xproterg]

During the design of your database, if you place a value of your choosing, such as "proper" in tag category of the property sheet of any textbox (or other control), you'll be able to use the code above to go through each control to find which ones have the word "proper" in the tag category. The code will then change the case of all of the controls that it finds that meet that criteria.

Excellent add btw... the tag puts the icing on the cake.

 

[smig]

...In each of those control's After Update event (not Before Update, sorry)...

no need to sorry.
as said before, I only wanted to show a way of looping through controls to set the same function, for the events.
I didn't even had a look what he realy do in this function LOL

if you use the Tag there is no need to check for the ControlType

@Paul - The detail addition here: Me.Detail.Controls is to save resources and to check for the controls in the detail section of the form only.
I guess you can already see the options here

 

[vbaInet]

if you use the Tag there is no need to check for the ControlType

There is still a need to check the ControlType in this case. The OP is setting the values for textboxes. If the OP mistakenly put proper in a label control then the code will bomb.

@Paul - The detail addition here: Me.Detail.Controls is to save resources and to check for the controls in the detail section of the form only.
I guess you can already see the options here

It doesn't save resources pe se. It reduces the cycle (or loop) time.

 

[Paul Cooke]

I guess you can already see the options here

I don't think I will be building databases as a profession just yet !! but I am learning at lot and that is thanks to the people like yourself on here - it's great to share knowledge ! hopefully oneday I can help another newbie out - you never know !!!

Thanks all again I'm off to see The Kings Speech now so will carry on tomorrow

 

[vbaInet]

Thanks all again I'm off to see The Kings Speech now so will carry on tomorrow

I heard it's got rave reviews. Everyone I know who's watched it have loved it. I will go at see it at some point.

Enjoy!