Why does listbox contents disappear after trying to sort?

Hi,
I am working in Access 2007, and I am trying to order a listbox.  I followed an example I found online, but when I try the button used, the listbox contents disappear.

I am using a function to carry out the orderby functionality.

Here is my code:
Private Function lst_ProductClassDiscountsOrderBy(col As String) As Integer
Dim strSQL As String
 
    strSQL = "SELECT SELECT int_ProductClassPlantID, str_ProductClass, int_PlantID, Discount1, Discount2"
    strSQL = strSQL & "FROM tbl_ProductClassDiscounts "
    strSQL = strSQL & "ORDER BY " & col
    Me!lst_ProductClassDiscounts.RowSource = strSQL
    Me!lst_ProductClassDiscounts.Requery
 
End Function
 
 
Private Sub cmd_OrderByProductClass_Click()
 
Dim response As Integer
 
response = lst_ProductClassDiscountsOrderBy("str_ProductClass")
 
 
End Sub

Open in new window

bmav25Asked:
Who is Participating?
 
Mike77Connect With a Mentor Commented:
There is also a space missing at the end of Discount2 :

Change :
    strSQL = "SELECT SELECT int_ProductClassPlantID, str_ProductClass, int_PlantID, Discount1, Discount2"

For :
    strSQL = "SELECT SELECT int_ProductClassPlantID, str_ProductClass, int_PlantID, Discount1, Discount2 "

str_ProductClass is the name of your Field right?
0
 
Craig YellickConnect With a Mentor Database ArchitectCommented:
If that code is literally what you are running, there are two SELECT keywords resulting in an invalid query.

strSQL = "SELECT SELECT int_ProductClassPlantID ..."
0
 
bmav25Author Commented:
Oh, sorry.  I edited that, but I am still getting the same result.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Mike77Commented:
I should have taken one of the 2 SELECTS lol ...
    strSQL = "SELECT int_ProductClassPlantID, str_ProductClass, int_PlantID, Discount1, Discount2 "
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
As a side note, you do not need

Me!lst_ProductClassDiscounts.Requery

The list box will automatically be requeried when the Row Source is set.  This is redundant.

Make this change:

Private Function lst_ProductClassDiscountsOrderBy(col As Integer) As Integer
Dim strSQL As String

Pass the 'col' as a number ... and the number should be the number of the field - left to right - that you want to sort by.  A generic SQL that does this would look like:

SELECT Table1.FIELD1, Table1.FIELD2, Table1.FIELD3
FROM Table1
ORDER BY 2;

In this example, the sort would be by FIELD2.

mx


0
 
bmav25Author Commented:
Thanks Mike77.

I overlooked something very minor.  I appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.