Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Sort Data by Clicking Label

    Hi,

    I would like to have a dynamic procedure that I can apply to many labels in many forms that when I click on the label, the data is sorted by that label. I think the biggest obstacle I have now is referencing the label that is clicked. I don't think ActiveControl applies to labels, so I cannot set a variable using ActiveControl.Caption. If I can reference the label's caption, I can set it to FieldName as the variable and utilize the procedure below. So, the question is how do I set the label's caption when it's clicked, and if you have any comment about the procedure below?

    If Right(Me.OrderBy,4) = "Desc" then
    Me.orderBy = "FieldlName"
    Else
    Me.orderBy = "FieldlName Desc"
    End if


    Me.OrderByOn = True

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the OnClick event of the label:
    Code:
        Dim frm As Form
        Set frm = Me
        Call SortByLabel(frm, "lblField1")
    Create a public function:
    Code:
    Public Function SortByLabel(FormName As Form, LabelName As String)
        FormName.OrderBy = LabelName
        FormName.OrderByOn = True
        FormName.Requery
        
    End Function

  3. #3
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    If I understand it correctly, I would substitute "lblField1" with the field name that I want to sort? If so, how can I make this more dynamic, so that I can apply this same procedure to other labels, not only in this form, but in other forms as well? In this particular form I have client name, start date, finish date, etc. I'd like to be able to sort by any of these fields by clicking on the label. Also, how do I make this sort toggle, so that when it's ascending, it becomes descending, vice versa?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What does "dynamic" mean? Isn't that what a public function is?

    To make it ascending or descending add another parameter to the function.

  5. #5
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    I want to be able to copy the procedure and apply to each label's click procedure without modifying the code for each event, therefore, it would pick up the caption of the labels that is clicked, which happen to be the same field name. So in your example, rather than manually replacing "lblField1" in each procedure, how can I assign, say assign strFieldName, to the caption of the label that is clicked? I believe if the label was a textbox, then I can simply say strFieldName = me.ActiveControl.name, but since it's a label, ActiveControl doesn't work.

    Would you mind adding the parameter for the toggling? I'm not too familiar on how to pass parameters to functions.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    rather than manually replacing "lblField1" in each procedure, how can I assign, say assign strFieldName, to the caption of the label that is clicked?
    Are you saying that you want only one procedure regardless of which label is clicked? I don't know if this is possible, maybe someone else knows.

    Add Public Cntr As Integer to the top of the form's code, after the Options.

    Code:
        Dim frm As Form
        Set frm = Me
        If Cntr = 0 Then
            Call SortByLabel(frm, "lblField1", "")
            Cntr = 1
        Else
            Call SortByLabel(frm, "lblField1, "DESC")
            Cntr = 0
        End If
    And the global function:
    Code:
    Public Function SortByLabel(FormName As Form, LabelName As String, Desc As String)
        FormName.OrderBy = LabelName & " " & Nz(Desc)
        FormName.OrderByOn = True
        FormName.Requery
        
    End Function

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A label that is attached to or associated with a textbox is a member of that control's collection, which oddly enolugh, has only one member. As with most collections it is zero based. To reference it and get its caption is like Me.txtEmplNo.Controls(0).Caption
    If it is not associated, this will generate an error.

    I could probably help further here, but am a bit confused as to where this is at and what's needed at this point. A function is probably more concise if this is would be repeated many times on a form or if portability is needed. Otherwise the label or control Click or DoubleClick event can simply grab it's own caption and apply the sort to the form. Same if you want to use a textbox event, except as noted, the label has to be associated with the control. Assuming the label is associated, I might have something like this in the control double click event:

    Code:
    If InstrRev(Me.OrderBy,"DESC") > 0 Then '> 0 means DESC found, otherwise assume ASC
     Me.OrderBy = Me.ActiveControl.Controls(0).Caption & " DESC" 'not sure, but this didn't raise an error
    Else
     Me.OrderBy = Me.ActiveControl.Controls(0).Caption & "ASC"
    End If
    Me.OrdeByOn = True
    Instr would probably work just as well. The number the Instr or InstrRev function returns is not important beyond its comparison to zero.

    To move this process to a function, you'd pass the form to it, test the sort order and alter the same way within that function.
    You do realize that with any of the current suggestions here, once a different label is clicked, the original sort is completely lost and becomes based solely on the new field?
    And we are not talking about a continuous form? You lose the label in a continuous form.
    Last edited by Micron; 08-02-2017 at 12:20 PM. Reason: code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    OK. Instead of using a label, I've created a textbox that appears to be a label. I have then capture the caption of the textbox, which is the field name, then assigned it to a variable, called strSortField. The code is below. However, it does not work. What am I doing wrong?

    Private Sub lblStart_Click() Dim strSortField As String
    strSortField = Me.ActiveControl.Caption

    If InStrRev(Me.OrderBy, "DESC") > 0 Then
    Me.OrderBy = "strSortField DESC"
    Else
    Me.OrderBy = "strSortField ASC"
    End If
    Me.OrderByOn = True
    End Sub

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Or (thinking out loud), you might identify those controls that you want to sort by (based on the RecordSource of the form), and put those in a combo (drop down)
    and allow user to select an entry. That selection would invoke an Event as aytee suggested--- identify the sortfield, turn OrderBy On, and requery the form.

    Totally untested(air code).

  10. #10
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Actually, in this form, I have six drop-down boxes. I just wanted to enhance the form by providing sorting features. Since I have several forms just like this with several columns/fields, I wanted to create just one routine so I can copy and paste it to all the events when clicked, thus I am hoping to use a variable versus hard coding it to the actual field name on each. It must be a syntax issue because when I hard code the field name, in this case, "Start", the sorting toggle works perfectly fine.

    If Me.OrderBy = "Start" Then Me.OrderBy = "Start DESC"
    Else
    Me.OrderBy = "Start"
    End If
    Me.OrderByOn = True

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database? And tell us specifically which form you are dealing with (let's do 1 to start),
    what fields/controls you want to Sort By and which direction.

    Since forms are class objects, you could make a form with these specific "features", then use this form in other situations. Whether this is practical or not depends on the various RecordSources and the fields involved.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I've got to ask: is there a reason you are not using Datasheet view? I believe it already does the things for which you are looking.

  13. #13
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Here it is. Had to make it a .mdb to keep the file small enough to attach. There is only one form.
    Attached Files Attached Files

  14. #14
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by RuralGuy View Post
    I've got to ask: is there a reason you are not using Datasheet view? I believe it already does the things for which you are looking.
    Would I be able to have drop-down boxes to filter with Datasheet view?

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by LonghronJ View Post
    Here it is. Had to make it a .mdb to keep the file small enough to attach. There is only one form.
    The system accepts 2MB files if it is a Zip file.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-15-2015, 03:08 AM
  2. Replies: 1
    Last Post: 04-25-2014, 11:41 AM
  3. preview data in label
    By darwish in forum Access
    Replies: 1
    Last Post: 04-09-2014, 09:59 AM
  4. Clicking on label adds new record?
    By swimmermx in forum Forms
    Replies: 1
    Last Post: 07-25-2010, 07:03 AM
  5. Clicking on label opens up new record
    By swimmermx in forum Forms
    Replies: 2
    Last Post: 07-20-2010, 03:58 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