Not In List Name Checker

[AndrewDotto]

Hiya,

I've got a combo box that has the following code in the Not In List option

Private Sub Operative_NotInList()

Dim count As Integer
Dim I As Long
Dim First As String
Dim Last As String
Dim Tmp As String
Dim Name As String

Name = InputBox("Operative is not on file, Please enter Operatives Name:", "New Operative")
    
count = Len(Name)
I = 1

    Do
      If (Left(Name, I) = " ") Then
          First = Left(Name, I - 1)
          Last = Right(Name, I)
      Else
          I = I + 1
      End If
    Loop Until (I = Len(Name))

End Sub

When it runs, i get to the input box, then i just get an error message box from the form saying "THIS VALUE IS NOT IN THE LIST etc".

Limit List is equal to YES, however, the displayed column is not the displayed column, so i cant really change this. It wont let me see if that code above actually works because the limit list error box pops up before it appears to run.

Is there a way around this and can someone cast an eye over the code above to tell me if it should work, the idea is that it will take a string, input from the input box, search through it until it find the space character, then everything pre the space is the first name, and post the space is the last name.

Thanks for your help!

 

[vbaInet]

You're using quite a lot of Access/VBA reserved keywords like FIRST, LAST and NAME. Change those before we can forge ahead.

If you want to split the string just use Left(), Mid() and Instr()