Strconv Function

[Paul Cooke]

Hi Guys could someone advise me if it is possible to string convert certain fields in a form when the field loses the focus?

I know how to do it for induvidual fields but wondered if it was possible to have 1 bit of code that will pickup the text fields on a whole form for example and exclude memo fields?

At the moment each relevant field has the follwoing event procedure in the lost focus box

MYFIELDNAME.Value = StrConv (MYFIELDNAME, vbProperCase)

Hope this makes sense !

Many thanks for any adivce offered

Paul

 

[DCrake]

You can but it seems a bit much like hard work. What is the naming conventions applied to the fields on the form. They may be alot or a little that need converting.

 

[Paul Cooke]

Hi David thanks for the reply.

I not 100% sure what you mean by naming conventions but I think it is the actual name of the field? If so I have just called them FirstName, Surname, OccupationType... ect ect. does this cause problems for me?

As to the Strconv main reason for my original question is that firstly I was worried to much code in a form / DB would cause problems or slow it down and secondly just thought it would be faster to enter 'one code does all' sort of thing !!

I am more than happy to just keep adding a new code for each field that I want converted if it is not going to screw things up later on.

As you may guess my DB knowledge is very basic and Im learnig as I go so i would welcome any advice you could give.

Many thanks

Paul

 

[DCrake]

The StrConv([Surname],3) is ideal for name sand addresses and is normally called on the Before/After Update event depending on bound/Unbound Controls. Conversley postcode fields can use UCase()

 

[Paul Cooke]

Many thanks David, thats pretty much what I am using it for although I am using it on the Lost Focus so it updates the "screen" as the users see's it.

I asssuming this is ok?

 

[DCrake]

If you are using a bound form the Lost focus is the right place if it is unbound then use the After update. However, it does not really matter. The only time you will hit the wall is when someone enter a name such as McGreggor or Davis Love III. when using vbProperCase

 

[Paul Cooke]

Many thanks again I am aware of ths issues of case changing names like Mc ect . I have found a far many suggestions on code to sort this but each is not wothout it's problems I think I will address this issue at a lter date but for now its just stayinng "simple" !!

Thanks again for your time

 

[smig]

yes, you can set the same fuction for all fields

create these sub and function

Private sub SetLostFocus()
 
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).Value = StrConv (CtlName, vbProperCase)
 
end Function

call the SetLostFocus from your Form_OnOpen event

 

[Paul Cooke]

Many thanks smig for the reply,

 

[DCrake]

Private sub SetLostFocus()

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).Value = StrConv (CtlName, vbProperCase)
 
end Function

In theory, yes this will work, practically it is cumbersome. By invoking the sub on the on load event of the form it is only replacing the contents of the fields with what they already look like now, no changes will be made to the appearance of the string.

If you placed this on the lost focus of the control that last receives the focus on the form you will only get one refresh.

If as stated earlier you want to convert a string as soon as the control looses the focus then you would have to call this on all the controls lost focus event. Again this would be wrong as it would run through every field on the form between one control loosing focus and the next getting the focus.

The most productive way to do this is as follows

On the LostFocus/AfterUpdate/BeforeUpdate of each control

Call ConvertToProper(Me.ActiveControl)

Private Sub ConvertToProper(Ctrl As String)
    Me(Ctrl) = StrConv(Me(Ctrl))
End sub

 

[Paul Cooke]

Many thanks David for the reply. I think I will stay with the basic strConv for now as in reality it is 1- working fine and 2-there are not that many fields that need converting at the present time.

but I will certainly refere back to this post in the coming weeks i'm sure !!

Thanks again


Paul

 

[smig]

Private sub SetLostFocus()

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).Value = StrConv (CtlName, vbProperCase)
 
end Function

In theory, yes this will work, practically it is cumbersome. By invoking the sub on the on load event of the form it is only replacing the contents of the fields with what they already look like now, no changes will be made to the appearance of the string.

hm... not exactly.
it's just replacing the need to write the same code for each TextBox .OnLostFocus event.

you can use the same trick to set the AfterUpdate/BeforeUpdate or any other event you wish.

for each Ctl in Me.Controls
  if Ctl.ControlType = acTextBox then
    Ctl.OnGotFocus = "=fnGotFocusFunction(" & Ctl.Name & ")"
    Ctl.OnLostFocus = "=fnLostFocusFunction(" & Ctl.Name & ")"
    Ctl.BeforeUpdate = "=fnBeforeUpdateFunction(" & Ctl.Name & ")"
    Ctl.AfterUpdate = "=fnAfterUpdateFunction(" & Ctl.Name & ")"
  end if
next Ctl

 

[Paul Cooke]

Thanks Smig

Just for confirmation where you have put 'ctl name' I'm asumming you mean the name of the field e.g. FirstName, Occupation ect...?

Paul

 

[smig]

it's Ctl.Name (don't miss the dot)
Ctl is the control as taken from the form controls collection.
Ctl.Name is the name of the control as was read from the control property

Me.Controls is the collection of all controls on form
for each Ctl in Me.Controls will go through all controls on the form and read their properties - ControlType, Name.......

put this code on your form's OnOpen event and see what happen:

dim ctl as Control
 
for each Ctl in Me.Controls
  MsgBox "ControlType - " & Ctl.ControlType & vbCrLf & "ControlName - " & Ctl.Name
next Ctl

 

[Paul Cooke]

Ha Ha - thank you so much for the explanation it's always good to learn and understanding things and trust me I have a lot to learn !

Thanks again

Paul

 

[smig]

glad I can help

 

[Xproterg]

This is a very useful piece of information thanks for sharing smig.

 

[DCrake]

Smig:

I don't think you get the point. Just because the control type is an acTextBox it does not guarrantee that the data being captured in that text box is text. It could be a date, number, Currency, amongst other things. So your method is performing unneccessary conversions that are succeptable to erroring.

 

[vbaInet]

Smig:

I don't think you get the point. Just because the control type is an acTextBox it does not guarrantee that the data being captured in that text box is text. It could be a date, number, Currency, amongst other things. So your method is performing unneccessary conversions that are succeptable to erroring.

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.

 

[smig]

David - sure, you are right. no question about this
I never talked about the function that will change the data in the field. I tooked it as it was originally posted by the OP.

I only wanted to show a way to set up the same event's code for all objects on the form.

Xproterg - I learned it here too