'Sorting' a string (1 Viewer)

[Matt Greatorex]

I'm using the following code to find the names of the various columns in an Access table and displaying those names in a listbox. It may not look like much but, slow as I am, it took me a while to figure out .

Dim li_Count As Integer
Dim li_Max As Integer
Dim str_Source As String

li_Max = CurrentDb.TableDefs("NBReferral").Fields.Count

li_Count = 1
Do While li_Count < li_Max
str_Source = str_Source & "; " & CurrentDb.TableDefs("NBreferral").Fields(li_Count).NAME
li_Count = li_Count + 1
Loop

If Left(str_Source, 2) = "; " Then
str_Source = Right(str_Source, Len(str_Source) - 2)
End If

Me![List5].RowSourceType = "Value List"
Me![List5].RowSource = str_Source
Me![List5].Requery

This works fine but I'd ideally like to find a way to display the results in alphabetical order, as opposed to just the way in which happen to fall within the table's structure.

Given that the values are currently all part of one string, sorting is proving a bit tricky. Does anyone have any idea if there is another way to obtain the column names which would allow for the sorting I need?

 

[Matt Greatorex]

Okay, I'm clearly using a year's worth of normal inspiration but I've found a way to do this. In case it's of any use to anyone else, my method was as follows

In brief, instead of creating a string and using that as the source for the listbox, I've populated a table with the fieldnames and selected from that to get the results sorted as I wanted.

The code now looks as shown below.

Dim li_Count As Integer
Dim li_Max As Integer
Dim str_Source As String

DoCmd.SetWarnings False

li_Max = CurrentDb.TableDefs("NBReferral").Fields.Count

DoCmd.RunSQL "DELETE FROM [ListBox_SortSequence];"

li_Count = 1
Do While li_Count < li_Max

str_Source =
"INSERT INTO [ListBox_SortSequence] (ColumnHeading) Values('" & _
CurrentDb.TableDefs("NBreferral").Fields(li_Count).NAME & "');"

DoCmd.RunSQL str_Source
li_Count = li_Count + 1

Loop

Me![List5].RowSourceType = "Table/Query"
str_Source = "SELECT [ListBox_SortSequence].[ColumnHeading] " & _
"FROM [ListBox_SortSequence] " & _
"ORDER BY [ListBox_SortSequence].ColumnHeading;"
Me![List5].RowSource = str_Source
Me![List5].Requery

DoCmd.SetWarnings True

Hope it helps someone.

 

[workmad3]

Hmm... I would suggest putting the strings into an array and using a bubble sort or something on them But if your way works, then stick with it until it doesn't