Using "SetUpper" function to deal with Names like Mc, Mac, O', etc.

  • Thread starter Thread starter BRC
  • Start date Start date
B

BRC

I found the code for this function on the i-net at:
http://msdn2.microsoft.com/en-us/library/aa662178(office.11).aspx.
The code was written to deal with names that start with "Mc or Mac
etc. it would present the user with options to select the correct case
(as i understand it.) I have copied the code and put it into my form
but I can't get it to run. If anyone has any experience with this
function I would like to hear about your experience. I have a client
file and I had the name fields updating with vbproper function but
this turns McKay in Mckay, so I am looking for an alternative to
vbproper. Any thoughts would be appreciated.
 
I found the code for this function on the i-net at:
http://msdn2.microsoft.com/en-us/library/aa662178(office.11).aspx.
The code was written to deal with names that start with "Mc or Mac
etc. it would present the user with options to select the correct case
(as i understand it.) I have copied the code and put it into my form
but I can't get it to run. If anyone has any experience with this
function I would like to hear about your experience. I have a client
file and I had the name fields updating with vbproper function but
this turns McKay in Mckay, so I am looking for an alternative to
vbproper. Any thoughts would be appreciated.
Click to expand...

After a brief look at this code, I noticed one thing here:

"Private Sub Form_BeforeUpdate(CancelInteger)"

(1) The parameter for this event procedure should read:
"Private Sub Form_BeforeUpdate(Cancel As Integer)"

(2) Be sure that your form has "[Event procedure]" set in the OnBeforeUpdate
event in the form's "Properties" page;

(3) In order for this procedure to work at all, your project must containa
reference to the DAO object library;

(4) Make sure that any other functions or subs called by this code are also
contained in your database, either as private functions or procedures contained
in your form's class module, or else as public functions or procedures inone of
your other code modules.

Other than that, there might be other issues which I didn't see because Inever
tried to use this code anywhere in my own stuff, so a "caveat emptor" is in
order here.
 
I've just had a play with that code, and found a few problems which prevent
it working if you do a straight cut/paste into your application.

In Function SetUpper, the Function declaration refers to varFixCaseVariant,
but in the function itself the value tested is varFixCase. You need to
change the function declaration to:
Function SetUpper(ByVal varFixCase) As Variant

Also in Function SetUpper, there is no assignment of the modified string to
the function to get it returned to the calling routine. You need to add the
statement:
SetUpper = strUpper
immediately before the End Function statement.

There are also some errors in the calling routine (seems like the original
code has lost some characters and line breaks). The following works:

Private Sub txtSurname_AfterUpdate()
Dim strUpper As String
' Get the corrected uppercase
strUpper = SetUpper(Me.txtSurname)
' See if it matches what the user entered
If StrComp(strUpper, Me.txtSurname, vbBinaryCompare) <> 0 Then
' Ask the user if this code should fix it
If vbYes = MsgBox("Would you like to correct the last name to: " & _
strUpper & "?", vbQuestion + vbYesNo, "Change?") Then
Me.txtSurname = strUpper
End If
End If
End Sub

Note that the name of the control ("txtSurname" in my example) to which this
AfterUpdate event code is attached is used three times within the routine
itself.

HTH,

Rob
 
I found the code for this function on the i-net at:
http://msdn2.microsoft.com/en-us/library/aa662178(office.11).aspx.
The code was written to deal with names that start with "Mc or Mac
etc. it would present the user with options to select the correct case
(as i understand it.) I have copied the code and put it into my form
but I can't get it to run. If anyone has any experience with this
function I would like to hear about your experience. I have a client
file and I had the name fields updating with vbproper function but
this turns McKay in Mckay, so I am looking for an alternative to
vbproper. Any thoughts would be appreciated.
Click to expand...

Just note that even if you get the code fixed, it's not going to be perfect.
"Machado" is a pretty common Spanish surname, and it does NOT mean members of
the Hado clan; I know people named Macdonald as well as MacDonald, and each
prefers their own style of capitalization.

You're probably safe with Mc names, but Mac names require someone's brain, and
probably often will also require asking the person what they prefer.

John W. Vinson [MVP]
 
Oops, forgot to mention:

The MsgBox line in the published code uses a global variable for its title
string; this will almost certainly give an error when yu cut/paste to your
own routine. That's fixed in my previous post.

And, for the record, I've submitted a feedback post to Microsoft describing
the problems with the published code.

Rob
 
Hi John,

The published code the OP refers to includes a message box requestin
confirmation of any change. That's probably fine for most people using this
in code on the AfterUpdate event of a form control (in the English
language); it would require a serious user-based overlook of, say, an update
query using this function. And, as you say, the algorithm itself will not
cope well with other language names - eg. dutch names with lowercase
space-separated prefixes such as "van". USe with caution, and modify the
code as required for your own purposes.

Rob

PS. I've also posted an expansion to my original reply, with another
problem I'd found (but omitted to mention), and a note that I'd also posted
the code problems to the MSDN website.
 
The published code the OP refers to includes a message box requestin
confirmation of any change.
Click to expand...

ah... thanks, Rob, and sorry about putting my spoke in without checking.

John W. Vinson [MVP]
 
Back
Top