Listbox OrderBy

epicmove · Jun 20, 2006

Hi,
I have a list box based on a quite a complex query. I would like give the user the option to dynamically sort the list box.

How can I refer to my listbox's SQL rowsource and add a sort order using VB?
i.e.

Code:

Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL

I am aware of how I could do this if my SQL was in VB i.e.:

Code:

Dim strSQL as String
Dim strSortOrder as String
Dim optSort as Integer

Select Case optSort.Value
Case 1
strSortOrder = "ORDER BY LastName;"
Case 2
strSortOrder = "ORDER BY FirstName;"
End Select

strSQL = "SELECT FirstName, LastName FROM TblName " & strSortOrder
Me.ListBox.Rowsource = strSQL

....but my query is far more complex than this and I dont want to have to keep calling the code each time I need to requery my form.

Thanks
BF

mhartman · Jun 20, 2006

Hello

Check out my attachment. I believe this is what your looking for. Open the form and follow label message.

Regards
Mark

epicmove · Jun 20, 2006

Impressed am I

Mark,

Nice work. Gets round me having to use option buttons to sort the fields.

I Understand the Select Case X Statement, but how does the following work?:

Code:

   If Y <= 705 Then
        If Right$(cboSales.RowSource, 5) = " Asc;" Then.....

Unfortunately this still doesnt get round my problem of having to store my SQL string within the code.

Do you know of anyway I can do this i.e. by just calling listbox.rowsource. I presume that:

Code:

Me.ListBox.RowSource = Me.ListBox.RowSource & "Order By FieldName;"

may not work because you are actually passing the following as the rowsource:

Code:

Select FirstName, LastName FROM TblName; Order By FieldName;

(";" in twice)

Would really rather not have to enter my SQL into Code.
Thanks
Ben

mhartman · Jun 21, 2006

Hello:

First question answer: What the code does is look at the Sql statement's orderby entry. If it happens to be Ascending, then it makes it Desending.

Second question answer:
If you do not want to store the the Sql statements in code, you can make make a specific table called tblSql with two columns, one for SglId and the other just to hold the Sql statement. Then make a combo box control on your form to refer to that table and you then have a control with many custom ways of querying your data. You can use code to REFER to the combo box for the RowSource string. Hope this helps and good luck.
Regards
Mark

epicmove · Jun 21, 2006

Cracked it......thanks Mark!!

Ive cracked it. I found the problem was that I was being a plank

I mentioned that i didnt want to place my SQL in VB as it was too complex. This was the same reason why I didnt want to specify my SQL string in the rowsource of my listbox....so what did I do??... i created a query.

So if my list box is based on query "QryListBox" and I have a button to search by last name all I have to do write is:

Code:

Private Sub CmdSortLastName_Click
Dim strSQL as String
Dim strOrderBy as String
strOrderby = ".LastName;"
strSQL = "SELECT " & Me.ListBox.RowSource & ".* FROM " & Me.ListBox.RowSource & " ORDER BY " & Me.ListBox.RowSource & strOrderBy
Me.ListBox.Rowsource = strSQL

so what I am basically passing as my SQL is:

Code:

SELECT QryListBox.* FROM QryListBox ORDER BY QryListBox.LastName;

When I asked what the code meant earlier I was referring to

Code:

Private Sub lstSales_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim strOrd As String, strSql As String
    If Button = 1 Then
        If Y <= 225 Then

I presume the Y <= 225 is to declare what is the column header. How did you work out the value/distance. Same question for the X Values.
And why do you need "If Button = 1" on the listbox??

Sorry for all the questions but I like to understand the code before I use it

Thanks for the help

mhartman · Jun 21, 2006

Hello:
Great. Glad you got it going. How I figured out the "area" for clicking was in MouseDown event. It declares an x and y variable. In each region I placed a line of code to invoke the message box and display the Y value for the extreme right edge of the column header and then repeated the same for the extreme left edge of the column header. This will give you the range, 255 to 600 so to speak for each column. I then repeated this for the two remaining column headers. Then I took out the message box code. Please advise if I have not made myself clear.
Regards
Mark

mhartman · Jun 21, 2006

Hello Again

To answer the rest of your question, The button one refers to the buttons on the mouse. I believe this is contingent on the settings on your computer. IE, some folks have a left handed mouse, or a mouse with three or more buttons on it. If you wanted to make the code universal. You would have to code for all buttons or make some arrangement for a specific button to be pressed.

Regards
Mark

epicmove · Jun 21, 2006

thanks for explaining and for your help.

if it means anything to you, youve made a significant contribution to the search functionality of my Asset Management System

Thanks again
Ben

mhartman · Jun 22, 2006

Hello:

Thank you very much for your kind remarks. Assisting others with Access gives me the opportunity make my understanding of Access all more greater.

Good luck with future projects.
Sincerely
Mark

CharlesWhiteman · Mar 23, 2007

Must be missing something, column problems after mousedown

Hi, I came across your thread as a potenital solution as I too have a listbox to sort. so i read the thread, downloaded the sample database.

The problem is is that when I click on any column headings the listbox seems to lose its columns so has to be reopened. Heres my code and a couple of before and after screenshots.

Private Sub LbCompSearch_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim strOrd As String, strSql As String
If Button = 1 Then
If Y <= 225 Then
If Right$(LbCompSearch.RowSource, 2) = " Asc;" Then
strOrd = " Desc;"
Else
strOrd = " Asc;"
End If
strSql = "SELECT CompanyName, City, NextCallDue FROM Tblcompanies ORDER BY "
Select Case X
'Determine point region
Case 0 To 2868 'Case 0 To 1395
strSql = strSql & "CompanyName " & strOrd
Case 2892 To 4308 '1396 To 2835
strSql = strSql & "City " & strOrd
Case Else
strSql = strSql & "NextCallDue " & strOrd
End Select
LbCompSearch.RowSource = strSql
LbCompSearch.Requery
End If
End If
End Sub

Luddite Lad · Jul 15, 2007

mhartman said:
Hello:
Great. Glad you got it going. How I figured out the "area" for clicking was in MouseDown event. It declares an x and y variable. In each region I placed a line of code to invoke the message box and display the Y value for the extreme right edge of the column header and then repeated the same for the extreme left edge of the column header. This will give you the range, 255 to 600 so to speak for each column. I then repeated this for the two remaining column headers. Then I took out the message box code. Please advise if I have not made myself clear.
Regards
Mark

Mark

I have just discovered this thread and am just a little unsure of the coding to report the X and Y value in the message box, you describe above.

I'm presuming that with this missing piece of code in place you would click at the left and right margins of each column header and the message box would then report the appropriate X values. Then repeat the process at the foot of the column header for the Y value.

mhartman · Jul 23, 2007

Hello:

I just saw your message today July 23 2007. I will look up my example and post.

Regards
Mark

mhartman · Jul 23, 2007

Hello
Enclosed is an example of how to determine the X and Y values of the mouse pointer. I hope this explains it: Just two lines of code in the MouseDown event.

Regards
Mark

Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
'Determine the X, "Column" position of the mouse pointer.
txtXValue = X
'Determine the Y, "Row" position of the mouse pointer.
txtYValue = Y

End Sub

See the enclosed Db as well. Good luck.

Luddite Lad · Jul 23, 2007

Thanks Mark

Just what I was looking for

mhartman · Jul 24, 2007

Great! Also I glad I was able to respond to your message in a timely manner. I accidently stumbled on to it looking for something else.

Regards
Mark

brsawvel · Oct 12, 2007

Hello Epic,

I attempted your first code above on my listbox using a command button above the listbox, but when I click the command button, the listbox goes blank. Would you happen to know why?

brsawvel

Villarreal68 · Nov 3, 2007

Dr. Data,

Thank you for your post on this topic. I followed your instructions and it worked flawlessly. Thank you! This was a sore subject until I found your sample DBs. The requestor required exaclty what you demo'ed in your db samples. thanks again!
René

Villarreal68 · Nov 3, 2007

Hello brsawvel,

If what you need is to sort a list box, read the whole thread, but focus on the following posts:

http://www.access-programmers.co.uk/forums/showpost.php?p=499667&postcount=2 (Look at the MouseDown Event code)

http://www.access-programmers.co.uk/forums/showpost.php?p=611911&postcount=13 (Add code to your form and find out the X and Y points for your sorting needs)

I just used the samples in these two posts and I was able to make my listbox sortable.

Have fun!

René

Mgmt. · Dec 27, 2013

mhartman: this was a brilliant and simple solution. Thank you for posting this. Myself and my bosses thank you. I would have had a lot of headaches otherwise.

ram_rtg · Aug 28, 2015

It's amazing that after so many years this code still works really well even in Access 2013. A big thank you to @mhartman.

Listbox OrderBy

Ben

Dr. Data

Attachments

Ben

Dr. Data

Ben

Dr. Data

Dr. Data

Ben

Dr. Data

Registered User.

Attachments

Registered User.

Dr. Data

Dr. Data

Attachments

Registered User.

Dr. Data

Registered User.

Registered User.

Registered User.

New member

Registered User.

Similar threads

Users who are viewing this thread