Solved

Why does listbox contents disappear after trying to sort?

Posted on 2009-04-11
6
423 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:bmav25
6 Comments
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 100 total points
ID: 24122828
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
 

Author Comment

by:bmav25
ID: 24122856
Oh, sorry.  I edited that, but I am still getting the same result.
0
 
LVL 5

Accepted Solution

by:
Mike77 earned 300 total points
ID: 24122906
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Expert Comment

by:Mike77
ID: 24122913
I should have taken one of the 2 SELECTS lol ...
    strSQL = "SELECT int_ProductClassPlantID, str_ProductClass, int_PlantID, Discount1, Discount2 "
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 24122915
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
 

Author Comment

by:bmav25
ID: 24122946
Thanks Mike77.

I overlooked something very minor.  I appreciate your help.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 31
vba sql wild card passing in code 3 22
Access check if a table is open 4 39
My SQL as Backend for Access 3 8
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question