-
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
-
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...
Code:
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
-
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
-
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.
Code:
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! :)
