code problem 1

[MA04]

Hi all,

I have code on the on exit of a textbox called Name_input, which takes the inputted name getting rid of any middle characters or strings to leave first string and last string which is copied to another textbox called SAL input (short for salutation).

e.g.
Mr M D Donald
will result
Mr Donald

The problem i get is if the user decides to enter (in name_input) one string or one character for instance "occupier" i get an error if the SAL_INPUT textbox has no value in it (null). Another problem with the code is if there is an entry already in the SAL_INPUT textbox it will not overide it with the changes which i want it to do.

This is the code

Private Sub Name_input_Exit(Cancel As Integer)
 If Trim(Me!SAL_input) & "" = "" Then
  If Not IsNull([Name_input]) Then
    Me!SAL_input = Left([Name_input], _
    InStr(1, [Name_input], " ") - 1) & " " & _
    Right([Name_input], Len([Name_input]) - _
    MyInStrReverse([Name_input], " "))
  End If
 End If
End Sub

Also myinstrReverse is a module as access does not support it:

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

p.s. i don't want the SAL_input to be bound to the Name_input textbox but to leave it unbound.

Any help appreciated, thanks in advance,
M-.

 

[TheAceMan1]

How are ya MA04 . . . .

Try the following:

[blue]Private Sub Name_input_Exit(Cancel As Integer)
   
   [green]'Detect at least one space![/green]
   [purple][b]If InStr(1, [Name_input], " ") <> 0 Then[/b][/purple]
      If Trim(Me!SAL_input) & "" = "" Then
         Me!SAL_input = Left([Name_input], _
         InStr(1, [Name_input], " ") - 1) & " " & _
         Right([Name_input], Len([Name_input]) - _
         [purple][b]InStrRev[/b][/purple]([Name_input], " "))
      End If
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[BobStubbs]

Try this:

Private Sub Name_input_Exit(Cancel As Integer)

'------------------------------------------------
'- If there is nothing in Name_Input, just exit -
'------------------------------------------------
If IsNull(Name_Input) or Name_Input < " " then
    Exit Sub
End If

'------------------------------------------------
'- If Name_Input does not contain a space, just -
'- exit, or set SAL_Input to <ERROR> if required-
'------------------------------------------------
If Instr(Name_Input, " ") = 0 then
'    Exit Sub
'    SAL_Input = "<ERROR>"
End If

'------------------------------------------------
'- Name_Input contains at least one space.      -
'------------------------------------------------
    Me!SAL_input = Left([Name_input], _
    InStr([Name_input], " ") - 1) & " " & _
    Right([Name_input], Len([Name_input]) - _
    MyInStrReverse([Name_input], " "))

End Sub

In the centre section, remove the ' comment mark from either Exit Sub or SAL_Input = "<ERROR>" as required

I hope this idea is useful.

Bob Stubbs

 

[MA04]

Hi guys,

Thanks for the help, Bob's code was almost what i was after. The only thing i need from it is if a user enters one string say Occupier it will copy it over into SAL_input. At present it does not copy over if a single string is entered. Thanks in advance for any further help,

M-.

 

[MA04]

Thanks again for the help, I have resolved the problem.

M-.