Sort Listbox using Option Group 1

[LevelThought]

Currently using the following code to sort a listbox on a Access form;

Dim strSQL as String

strSQL = "SELECT Company, [First Name], [Last Name] FROM YourTable"

Select Case Me.[YourOptionGroup].Value
Case 1
strSQL = strSQL & " ORDER BY Company"
Case 2
strSQL = strSQL & " ORDER BY [First Name]"
Case 3
strSQL = strSQL & " ORDER BY [Last Name]"
Case Else
MsgBox "What to do with " & Me.[YourOptionGroup].Value
End Select

Me.[YourListBox].RowSource = strSQL & ";"


The issue that I am having is that the sort works ok if I only specify four columns of data in my listbox. If I display more than four columns of data, then, upon sorting using the option buttons, all of the columns other than the four sorted columns are blank!

Any insight as to how I would resolve this?

Thanks in advance.

 

[PHV]

What about something like this ?
Dim strSQL as String
strSQL = "SELECT your field list here FROM YourTable"
If Me![YourOptionGroup].Value > 0 Then
strSQL = strSQL & " ORDER BY " & Me![YourOptionGroup].Value
Else
MsgBox "What to do with " & Me![YourOptionGroup].Value
End If
Me![YourListBox].RowSource = strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

The code now "blanks" all of the rows!