VBA routine that would organise my messy table (1 Viewer)

dim db as database
dim rs as recordset
dim a as long
dim s as string
dim position as long

set db=currentdb
set rs=db.open("MyTable")
' --- insert here the standard check if rs is opened ---
...
' ---
rs.Movelast
rs.movefirst
for a=1 to rs.recordcount
s= rs!YourField
rs.edit
position=instrrev(s," ")
rs!surname=left$(s,position-1)
rs!first_name=right$(s,len(s)-position)
rs.update
rs.movenext
next
rs.close
'  etc... close all

Public aStrOut(3) As String

Function FunGetNames(inputstr As String) As Variant
Dim re, aStrIn() As String
' clean up string
inputstr = Replace(inputstr, "(", "")
inputstr = Replace(inputstr, ")", "")
inputstr = Trim(inputstr)

' find if there is a reference and add to aStrOut and remove from name string
aStrIn = Split(inputstr, " ")
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[A-Z][A-Z]+\-[0-9][0-9]+"
re.IgnoreCase = False
If re.test(aStrIn(UBound(aStrIn))) Then
    aStrOut(2) = aStrIn(UBound(aStrIn))
    inputstr = Trim(Replace(inputstr, aStrIn(UBound(aStrIn)), ""))
Else
    aStrOut(2) = ""
End If
Set re = Nothing
' check for names
If Len(inputstr) = 0 Then ' no names found
    aStrOut(0) = ""
    aStrOut(1) = ""
Else
    aStrIn = Split(inputstr, " ")
    Select Case aStrIn(0) ' check for pre-fixes
        Case Is = "von", "xyz", "ABC" ' set up prefixes here
            inputstr = Replace(inputstr, " ", "*", 1, 1)
    End Select
    aStrIn = Split(inputstr, " ")
    Select Case UBound(aStrIn) + 1
    Case 1 ' last name only
        aStrOut(0) = Replace(aStrIn(0), "*", " ")
        aStrOut(1) = ""
    Case 2 ' both names
        aStrOut(0) = Replace(aStrIn(0), "*", " ")
        aStrOut(1) = aStrIn(1)
    Case Else ' give warning and show problem text
        MsgBox "Too many names in " & inputstr, vbCritical, "Error"
        aStrOut(0) = inputstr
        aStrOut(1) = ""
    End Select
End If
End Function

Sub test()
Call FunGetNames("Rico-Ruiz M")
Debug.Print aStrOut(0)
Debug.Print aStrOut(1)
Debug.Print aStrOut(2)
End Sub