Results 1 to 13 of 13
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Sort a list box by on click event on label?

    Hi all,


    I am trying to figure out how to do a sort on a list box from an on click event on a label.

    What I am looking for is to click on a label, then it will sort the listbox DESC or ASC
    I have three labels,
    lblBusinessNameSort
    lblIndustrySort
    lblCategorySort

    Code:
    ‘List box is “LstBusinessSearch”
    Rowsource = SELECT DISTINCT BusinessID, BusinessName, CategoryName, SubCategoryName, CategoryID, SubCategoryID FROM qryBusinessSearch;
    When I Click on LblBusinessNameSort, Rowsource BusinessName, then I want it to sort by BusinessName DESC or click again to make it ASC
    Same with LblIndustrySort would = rowsource CategoryName
    Same with LblCategorySort would = Rowsource SubCategoryName

    If DESC then the lbl color would be = RGB(100, 255, 255)
    If ASC then the lbl color would be = RGB(255, 100, 100)

    Code:
    Private Sub ResetSortLblColor()
    ‘And it would reset to  this when clicking a different label
        lblBusinessNameSort.ForeColor = RGB(217, 240, 255)
        lblIndustrySort.ForeColor = RGB(217, 240, 255)
        lblCategorySort.ForeColor = RGB(217, 240, 255)
    End Sub
     
    ‘Something Like this?
    Private Sub lblBusinessNameSort_Click() ‘ Same for Industry and category sort
     
    ResetSortLblColor
     
    If  (Me.LstBusinessSearch.[BusinessName]) Then
                    Me.LstBusinessSearch.[BusinessName],DESC
                                    Me.LblBusinessNameSort.ForeColor = RGB(100, 255, 255)
     
                    Else
     
                    If  (Me.LstBusinessSearch.[BusinessName]) Then
                                    Me.LstBusinessSearch.[BusinessName],ASC
                                    Me.LblBusinessNameSort.ForeColor = RGB(255, 100, 100)
                    End If
                                    Me.LstBusinessSearch.Requery
    End Sub
    Just not sure how to write this and If any dec's are necessary or Order by? Could you give me some assistance plz
    Thanks
    Dave

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So just add Order BY to the rowsource?
    Either store the direction, or look for "ASC" or "DESC" in that rowsource and switch?

    So perhaps default to ASC, then if ASC is found, switch to DESC, if not found, switch to ASC
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Good afternoon Welshgasman,
    Thanks and I am looking to sort by Business Name, CategoryName, and SubCategoryName via Label. Not to just sort the rowsource.
    Maybe i dont understand what you are saying.
    dave

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us the form in design view with the listbox's row source?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sure Can,
    Here ya go
    Copy of row source and the form itself
    Thanks
    Dave

    Click image for larger version. 

Name:	Row Source.jpg 
Views:	23 
Size:	144.0 KB 
ID:	48734

    Click image for larger version. 

Name:	Form.jpg 
Views:	23 
Size:	232.8 KB 
ID:	48735
    Attached Thumbnails Attached Thumbnails LstBox Rowsource.jpg  

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by d9pierce1 View Post
    Good afternoon Welshgasman,
    Thanks and I am looking to sort by Business Name, CategoryName, and SubCategoryName via Label. Not to just sort the rowsource.
    Maybe i dont understand what you are saying.
    dave
    Sorry, I thought those were three different listboxes , however the same logic should work?
    So keep tha base sql string in a variable and add ORDER BY yourfieldname and either ASC or DESC?

    I would show all as ASC as a default initially, then as I mentioned before, this time the relevant label adds the ORDER BY fieldname ?, so perhaps storing which direction is being used would be easier.?
    Inspect the current rowsource to determine direction otherwise.

    So if I am on DESC on business and I click on Category, is that menat to stay DESC or change to ASC?
    You will need to work out what order each affects the other?

    Alternatively add a up or down arrow to the label to show direction and then check for that or the colour you last set it at?

    Need the eyes of a hawk to read that last image, or a very large monitor .
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As mentioned in the previous post you need to decide if you want to sort by all three fields or by just one at the time (my guess is the later). The easiest would be to reset the listbox's row source based on the current color of the label:
    Code:
    Private Sub lblBusinessNameSort_Click() ‘ Same for Industry and category sort
    
    If Me.LblBusinessNameSort.ForeColor = RGB(100, 255, 255) then 'this means it is already sorted DESC
        Me.LstBusinessSearch.RowSource="SELECT .....FROM qryBusinessSearch... ORDER BY qryBusinessSearch.BusinessName"
        Me.LblBusinessNameSort.ForeColor = RGB(255, 100, 100)
    Else
        Me.LstBusinessSearch.RowSource="SELECT .....FROM qryBusinessSearch... ORDER BY qryBusinessSearch.BusinessName DESC"
        Me.LblBusinessNameSort.ForeColor = RGB(100, 255, 255)
    End If
    'reset the other two labels
    Me.lblCategorySort.ForeColor=RGB(255, 100, 100)
    Me.lblIndustrySort.ForeColor=RGB(255, 100, 100)
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Another option is using an option group
    Click image for larger version. 

Name:	a.png 
Views:	22 
Size:	3.2 KB 
ID:	48736Click image for larger version. 

Name:	b.png 
Views:	22 
Size:	3.2 KB 
ID:	48737
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Gicu
    I couldnt get that working but I did some searching and found some code from 2000 which when adapted works well and does what I was looking for.
    I posted it in case anyone else wants it. I am sure there is a way to just use a lable but I ended up using buttons as ran into a object not supported with lable.
    Also had t make 6 buttons and hide them.... but it has been tested and works well.
    Thanks to all, will mark solved in less someone has a better method and can educate me!

    Code:
    Private Function basOrderby(col As String, xorder As String) As Integer
    Dim strSQL As String
    'ClearCaptions
    'Set row source for list box
        strSQL = "SELECT DISTINCT BusinessID, BusinessName, CategoryName, SubCategoryName, CategoryID, SubCategoryID  "
        strSQL = strSQL & "FROM qryBusinessSearch "
        strSQL = strSQL & "ORDER BY " & col & " " & xorder
        Me!LstBusinessSearch.RowSource = strSQL
        Me!LstBusinessSearch.Requery
    
    
    End Function
    Private Sub ResetSortCmdColor()
    'Set reset color and capptions
        CmdBusinessNameSort.ForeColor = RGB(217, 240, 255)
        CmdBusinessNameSortDesc.ForeColor = RGB(217, 240, 255)
        CmdIndustrySort.ForeColor = RGB(217, 240, 255)
        CmdIndustrySortDesc.ForeColor = RGB(217, 240, 255)
        CmdCategorySort.ForeColor = RGB(217, 240, 255)
        CmdCategorySortDesc.ForeColor = RGB(217, 240, 255)
            
        Me!CmdBusinessNameSortDesc.Caption = "Business Name"
        Me!CmdBusinessNameSort.Caption = "Business Name"
        Me!CmdIndustrySortDesc.Caption = "Industry"
        Me!CmdIndustrySort.Caption = "Industry"
        Me!CmdCategorySortDesc.Caption = "Category"
        Me!CmdCategorySort.Caption = "Category"
    
    
    End Sub
    
    
    Private Sub CmdBusinessNameSort_Click()
    'Set BusinessName order in ascending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("BusinessName", "ASC")
        Me!CmdBusinessNameSortDesc.ForeColor = RGB(100, 255, 255) ' blue
        Me!CmdBusinessNameSortDesc.Visible = True
        Me!CmdBusinessNameSortDesc.Caption = "Business Name (ASC)"
        Me!CmdBusinessNameSortDesc.SetFocus
        Me!CmdBusinessNameSort.Visible = False
        Me!LstBusinessSearch.SetFocus
    End Sub
    
    
    Private Sub CmdBusinessNameSortDesc_Click()
    'Set BusinessName order in descending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("BusinessName", "DESC")
        Me!CmdBusinessNameSort.ForeColor = RGB(75, 255, 79) ' Green
        Me!CmdBusinessNameSort.Visible = True
        Me!CmdBusinessNameSort.Caption = "Business Name (DESC)"
        Me!CmdBusinessNameSort.SetFocus
        Me!CmdBusinessNameSortDesc.Visible = False
        Me!LstBusinessSearch.SetFocus
    End Sub
    
    
    Private Sub CmdCategorySort_Click()
    'Set Category order in ascending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("SubCategoryName", "ASC")
        Me!CmdCategorySortDesc.ForeColor = RGB(100, 255, 255) ' blue
        Me!CmdCategorySortDesc.Visible = True
        Me!CmdCategorySortDesc.Caption = "Category (ASC)"
        Me!CmdCategorySortDesc.SetFocus
        Me!CmdCategorySort.Visible = False
        Me!LstBusinessSearch.SetFocus
        
    End Sub
    
    
    Private Sub CmdCategorySortDesc_Click()
    'Set Category order in descending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("SubCategoryName", "DESC")
        Me!CmdCategorySort.ForeColor = RGB(75, 255, 79) ' Green
        Me!CmdCategorySort.Visible = True
        Me!CmdCategorySort.Caption = "Category (DESC)"
        Me!CmdCategorySort.SetFocus
        Me!CmdCategorySortDesc.Visible = False
        Me!LstBusinessSearch.SetFocus
    
    
    
    
    End Sub
    
    
    Private Sub CmdIndustrySort_Click()
    'Set Industry order in ascending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("CategoryName", "ASC")
        Me!CmdIndustrySortDesc.ForeColor = RGB(100, 255, 255) ' blue
        Me!CmdIndustrySortDesc.Visible = True
        Me!CmdIndustrySortDesc.Caption = "Industry (ASC)"
        Me!CmdIndustrySortDesc.SetFocus
        Me!CmdIndustrySort.Visible = False
        Me!LstBusinessSearch.SetFocus
    End Sub
    
    
    Private Sub CmdIndustrySortDesc_Click()
    'Set Industry order in descending order and apply captions
    ResetSortCmdColor
    Dim response As Integer
        response = basOrderby("CategoryName", "DESC")
        Me!CmdIndustrySort.ForeColor = RGB(75, 255, 79) ' Green
        Me!CmdIndustrySort.Visible = True
        Me!CmdIndustrySort.Caption = "Industry (DESC)"
        Me!CmdIndustrySort.SetFocus
        Me!CmdIndustrySortDesc.Visible = False
        Me!LstBusinessSearch.SetFocus
        
    End Sub

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Yes, Sorry about the size Welshgasman,
    I do use two 32" monitors as getting old and cant see stuff anymore
    Thanks
    Dave

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by d9pierce1 View Post
    Yes, Sorry about the size Welshgasman,
    I do use two 32" monitors as getting old and cant see stuff anymore
    Thanks
    Dave
    And there is me on a 17" laptop.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Also had t make 6 buttons
    For 3 sorted columns? 3 would be enough as the main difference in appearance would be captions? So
    Code:
    If button caption says this 
      do this
      caption = A
    else
      do that
      caption = B
    End If
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Using an option group like my post above the code is simply
    Code:
    Private Sub Frame6_AfterUpdate()
    
    
        Select Case Me.Frame6
    
    
            Case 2
                Me.List0.RowSource = "Select CompanyID, CompanyName FROM tblCompany ORDER BY CompanyName;"
                
            Case 1
                Me.List0.RowSource = "Select CompanyID, CompanyName FROM tblCompany ORDER BY CompanyName DESC;"
                
        End Select
    
    The formatting of the buttons is done in the property sheet.
    Set the background and pressed colors and use alt code (Alt 30 and Alt 31) for the arrows in the caption property.
    
    
    
    End Sub
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-08-2019, 03:25 PM
  2. Sort Data by Clicking Label
    By LonghronJ in forum Modules
    Replies: 18
    Last Post: 08-03-2017, 05:16 AM
  3. Replies: 4
    Last Post: 10-03-2015, 01:22 AM
  4. Forms reload on Label Click
    By drunkenneo in forum Programming
    Replies: 4
    Last Post: 07-10-2013, 06:25 PM
  5. List Box Click Event
    By bginhb in forum Forms
    Replies: 3
    Last Post: 04-09-2012, 02:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums