Function question

[Malcy]

Hi
First can I say I haven't used functions before only subs so please forgive apparent ignorance!
I have set up Db to record (amongst other things) surnames. Data capture is from an unbound form and uses ADO to append record, once validated, to the table. Surnames go in txtSurname.
Given based in Scotland have a lot of Celtic type surnames which need to be converted properly since vbProperCase gets it wrong. I have found a function from SJ McAbney which seems to do this.

I have put it is a module called basCeltic. Is this right, or should I incorporate it into the code behind my data entry form (I use several different ones depending upon circumstances)
My questions are, how do I apply this function?
I presumably need to invoke it in the BeforeUpdate event of the textbox txtSurname???
If so, what code do I use to call it (except I read you don't call a function)?
Do I need to change the code in some way to let it know I need it to work on the contents of txtSurname?
Do I then change my ADO code to append the new record to update it to ??? instead of Me.txtSurname?

Sorry so many questions but it seems so close and yet so far!!
Thanks for any help.

 Function CorrectNames(ByVal strName As String) As String
' Code to correct Celtic surname capitalisation by SJ McAbney
    On Error GoTo Err_CorrectNames
    
    strName = StrConv(strName, vbProperCase)
    
    If InStr(1, strName, "Mc") Then _
        strName = Left(strName, InStr(1, strName, "Mc") - 1) & "Mc" & _
        StrConv(Mid(strName, InStr(1, strName, "Mc") + 2), vbProperCase)
        
    If InStr(1, strName, "Mac") Then _
        strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
        StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbProperCase)
        
    If InStr(1, strName, "'") Then
        If InStr(1, strName, "O'") Then
            strName = Left(strName, InStr(1, strName, "O'") - 1) & "O'" & _
                StrConv(Mid(strName, InStr(1, strName, "O'") + 2), vbProperCase)
        Else
            strName = Left(strName, InStr(1, strName, "'") - 2) & LCase(Mid(strName, InStr(1, strName, "'") - 1, 1)) & "`" & _
                StrConv(Mid(strName, InStr(1, strName, "'") + 1), vbProperCase)
        End If
    End If
        
    CorrectNames = strName

 

[FoFa]

Place the function in a module.
Using your example:
Dim NewName as string
NewName = CorrectNames(Me.txtSurname)

Now NewName should contain the value returned from the function

 

[Brian1960]

Functions

You need to set it as a Public Function NB you are missing the error handling code.
Set it in the Before Update code and then when you use the ado use the converted name.

StrTestName = Me.SURNAME
StrRedone = CorrectNames(StrTestName)
Me.SURNAME = StrRedone

Is the easiest way to do this.

 

[Rich]

Why are you using an Unbound form?

 

[Malcy]

Thanks Guys
I used Pat's suggestion asa first off try and put

 Me.txtSname = CorrectNames(Me.txtSname)

in the textbox AfterUpdate event and it worked an absolute treat.
WHen this stuff works it makes life look so easy, until you hit the next snag about five minutes down the line!