parsing Problem

[Rev. Michael L. Burns]

Ihave a database that the customer requirs the names to be entered on the data entry screen like Rev. Michael L. Burns, Billy Carter, etc. These nameas are then parsed and written to the database as Burns, Rev. Michael L., Carter, Billy, etc. They are subsequently displayed in a listview in their parsed form. Works great.

When the user selects a name from the parsed listview the name is unparsed (is there such a name) and the record is shown in it's orignal form on the screen.

Everything works fine and unexpected until today. One of the names that was enter wrote to the database ok (stored as usually in its parsed form). However, when the name is clicked on in the Listview nothing happens. Any other name does the reverse parse as expected. I have found that it is because the name has an apostrophy "'" in it and this is causing the problem. Remove it an it works ok. For instance, Women's Society.

The code that I use reverse the parse is listed below. Can anyone tell me what I need to do to fix it?

Private Sub FromListUpdateRecord()
On Error GoTo ExitThis
If Not CusRS.BOF Then CusRS.MoveFirst
If Not lstCurrentCustomers.SelectedItem.Text = Empty Then
'// TCC Customer List is Not in Same Format As Data in Table
'// Therefore When Customer in List is Clicked it Must Be Parsed
'// Before Being Passed On To Fill Fields on the Form
Dim undostrname As String
Dim undotmp_name() As String
undostrname = Trim(lstCurrentCustomers.SelectedItem.Text)
undotmp_name = Split(undostrname, ",")
undostrname = undotmp_name(UBound(undotmp_name)) & " " & left(undostrname, Len(undostrname) - Len(undotmp_name(UBound(undotmp_name))))
undostrname = Replace(undostrname, ",", "") '// Get Rid of the ","
undostrname = LTrim$(undostrname) '// Get Rid of Leading Spaces
undostrname = RTrim$(undostrname) '// Get Rid of Trailing Spaces
'// End of Parsing Code

CusRS.Find "Name='" & undostrname & "'"
If Not CusRS.EOF Then
CusNumber = CusRS!CNum
Set invHRS = New ADODB.Recordset
invHRS.Open "SELECT SUM(Total - Paid) AS OB, CNum From Invheadder GROUP BY CNum HAVING (CNum = " & CusNumber & ")", db, adOpenStatic, adLockOptimistic
If Not invHRS.EOF Then
txtOutstandingBalance.Text = Format(invHRS!OB, "###,##0.00")
Else
txtOutstandingBalance.Text = "0.00"
End If
End If
End If
ExitThis:
End Sub

Any help here would be most appreciated.

Rev. Michael L. Burns

[jxiong]

Hi there, try this example:


"Update members set lastname=' " & Replace(txtInputName,"'","''") & " ' "

I got this from the searchVB.com which they provide
great VB tips and misc stuffs. You might want to
subscribe yourselft because it is free!

[Rev. Michael L. Burns]

jxiong,

Thanks ever so much for your reply. It put me on tract. With your tip I changed my code as follows:

From:

CusRS.Find "Name='" & undostrname & "'"

To:

CusRS.Find "Name='" & Replace(undostrname, "'", "''") & "'"


All now works fine.

Thanks Again,
Rev. Michael L. Burns