Trap txt with a space

[kimtp]

Am trying to error trap whenever a name is changed on a form by the addition of a space at the end or any place in the name. If I do a search on the name with the errant space, it is found using a filter. Therefore the boolean bFound is true.

However, during the next step, using a loop, the record can not be found. Have tried using the trim fct, no luck. First I do a search:

sSql = "SELECT * FROM Members WHERE Lastname = '" & sLast & "'AND" & _
" Firstname = '" & sFirst & "'"
rs.Open sSql, cData
rs.Filter = "Lastname = '" & sLast & "' AND Firstname = '" & sFirst & "'" 'LTrim(slast) does not work
If rs.EOF Then
bNameFound = False
Else
bNameFound = True
lngID = rs!ID
End If

Since bFound is true, next is the search on the name. Once the name is found, movenext to list the next record:

sSql = "Select * FROM Members ORDER BY Lastname, Firstname"
rs.CursorLocation = adUseClient
rs.Open sSql, cData
Do Until rs!LastName = sLast And rs!FirstName = sFirst
rs.MoveNext
Loop
rs.MoveNext
If rs.EOF Then
rs.MoveFirst
If rs.BOF Then
Exit Sub
End If
End If

Example, if there are spaces after the lastname, the loop will not find the record which causes an EOF/BOF error. It seems that the error trapping should come on the first procedure, but I am at a loss as how to.

Any help is appreciated.

Kim

 

[bboffin]

Why not remove all spaces from the names first then there is no possibility of an error? (This assumes that there are no spaces in the names on the database of course)

The Replace function does this quite neatly:

sFirst = Replace(sFirst," ","")

 

[sciophyte]

use the instr() function, you'll have to look it for all the parameters, basically if instr(sString, " ") = true then...

 

[kimtp]

Thanx for the responses. Yes, there are spaces in the lastname. And for the instr or instrrev, code can be written if the number of spaces included is known. Or that is my understanding of the functions instr/instrrev.

Since there appears to be many variations, seems that it would be better to trap and let the end user repair his/her search.

Any other ideas would be appreciated.

Kim

 

[hmckillop]

Why not use the keypress event and capture the space bar being pressed for the specific inputs.
i.e.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 32 Then KeyAscii = 0
    
End Sub

--this example is vba code from excel but essentially the same thing

"I'm living so far beyond my income that we may almost be said to be living apart

 

[sciophyte]

also you could make a temporary array and split the line into an array using the space as your delimeter

dim sArray() as string

...

sArray() = split(sString, " ", , vbtextcompare)

so if the string "dave jones " is in there it will split into this...

sArray(0) = "dave"
sArray(1) = "Jones"
sArray(2) = ""

loop through the array and ignore all the empty strings and you can parse out your words this way.