mixed cases words help required!

[riccifs]

Hi to everyone in the Newsgroup,

I have a question that I really don't known how to solve.
To handle names with mixed cases, on my form, I'm using the code I
found at this web-link: http://www.mvps.org/access/strings/str0008.htm
Of course, it works great and as I read it should be modular so that
it possible to add additional rules to handle special sequences of
characters.

What I'd like to do is to add a piece of code, to be able to
transform the string ''spa'' in ''S.p.A.''
My trouble is that any time I have to write S.p.A. and I do that
hundred of
times, I have to remember to hit the point key after each letters and
at
same time I have to mind the different case of each characters .
It will be great if it doesn't matter how I to write ''spa'', the code
would change it in ''S.p.A.'' for me.
Looking the last part of the code there is a function to handle
roman numbers. So any time you write ''vii'' the code will change in
''VII'' for
you.
Is possible to do something like that for my sequences of words?
I'm not absolutely able to that, so I'm asking for help.
The words I'd like to be able to handle are: S.r.l. S.p.A. s.n.c.
s.a.s. S.r.l.u.
They are often (not always) located at the end of any strings

I hope you will be give to me an hand.....!
Thanks,
Stefano.

 

[Klatuu]

You can use the Before Update event of the control to do this. The only
drawback is whether the possibility exists that the entered text could
contain the string spa that should not be transformed, but you will have to
deal with that.

Private Sub txtSomeControl_BeforeUpdate(Cancel As Integer)

If Instr(Me.txtSomeControl, "spa") > 0 Then
Me.txtSomeControl = Replace(Me.txtSomeControl,"spa", "S.p.A.")
ElseIf Instr(Me.txtSomeControl, "srl") > 0 Then
Me.txtSomeControl = Replace(Me.txtSomeControl,"srl", "S.r.L.")
ElseIf Instr(Me.txtSomeControl, "snc") > 0 Then
Me.txtSomeControl = Replace(Me.txtSomeControl,"srl", "s.n.c.")
End If
End Sub

Add whatever other values you need to convert.

 

[Jeff Boyce]

A minor detail/typo, I suspect...

I think the third replace needs to replace "snc" rather than "srl". Drat
that Copy/Paste <g>!

Jeff Boyce

 

[riccifs]

A minor detail/typo, I suspect...

I think the third replace needs to replace "snc" rather than "srl". Drat
that Copy/Paste <g>!

Jeff Boyce

Hi Jeff,
many thanks to have answered me, but I'm still not understanding very
well what I have to do.
Could you give to me more instructions on how to do, please?

Bye,
Stefano.

P.S.
I'm not very good VBA code builder, so I need a step by step help!

 

[Jeff Boyce]

Stefano

What Dave suggested is to add an event procedure to the Before Update event
of the text control on your form in which the users will be entering the
codes. If you don't have any experience creating (and troubleshooting!)
VBA, it could be a bit of a learning curve.

Fortunately, you can copy and paste his code, making changes appropriate to
the names of your objects.

Potentially, another approach might be to create a table that hold two
fields. One field is the abbreviation, the other is the description. Then
you could use a combo box on your form to let the users select what they
need to use, rather than having to enter something (and have you, via code,
have to modify it).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP