Textbox that copies a name to another, minus middle initials or name 1

[MA04]

Hi all,

I have a textbox called SAL (short for Salutation) that copies the value of another textbox called Name, but displays it so that all middle initials or names are gone leaving title and lastname.

e.g.
Mr T Brown becomes Mr Brown
Mrs J L Andrews Becomes Mrs Andrews
Mr John Smith becomes Mr Smith.

This works but i have it so that the texbox SAL is bound but is it possible to keep it unbound. This is because i can get errors, for instance if the name entered in the Name textbox is one string there is an error in the SAL textbox; e.g. Occupier. Also something has to be entered into the textbox name to get something displayed in Salutation is it posssible to enter a seperate value for sal or Also because the Name textbox can be left blank sometimes allow an entry in SAL even if Name is not entered in Name textbox.

For instance i was thinking having some code on dbl click of the textbox, so its optional whether to copy and manipulate the value ot textbox name.

This is the value in the control source of my SAL textbox

=IIf(Not IsNull([Name_Input]),Left([Name_Input],InStr(1,[Name_Input]," ")-1) & " " & Right([Name_Input],Len([Name_Input])-MyInStrReverse([Name_Input]," ")),"")

Also I am using Access 97 which does not have MyInStrReverse function so i also got this module:

Function MyInStrReverse(Instring As String, Searchstring As String)

Dim i As Integer
Dim Char As String

i = Len(Trim(Instring))
Do While Char <> Searchstring
    Char = Mid(Trim(Instring), i, 1)
    i = i - 1
Loop
MyInStrReverse = i + 1
End Function

Any help very much appreciated, thanks in advance,

M-.

 

[Remou]

Hi
Have you tried making the SAL textbox unbound? From the above, it looks like you can have it unbound.

I usually find it easier to have four fields: Title (Mr, Mrs etc); First Name; Initials; Last Name. But I know you can get stuck with things the way they are.

 

[MA04]

I have tried placing the code, which is in the control source as the default value and other properties that would make SAL textbox unbound but does not seem to do anything also tried putting the code on the on exit part of my Name_input textbox. Could it be due to some property setting of the textbox?

M-.

 

[Remou]

Hi
How about putting the code in the OnEnter event of the SAL textbox? Something like:
If trim(Me!SAL) & "" = "" Then
If Not IsNull([Name_Input]) Then
Me!SAL = Left([Name_Input], _
InStr(1, [Name_Input], " ") - 1) & " " & _
Right([Name_Input], Len([Name_Input]) - _
myinstrreverse([Name_Input], " "))
End If
End If

 

[MA04]

Hi,

Thanks for your help Remou that works great,

M-.