How to Sort A Listbox based on Item Selected in combobox Access VBA

Question

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

VB

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:

VB

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

Chukse

Posted 8-Jan-14 5:56am

Chukse

Updated 8-Jan-14 7:12am

Maciej Los

v4

Add a Solution

Comments

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?

Chukse 8-Jan-14 16:10pm

Yes it has been corrected

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2014-01-08T07:04:00

Solution 1

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!

VB

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

Posted 8-Jan-14 7:04am

Maciej Los

Comments

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:
Year
OurDate
LogID
Subject
Contact
Director
ADM
DM
Minister

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

Check this link: RowSource Property [Access 2003 VBA Language Reference][^]