sorting a listbox

[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!

 

[Jeanette Cunningham]

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

 

[fredg]

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!

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.

 

[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!

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.

Thanks Fred, this worked perfectly.