Listview / Parse Question

[Rev. Michael L. Burns]

I am working on a program where the customer wishes that the customer name be entered as it would be seen on a shipping label, envelope, invoice, etc. For instance, Rev. Michael L. Burns, Albion Seventh Day Baptist Church, Billy Jones, etc. No problem here.

However, on one form I need a Listview control that allows me to select a particular customer from a list of customers in a database table which stores the names as entered above. Again, this works fine except I would like to parse the data as it is read from the table and put into the listview control so as to display as follows: Burns, Rev. Michael L.; Jones, Billy; Church, Albion Seventh Day.

Can anyone show me how this can be done. Below is the code I use to load the listview at present:

Private Sub DoList()
Set LCusRS = New ADODB.Recordset
LCusRS.Open "SELECT * FROM mstCust", db, adOpenStatic, adLockOptimistic

If Not LCusRS.BOF Then LCusRS.MoveFirst
ListView1.ListItems.Clear
Do While Not LCusRS.EOF
ListView1.ListItems.Add , , LCusRS!Name '//Add names to list
LCusRS.MoveNext
Loop
If Not LCusRS.EOF Then LCusRS.MoveFirst
ListView1.Refresh
End Sub

Any help here would be most appreciated.

Rev. Michael L. Burns

[YoungBuck]

I'm not 100% sure what you are looking to do, but I think you want to display the data in the ListView in columns. You need to use the SubItems property of the ListView Control if that is what you are trying to do. Something like this should get you started, of course you will have to append the code to match your database...

Private Sub Form_Load()

    ListView1.View = lvwReport
    
    With ListView1.ColumnHeaders
        .Add , , "Name"
        .Add , , "Address"
        'etc
    End With
    
End Sub

Private Sub DoList() 
Set LCusRS = New ADODB.Recordset 
LCusRS.Open "SELECT * FROM mstCust", db, adOpenStatic, adLockOptimistic 

If Not LCusRS.BOF Then LCusRS.MoveFirst 
ListView1.ListItems.Clear 
Do While Not LCusRS.EOF 
With ListView1.ListItems.Add(, , LCusRS!Name) '//Add names to list
.SubItems(1) = LCusRS!Address
.SubItems(2) = LCusRS!ShipTo
'etc
End With
LCusRS.MoveNext 
Loop 
If Not LCusRS.EOF Then LCusRS.MoveFirst 
ListView1.Refresh 
End Sub

[Rev. Michael L. Burns]

YoungBuck,

Thanks ever so much for your speedy supply. Let me see if I can be a little clearer as to my needs.

I have a field in the mstCust Table called "Name" that contains date in it in the following manner :

Rev. Michael L. Burns
Albion Seventh Day Baptist Church
Billy Jones
Sharon Jill Burns
Calvin J. Hobbs

(The above reflects the contents of the "Name" field for five records). In most places throughout the program when I retrieve the field in this manner, it is exactly what I want and formatted the way I desire.

On one form I have a ListView control with a single column that currently displays the five records in the order show above. I provided the code I currently use to populate the ListView in my earlier post. What I want to do, if possible is to parse the data as it is read from the field in the table and before it is placed into the ListView so that when I do load the ListView it will display the same five records for instance in the following manner:

Burns, Rev. Michael L.
Church, Albion Seventh Day
Jones, Billy
Burns, Jill
Hobbs, Calvin J.

This would still be displayed in a single column in the Listview. Don't know if that helps or just muddied the waters a little more.

Rev. Michael L. Burns

[YoungBuck]

Sorry about the earlier post I was confused as to what you are looking for, here is a little function I put together which seems to work well.

Private Sub DoList() 
Set LCusRS = New ADODB.Recordset 
LCusRS.Open "SELECT * FROM mstCust", db, adOpenStatic, adLockOptimistic 

If Not LCusRS.BOF Then LCusRS.MoveFirst 
ListView1.ListItems.Clear 
Do While Not LCusRS.EOF 
ListView1.ListItems.Add(, , LastNameFirst(LCusRS!Name)) '//Add names to list
LCusRS.MoveNext 
Loop 
If Not LCusRS.EOF Then LCusRS.MoveFirst 
ListView1.Refresh 
End Sub 

Private Function LastNameFirst(str As String) As String
 Dim iSpace As Integer
 Dim sOut As String
 
 Do
        
    iSpace = InStr(iSpace + 1, str, " ") ' Get the position of the next space
    If Not iSpace = 0 Then sOut = Mid(str, iSpace, Len(str)) ' Get the next string inbetween spaces
        
 Loop Until iSpace = 0 ' time to exit when no spaces found
 
 If Not sOut = "" Then
 
    sOut = sOut & ", " & Left(str, Len(str) - Len(sOut)) ' put the last string in front of the rest of the strings
 
 Else 'if no spaces are found then return original string passed
 
    sOut = str
    
 End If
 
 LastNameFirst = Trim(sOut) ' return string
 
End Function

Good Luck!