sorting a listbox

  • Thread starter Thread starter jseger22
  • Start date Start date
J

jseger22

Hi,

I have a listbox on a form dependant on a query called Projects. The
listbox contains 6 columns and I have put 6 labels about each column
which describes it and there is no set number of rows that will be
displayed in the listbox. What I want to do is be able to sort the
columns whenever the user clicks on the label name. I tried doing
this with the macro builder but that didn't seem to work and I am only
a beginner with VBA so I don't even know where to start. Thanks!
 
Hi,
would like to post the SQL string for the query Projects and the names of
the 6 labels, then we could do some code for you.

Jeanette Cunningham
 
Hi,

I have a listbox on a form dependant on a query called Projects. The
listbox contains 6 columns and I have put 6 labels about each column
which describes it and there is no set number of rows that will be
displayed in the listbox. What I want to do is be able to sort the
columns whenever the user clicks on the label name. I tried doing
this with the macro builder but that didn't seem to work and I am only
a beginner with VBA so I don't even know where to start. Thanks!
Click to expand...

What is the rowsource of the List Box?
If it is a SQL query then you can do something like this.
Let's assume the current rowsource SQL is like this:

SELECT YourTable.[LastName], YourTable.[FirstName],
YourTable.[DateField] FROM YourTable ORDER BY YourTable.[LastName];

Change the above SQL to whatever yours actually is.

My example has just 3 labels, with the captions of "LastName",
"FirstName", and "Date". Just do the same for your fourth label.

Code the Click event of the LastName label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[LastName];"

Code the click event of the FirstName label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[FirstName];"

Code the Click event of the Date label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[DateField];"

Clicking on any of the labels will re-sort the list box.
 
Hi,
Click to expand...
I have a listbox on a form dependant on a query called Projects.  The
listbox contains 6 columns and I have put 6 labels about each column
which describes it and there is no set number of rows that will be
displayed in the listbox.  What I want to do is be able to sort the
columns whenever the user clicks on the label name.  I tried doing
this with the macro builder but that didn't seem to work and I am only
a beginner with VBA so I don't even know where to start. Thanks!
Click to expand...

What is the rowsource of the List Box?
If it is a SQL query then you can do something like this.
Let's assume the current rowsource SQL is like this:

SELECT YourTable.[LastName], YourTable.[FirstName],
YourTable.[DateField] FROM YourTable ORDER BY YourTable.[LastName];

Change the above SQL to whatever yours actually is.

My example has just 3 labels, with the captions of "LastName",
"FirstName", and "Date". Just do the same for your fourth label.

Code the Click event of the LastName label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[LastName];"

Code the click event of the FirstName label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[FirstName];"

Code the Click event of the Date label:
Me![ListBoxName].Rowsource = "SELECT YourTable.[LastName],
YourTable.[FirstName], YourTable.[DateField] FROM YourTable ORDER BY
YourTable.[DateField];"

Clicking on any of the labels will re-sort the list box.
Click to expand...

Thanks Fred, this worked perfectly.
 
Back
Top