473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

ListBox Sort Records - With out using Command Buttons

Hi,

I was wondering if it was possible to use the first line of a listbox which contrains the field names (Product Name, Unit Price ... etc) to run an on click event to change the sql of the list box in this case Order By. - I have seen some examples using command buttons which will work, just not as neatly as i was hoping for.
May 16 '16 #1
11 3191
PhilOfWalton
1,430 Expert 1GB
The answer is probably yes, but...
What about the sort order in the other columns. Suppose you want to sort on price descending. suppose you also have a number of products all at the same price. I presume you would want to keep the those product names in alphabetical order.

I would normally expect a list box's row source to end up with something like
Expand|Select|Wrap|Line Numbers
  1. ORDER BY UnitPrice DESC, DateDelivered, ProductName;
  2.  
What thoughts have you how to indicate the other sort fields?

Phil
May 16 '16 #2
jforbes
1,107 Expert 1GB
I've looked into doing this in the past and with Access' built-in ComboBox there isn't an Event attached to clicking on a Column Header.

The best solution that I came up with was to add additional ComboBox that would reorder the ComboBox.

...There are some controls out there from third-party controls manufacturers that do this, but I wouldn't recommend going this route as it will complicate your database considerably as deployment gets tricky and there is often a cost associated with third-party controls.


This code is an implementation of the Two Combobox approach where cboPipSort determines the sort order of four other comboboxes.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboPipeSort_AfterUpdate()
  2.  
  3.     Dim sSQL As String
  4.  
  5.     Select Case cboPipeSort.Value
  6.         Case "Inner Diameter"
  7.             sSQL = "SELECT LookupPipeSizes.Size, LookupPipeSizes.Inner, LookupPipeSizes.WorkingPressure FROM LookupPipeSizes ORDER BY LookupPipeSizes.Inner"
  8.         Case "Working Pressure"
  9.             sSQL = "SELECT LookupPipeSizes.Size, LookupPipeSizes.Inner, LookupPipeSizes.WorkingPressure FROM LookupPipeSizes ORDER BY LookupPipeSizes.WorkingPressure"
  10.         Case Else
  11.             sSQL = "SELECT LookupPipeSizes.Size, LookupPipeSizes.Inner, LookupPipeSizes.WorkingPressure FROM LookupPipeSizes ORDER BY LookupPipeSizes.Size"
  12.     End Select
  13.  
  14.     cboFlowPipe.RowSource = sSQL
  15.     cboFlowPipe2.RowSource = sSQL
  16.     cboFlowPipe3.RowSource = sSQL
  17.     cboFlowPipe4.RowSource = sSQL
  18.  
  19. End Sub
May 16 '16 #3
PhilOfWalton
1,430 Expert 1GB
Yes, I know there isn't an event connected with a column header, but I was guessing that with some pretty complicated stuff using MouseUp ? Down I might be able to work out relative to the list box corner, which column had been selected.
I wasn't prepared to look at that until I knew how the OP wanted to sort all the columns.

I suspect that your sort of approach is going to provide the satisfactory answer, but finding which column header had been pressed would be an interesting exercise.

Phil
May 16 '16 #4
Hi PhilOfWalton and jforbes,

Cheers for you fast replys and confirming there isn't an event connected with the column header. - I will have a play at alternative methods. - Thanks

Adam
May 17 '16 #5
zmbd
5,501 Expert Mod 4TB
@Phil:
I have seen code that does this out in the wild; however, the code is fairly deep.
Most of what I have seen is restricted to a fixed listbox (that is no horizontal scroll) usually are based on a query and use the right-click events, mostly because it's a real trick to determine where in the list box the mouse is located. THe column widths are then calculated, the total width calculated, etc...

It's out there; however, not really all that flexible.

@AssistAdam:
What I've done in continuous/single froms is to create the listbox without header then above the listbox place a series of Labels, TextBoxes, or Command buttons and in their on_click event build the code to sort the list by that "column." The code I use is pretty much along the same lines as J's... except I place this as a private sub in the form and from the afore mentioned controls "Call" the procedure passing the name of the control, Sub zSortListBox(inControlName as string) then use that same select-case construct. This way I write the sort procedure only once in the form :) Of course, this wont work on a datasheet view.
May 17 '16 #6
MikeTheBike
639 Expert 512MB
Hi

If all you want to do is sort just one column at time, then I though it shouldn't be to complicated, so I came up with just putting label above each column in the list box with this code
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim SQL_ListBox As String
  4.  
  5. Private Sub Form_Load()
  6.     'Module level variable
  7.     SQL_ListBox = "SELECT Table.ID, Table.EttCodeNum, Table.errCodeReason, Table.Name, Table.DNF FROM [Table] "
  8.  
  9.     Me.lstSort.RowSource = SQL_ListBox
  10.  
  11.     'Initialize lable Tags
  12.     Me.lbl_ID.Tag = " ORDER BY ID ASC "
  13.     Me.lbl_ErrNum.Tag = " ORDER BY EttCodeNum ASC "
  14.     Me.lbl_Error.Tag = " ORDER BY errCodeReason ASC "
  15.     Me.lbl_Name.Tag = " ORDER BY Name ASC "
  16.  
  17. End Sub
  18.  
  19. Private Sub lbl_Error_Click()
  20.     SortListBox Me.lbl_Error
  21. End Sub
  22.  
  23. Private Sub lbl_ID_Click()
  24.     SortListBox Me.lbl_ID
  25. End Sub
  26.  
  27. Private Sub lbl_ErrNum_Click()
  28.     SortListBox Me.lbl_ErrNum
  29. End Sub
  30.  
  31. Private Sub lbl_Name_Click()
  32.     SortListBox Me.lbl_Name
  33. End Sub
  34.  
  35. Sub SortListBox(ByRef oControl As Control)
  36.     Dim sql As String
  37.     Dim Criteria As String
  38.  
  39.     With oControl
  40.          Criteria = .Tag
  41.         If Right(.Tag, 4) = "DESC" Then
  42.             .Tag = Left(.Tag, Len(.Tag) - 4) & "ASC "
  43.         Else
  44.             .Tag = Left(.Tag, Len(.Tag) - 4) & "DESC"
  45.         End If
  46.     End With
  47.  
  48.     sql = SQL_ListBox & Criteria
  49.     Me.lstSort.RowSource = sql
  50.  
  51. End Sub
Of course I could be way of beam with this, but, as I am between 'challenges' at the moment, it seem interesting, although it isn't a million miles away from previous suggestions.

MTB
May 17 '16 #7
ADezii
8,834 Expert 8TB
A rather 'Rogue' approach would be to embed a ListView Control on your Form. The Column Headers would consist of the Field Names and, more importantly, the ListView Control has a ColumnClick() Event that can determine which Column was clicked and sort that Column appropriately. As jforbes has previously stated, this approach can complicate the DB.
May 17 '16 #8
jforbes
1,107 Expert 1GB
This is getting interesting. The activity on this thread made me think about it a little bit more... You could use a Subform in Datasheet View and make it act like a ListBox.

I created a Form as a Datasheet and named it PeopleDatasheet, then added this code to the People Form:
Expand|Select|Wrap|Line Numbers
  1. Private nParent As String
  2.  
  3. Public Sub setParent(ByRef sParent As String)
  4.     nParent = sParent
  5. End Sub
  6.  
  7. Private Sub Form_Current()
  8.     If CurrentProject.AllForms(nParent).isLoaded Then
  9.         Forms(nParent).PeopleSelected
  10.     End If
  11. End Sub
Then placed the PeopleDatasheet Form on another Form as a SubForm. Then added this code to the Parent Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Call Me.PeopleDatasheet.Form.setParent("PeopleWithSortableDropdown")
  3. End Sub
  4.  
  5. Public Sub PeopleSelected()
  6.     Dim sNewFullName As String
  7.     If Me.PeopleDatasheet.Form.CurrentRecord <> 0 Then
  8.         sNewFullName = Nz(Me.PeopleDatasheet.Form!People, "")
  9.         If sNewFullName <> Nz(Me.txtFullName.Value, "") Then
  10.             Me.txtFullName.Value = sNewFullName
  11.         End If
  12.     End If
  13. End Sub
So now there is a Datasheet that can be sorted at will that update the Textbox txtFullName when a row is selected. Maybe that will work for you.
May 17 '16 #9
PhilOfWalton
1,430 Expert 1GB
Out of sheer boredom, I thought this routine might be of interest.
Basically it allows you to click on the column header of a list box and do something like sort it

This is in the form header
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim OldSelectCount As Integer
  5.  
Then we have these 2 subs on the MouseDown & MouseUp
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyListBox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  2.  
  3.     OldSelectCount = MyListBox.ItemsSelected.Count
  4.  
  5. End Sub
  6.  
  7. Private Sub MyListBox_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  8.  
  9.     Dim ColWidths() As Long
  10.     Dim i As Integer, j As Integer, k As Integer
  11.     Dim Head As Boolean             ' We have hit the column head
  12.  
  13.     ' Get the cumulative column widths of the list box into an array
  14.     For i = 1 To MyListBox.ColumnCount
  15.         j = InStr(j + 1, MyListBox.ColumnWidths, ";")
  16.         If j > 0 Then
  17.             ReDim Preserve ColWidths(i)
  18.             ColWidths(i) = Mid(MyListBox.ColumnWidths, k + 1, j - k - 1) + ColWidths(i - 1)
  19.             k = j
  20.         End If
  21.     Next i
  22.     i = i - 1
  23.     ReDim Preserve ColWidths(i)
  24.     ColWidths(i) = right(MyListBox.ColumnWidths, Len(MyListBox.ColumnWidths) - k) + ColWidths(i - 1)
  25.  
  26.     ' If we have done a mousedown and mouseup and the ItemsSelected.Count hasn't changed then we have hit the header
  27.     If OldSelectCount = MyListBox.ItemsSelected.Count Then
  28.         'MsgBox "Blank"
  29.         Head = True
  30.     Else
  31.        ' MsgBox "Item"
  32.         Head = False
  33.     End If
  34.  
  35.     ' We've hit the header so how far along
  36.     If Head = True Then
  37.         For i = 0 To MyListBox.ColumnCount - 1
  38.             If X >= ColWidths(i) And X < ColWidths(i + 1) Then
  39.                 Debug.Print "Column" & CStr(i + 1) & " Selected"
  40.                 '    #### Do something here like sort the column ####
  41.             End If
  42.         Next i
  43.     End If
  44.  
  45. End Sub
  46.  
  47.  
Phil
May 18 '16 #10
zmbd
5,501 Expert Mod 4TB
very nice, along the same lines as the code I mentioned earlier.

I haven't tried your code and wont have the time to do so; however, I am curious, have you avoided the horizontal scroll-bar issue. From what I've remembered when I tried to do this (and I do have code; however, it's very messy) the Mouse-X is in twerps for the visible columns and when you scroll over (right/left) these columns are not accounted....

For example, say the Mouse-X=6480twips within the list box, that may be fine for the displayed columns; however, say one scrolls over to the 6th column that wasn't displayed, the X will still point to the 5th (1440twips to an inch, let the columns be 1inch width, total column width 8inches, listbox width 7200twips==5inches) column position on the unscrolled columns
May 20 '16 #11
PhilOfWalton
1,430 Expert 1GB
Good point, z. As you surmised it only works if the columns are visible without using the horizontal scroll bar.

I haven't the foggiest idea how to work out how far the scrollbar has been "scrolled"

Phil
May 20 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Lad | last post by:
What is the best( easiest)way how to sort a file? I have a file where each record consists of 3 fields( 3 words) and I would like to sort records by the first field( word)in each record. Any idea?...
2
by: Chris Bolus | last post by:
I'm a teacher using MS Access on an RMConnect 2.4 network. On some workstations both I and my students sometimes get an error message when attempting to insert a command button on a form which...
12
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course,...
1
by: bjbounce2002 | last post by:
Hello, I am using forms with command buttons to close form or run action queries. The error messages such as "Null value in required field" or "duplicate value in primary key" are suppressed....
3
by: BVH | last post by:
Hi, I'm currently having a problem with a vb6 project I once wrote that needs to be converted to vb.NET. The problem is as follows : On a form I have a listbox and two commandbuttons. The 2...
3
by: atiq | last post by:
I have got serveral forms where i want to use the same command buttons for each form to navigate through records. i was wondering if there is a way of achieving this without having to create all of...
2
by: Randy | last post by:
I have two listboxes on a form. The first box displays categories while the second box displays the items belonging to the category selected in the first box. Thus, the second box is essentially...
3
by: jervin32189 | last post by:
Hello, I am interested in designing a program that is optimized for touch-screen systems. But I don't know how to code a command button to enter text. This is necessary for like login screens...
1
by: Kevin | last post by:
I'm using a series of command buttons A - Z to filter a list of contacts. I'd like to change the functionality so that by pressing one of the buttons, access will jump to the first entry beginning...
5
by: bluethunder | last post by:
Good day! I'm having a problem with my program with the use of datagrid. Here's the scenario after adding a new record I want the data from my database will be also display in datagrid. Is there...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.