Click here to Skip to main content
15,918,742 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to sort a list box items based on the selected item in my combo box.
I am using access vba database 2007 and below is my code

Private Sub QueryExecutives()
sqlString = "SELECT Executive Correspondence.Year, Executive Correspondence.OurDate, Executive Correspondence.LogID, Executive Correspondence.Subject, Executive Correspondence.Contact, Executive Correspondence.Director, Executive Correspondence.ADM, Executive Correspondence.DM, Executive Correspondence.Minister" _
      &"FROM Executive Correspondence ";
End Sub

On my combo box Sort click here is my code:

Private Sub cmbSortExecutives_Click()
Dim strSourceExecutives As String
Select Case cmbSortExecutives.ListIndex
    Case 0
        Call QueryExecutives
         orderString = "ORDER by Year Asc "
    Case 1
         Call QueryExecutives
         orderString = "ORDER by OurDate Asc "
    Case 2
         Call QueryExecutives
         orderString = "ORDER by LogID Asc "
    Case 3
         Call QueryExecutives
         orderString = "ORDER by Subject Asc "
    Case 4
         Call QueryExecutives
         orderString = "ORDER by Contact Asc "
    Case 5
         Call QueryExecutives
         orderString = "ORDER by Director Asc "
    Case 6
         Call QueryExecutives
         orderString = "ORDER by ADM Asc "
    Case 7
         Call QueryExecutives
         orderString = "ORDER by DM Asc "
    Case 8
         Call QueryExecutives
         orderString = "ORDER by Minister Asc "
End Select
strSourceExecutives = sqlString + orderString
Me.lstViewExecutive.RowSource = strSourceExecutives
Me.lstViewExecutive = Me.lstViewExecutive.ItemData(1)
End Sub

After selecting each item the list box is empty and I don't know what I am not doing fine

I declared orderString and sqlString in my module as Global variables
Please urgent help is needed and thanks in advance

Updated 8-Jan-14 7:12am
Maciej Los 8-Jan-14 12:14pm    
What kind of issue do you have?
Chukse 8-Jan-14 12:47pm    
The list box is not populated based on the selected item. It returns an empty list box
Richard MacCutchan 8-Jan-14 12:17pm    
Why have you tagged your question C++/CLI and Cloud?
Chukse 8-Jan-14 12:48pm    
Sorry about that it was a mistake
Richard MacCutchan 8-Jan-14 12:56pm    
So are you going to correct it?

1 solution

First of all, please read this: scope of variables[^].

You don't need any global variable and Select Case ... End Select statement. Your query-string is wrong!

Private Sub cmbSortExecutives_Click()
Dim sqlString As String, sSort AS String

sqlString = "SELECT [Year], [OurDate], [LogID], [Subject], [Contact], [Director], [ADM], [DM], [Minister]" & vbcr & _
"FROM [Executive Correspondence]" & vbcr 
sSort = "ORDER BY " & cmbSortExecutives.Value & " ASC;"

Me.lstViewExecutive.RowSource = sqlString
End Sub
Share this answer
Chukse 8-Jan-14 16:23pm    
Thanks so much for the code. The list box does not sort alphabetically but it does change when the selected value of the combo box is selected

I tried to add Me.lstViewExecutive.Requery still does not work.

The Row Source of cmbSortExecutives was manually typed in by me in the Edit List Items of the combo box in the design view as:

I don't know if I am right
The Row Source Type is Value List
Maciej Los 8-Jan-14 16:28pm    
Is it ListBox or is it ListView. It has matter!
Chukse 9-Jan-14 10:28am    
It is a ListBox
Maciej Los 9-Jan-14 11:20am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900