Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Using Orderby with forms (Access 97 /2000)

    I often find myself wanting to provide an easy way for users to change the sort order of records in continuous forms.
    The simplest way to do this seems to me to use vba code to set the "orderby" property of the form. But what I find is that sometimes it works, and sometimes it doesn't.
    I read somewhere that for Reports, the orderby property only takes effect if the orderbyon property is set to true.
    Is the same true for forms? It seems to be, but how can I tell whether a form's OrderByOn property is true or false? It seems to me to be a hidden property.

    I attach a little db with 3 forms. The first two forms look the same ,except that the sort buttons work on one form and not the other. Can anyone tell me where to look to see why they don't work on form2.

    On form3, you can turn the sorting on and off, using the yes/no buttons.

    In a way, I have already solved my own problem.
    If I always preceed any line of code me.Orderby = "some field" with me.orderbyOn = true , then the sorting will work.
    Attached Files Attached Files
    Regards
    John



  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    For OrderBy to work, OrderByOn has to be set to True. This can be done on Form Load event. It only has to be done once. As long as OrderByOn is True the OrderBy commands will function properly. You don't have to repeat command each time you re-sort form.

    I've attached modified copy of sample db as zip file. I modified frm2 by adding OrderByOn = True to form's Load event. Also I usually use a generic sub to sort form. Example used in attached db:

    Private Sub SortForm(strField As String, intOrder As Integer)

    With Me
    Select Case intOrder
    Case 1 'Ascending
    .orderby = strField
    Case 2 'Descending
    .orderby = strField & " DESC"
    Case Else
    'do nothing
    End Select
    DisplaySortOrder
    End With

    End Sub

    Example of use:

    Private Sub Command12_Click()
    SortForm "FirstName", 1
    End Sub

    Private Sub Command13_Click()
    SortForm "FirstName", 2
    End Sub

    Also added 2 unbound textboxes to form (for test purposes only) that display current value of form's OrderBy and OrderByOn properties. These textboxes are updated when changing sort order or turning OrderByOn on or off with the Yes & No buttons:

    Private Sub DisplaySortOrder()

    With Me
    .TextOrderBy = .orderby
    .TextOrderByOn = .OrderByOn
    End With

    End Sub

    I had to convert db to A2K format to open; attd file converted back to A97 format but should work OK.

    HTH
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Orderby with forms (Access 97 /2000)

    I do something rather similar, but I use labels rather than command buttons because I don't want the label to ever receive the focus, only to respond to a click. And I use a custom Labels class to handle the code so that I don't have to repeat it in various forms.
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    <P ID="edit" class=small>(Edited by MarkD on 22-Dec-02 12:40. Modified ZIP file attached.)</P>PS - In further reply, I've attached a 2nd modified copy of sample database to demonstrate a technique I sometimes use for continuous forms where command buttons are used for column headings; when you click button the form is sorted by that field in ascending or descending order (toggles sort order to impersonate behavior of column headings in Windows Explorer, Outlook, etc). The SortForm sub looks like this:

    Private Sub SortForm(strField As String)

    With Me
    If .orderby = strField Then
    .orderby = strField & " DESC"
    Else
    .orderby = strField
    End If
    DisplaySortOrder
    End With

    End Sub

    Example of command button's On Click event procedure:

    Private Sub cmd_FirstName_Click()
    SortForm "FirstName"
    End Sub

    See form named "SortByColumns1" in attached file. This may simplify things; and users are probably used to sorting columns by clicking column headings from doing so in other programs.

    PS: Attached revised copy of attachment with 2nd sample form, "SortByColumns2" which includes additional command buttons with Up and Down arrows to indicate sort order (Up for Ascending, Down for Descending, in emulation of Windows Explorer). For this form to work copy the 2 bitmap files in attd ZIP file to specified folder, I used "C:ACCESS"; if copied elsewhere modify SortForm sub's "strImgPath" variable to reflect correct path. The bitmaps are used for the 3 new command buttons' Picture property.

    HTH
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    Thanks for those suggestions.
    I am still interested in the question : "is orderbyOn" a hidden property of a form? It doesn't appear in the properties box. I don't think I have come across hidden properties before. Is there any way to find out its value other than by using code.
    According to Help, its default value is true. It also says that a new form will inherit is orderbyon property from the table or query it was created from. Do tables and queries have an OrderbyOn property? How can I tell what value it has.

    Thanks Charlotte for the suggestion about using Labels instead of command buttons. One bonus of this is that you can have a pseudo command button that hides itself without having to put in an extra line to move the focus.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Orderby with forms (Access 97 /2000)

    OrderByOn and FilterOn are Yes/No properties that are set from code unless you pass a FilterString or WhereCondition into a DoCmd.OpenForm statement. In that case, they're set by Access itself. If you base a form on a SQL statement with an OrderBy clause, then OrderBy will be turned on automatically as well. No, these are not visible in the properties sheet because they can't logically be set unless the form is open or in the process of being opened. The properties apply to the recordset underlying the form and that is not available when the form is open in design view, so not showing the properties in design view is entirely consistent.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    After playing around with various options I have written this function that can be palced in a general module.
    <pre>Public Function fnSort(strFieldname1 As String, _
    Optional StrSortType As String = "A", _
    Optional StrFieldName2 As String)
    Dim StrSortOrder As String

    On Error GoTo fnSort_Error

    StrSortOrder = strFieldname1
    If StrSortType <> "A" Then
    StrSortOrder = StrSortOrder & " DESC"
    End If
    If StrFieldName2 <> "" Then
    StrSortOrder = StrSortOrder & ", " & StrFieldName2
    End If
    With Screen.ActiveForm
    .OrderByOn = True
    .orderby = StrSortOrder
    End With

    Exit Function

    fnSort_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
    End Function
    </pre>

    I can call it from any form without creating an event procedure. Instead in the onclick box put =fnsort("Surname")

    I found that I can't leave out the second parameter and use the third if I call the function directly, but can if I call it from an event procedure.
    e.g.=-fnsort("Surname", ,"Firstname") produces a syntax error, but if I create an event procedure then call the function:
    fnsort "Surname", , "firstname"
    it works OK.
    Is this normal for use of optional parameters?
    Attached Files Attached Files
    Regards
    John



  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    MS's documentation doesn't seem to mention anything about not being able to use optional arguments when calling function as an Event property. I do not normally use =(FunctionName) in place of Event Procedures because it can be hard to debug so I haven't run into this problem before. If you don't want to use Event Procedure for some reason (lightweight form?) you can rewrite function so there are no optional arguments. Here is one example:

    Option Compare Database
    Option Explicit

    Enum udSortType
    Ascending = 1
    Descending = 2
    End Enum

    Public Function SortForm(Sort As udSortType, ParamArray varFields() As Variant)

    ' varFields: Optional array of fields to sort by

    Dim n As Integer
    Dim strSortType As String
    Dim strOrderBy As String

    ' Note: Can only assign numerical values (Long) to Enum elements
    Select Case Sort
    Case 1
    strSortType = "ASC"
    Case 2
    strSortType = "DESC"
    End Select

    For n = 0 To UBound(varFields)
    strOrderBy = strOrderBy & varFields(n) & " " & strSortType & ","
    Next

    strOrderBy = Left(strOrderBy, Len(strOrderBy) - 1)

    With Screen.ActiveForm
    .OrderByOn = True
    .OrderBy = strOrderBy
    End With

    End Function

    The ParamArray keyword allows you to pass function an arbitrary number of arguments as an optional array of Variant elements; in this example, the field or fields that form will be sorted by. This gives you the flexibility to sort by one or more fields. The first argument, Sort, uses a user-defined Enum type (udSortType) so you get "Intellisense" list when entering arguments in code module. (Note: cannot specify a default value of 1 (ascending) for Sort argument because you cannot use optional arguments with ParamArray.) The sample function above worked both as Event Procedure and when entered in property sheet (example is using continuous form based on Northwind.mdb Orders table). Syntax for event procedure:

    Dim n As Integer
    n = SortForm(Ascending, "EmployeeID", "CustomerID", "ShippedDate")

    If entered in property sheet for On Click property:

    =SortForm(1,"EmployeeID","CustomerID","ShippedDate ")

    Note that if used in property sheet, you must enter numerical value for Sort argument, you can't use Enum element name as you can in code module. One drawback with above function, you can specify only 1 sort order for all fields; all fields will be sorted either ascending or descending. To mix & match would require a more complicated function.

    HTH

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Orderby with forms (Access 97 /2000)

    Yes, I've seen that behavior. It appears that when you call a function from the property sheet, you have to explicitly pass all the arguments, including any optional arguments. Like Mark, I avoid that approach because it is so hard to debug. However, it has gotten a lot of use in so-called "light weight" forms, where there is no code module behind the form. You have stumbled over one of the reasons I don't like it.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    This is revised function that allows you to specify sort order (Ascending or Descending) for each field being sorted. It uses a user-defined Type along with user-defined Enum to simplify(?) things. When declared as Public (the default), Types & Enums provide Intellisense lists when typing code. Revised function:

    Option Compare Database
    Option Explicit

    Enum udSortOrder
    Ascending = 1
    Descending = 2
    End Enum

    Type udtSort
    SortField As String
    SortOrder As udSortOrder
    End Type

    Public Function SortFormRev(Sort() As udtSort)

    Dim n As Integer
    Dim strOrderBy As String
    Dim strSortType As String

    For n = 1 To UBound(Sort)
    strSortType = GetSortOrder(Sort(n).SortOrder)
    strOrderBy = strOrderBy & Sort(n).SortField & " " _
    & strSortType & ","
    Next

    strOrderBy = Left(strOrderBy, Len(strOrderBy) - 1)

    With Screen.ActiveForm
    .OrderByOn = True
    .OrderBy = strOrderBy
    End With

    Because an Enum does not allow you to specify constant values other than Long Integers, this function converts Enum value to string:

    Function GetSortOrder(SortOrder As udSortOrder) As String
    Select Case SortOrder
    Case 1
    GetSortOrder = "ASC"
    Case 2
    GetSortOrder = "DESC"
    End Select
    End Function

    Example of use in an event procedure:

    Dim Sort(1 To 2) As udtSort
    Dim n As Integer

    Sort(1).SortField = "CustomerID"
    Sort(1).SortOrder = Ascending
    Sort(2).SortField = "ShippedDate"
    Sort(2).SortOrder = Descending

    n = SortFormRev(Sort())
    Erase Sort

    This example uses Orders table in Northwind.mdb. The array variable (Sort) should be dimensioned to number of fields being sorted, with 1 as lower bound. As shown, this affords you flexibility to specify sort order for each field. Only drawback is, you cannot call this function from a property sheet because it requires as its argument an array of user-defined type udtSort, which must be populated before calling function, as shown above.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    Thanks again for these sugestions. I will try them out.
    I don't often call functions directly from the event, but at times I deliberately try doing things different ways to see what happens.
    What I really want is a function I can place in a standard module so it will be available any time I want it without having to recreate it.
    Regards
    John



  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    I looked at sample db you attached, modified function slightly so there are no optional arguments, as it seems evident you cannot leave these blank if using function in property sheet. Revised function:

    Public Function fnSort(strFieldname1 As String, strSortType As String, _
    strFieldName2 As String)

    On Error GoTo fnSort_Error

    ' strSortType = ASC or DESC
    Dim strSortOrder As String

    If Len(strFieldName2) = 0 Then 'Sort on 1st field only
    strSortOrder = strFieldname1 & " " & strSortType
    Else
    strSortOrder = strFieldname1 & " " & strSortType & "," & strFieldName2
    End If

    With Screen.ActiveForm
    .OrderByOn = True
    .orderby = strSortOrder
    End With

    On Error GoTo 0
    Exit Function

    fnSort_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure fnSort of Module moduleMain"

    End Function

    Example of use when calling from property sheet (sorting on one field):
    <pre>=fnsort("Surname","ASC","")</pre>

    Note that if not sorting on 2nd field, you have to enter "" (two double-quotes) as 3rd argument to denote zero-length string. You also have to specifiy ASC or DESC for sort order as 2nd argument. Example if sorting on 2 fields:
    <pre>=fnsort("Surname","DESC","FirstName")</pre>

    Attached zip file is modified copy of sample db that demonstrates use of modified function. This may be simplest approach in this case.

    HTH
    Attached Files Attached Files

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    <<It appears that when you call a function from the property sheet, you have to explicitly pass all the arguments,

    You don't have to pass all the arguments, but you can't leave one out of the middle of the argument list.

    So this was ok =fnsort("Surname")
    but this produced an error : =fnsort("Surname", ,"Firstname")
    Regards
    John



  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    Thnks again Mark. I have a surfeit of options to consider.

    In your previous posts you called the sorting functions with this syntax:
    Dim n As Integer
    n = SortForm(Ascending, "EmployeeID", "CustomerID", "ShippedDate")
    rather than
    SortForm Ascending, "EmployeeID", "CustomerID", "ShippedDate"

    Was there any deliberate logic with this, or just a matter of arbitary choice?

    I was surprised to see that this worked OK even though the functions called did not specify a return type, not assign a return value anywhere in the code. I checked the value of n after these function calls in it held 0.
    Regards
    John



  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using Orderby with forms (Access 97 /2000)

    Either syntax for calling function works, I'm in habit of assigning return value of function to a variable; it's not necessary in this case since the function doesn't return a value. Looking at this one more (last) time, here is one (last) option that combines 2 previous approaches, using ParamArray to populate an array of a user-defined type with a varying number of arguments. Revised function:

    Public Function SortFormRev2(ParamArray varSort() As Variant)

    Dim n As Integer 'Counter for varSort() arg array
    Dim i As Integer 'Counter for Sort() udt array
    Dim intArgCount As Integer
    Dim Sort() As udtSort 'Dynamic array of User-Defined Type udtSort
    Dim strSortType As String
    Dim strOrderBy As String

    ' Following assumes correct (even) number of args passed to function
    ' UDT udtSort has 2 elements, so divide # of args by 2:
    intArgCount = UBound(varSort) + 1
    ReDim Sort(1 To intArgCount / 2)

    ' Populate Sort() Array:
    For n = 0 To intArgCount - 1
    '(Mod 2 = 0) = Even numbers; this condition will be true 1st loop:
    If n Mod 2 = 0 Then
    i = i + 1 'increment once every 2 loops
    Sort(i).SortField = varSort(n)
    Else
    Sort(i).SortOrder = varSort(n)
    End If
    Next n
    ' Concatenate OrderBy string:
    For i = 1 To UBound(Sort)
    strSortType = GetSortOrder(Sort(i).SortOrder)
    strOrderBy = strOrderBy & Sort(i).SortField & " " _
    & strSortType & ","
    Next
    strOrderBy = Left(strOrderBy, Len(strOrderBy) - 1)

    With Screen.ActiveForm
    .OrderByOn = True
    .OrderBy = strOrderBy
    End With

    Erase Sort

    End Function

    In above example, the udtSort array is populated based on arguments passed to function using ParamArray keyword. This allows you to call function from property sheet. Example (sorting on 1 field):

    =SortFormRev2("ShipCountry",1)

    Sorting on multiple fields:

    =SortFormRev2("EmployeeID",1,"CustomerID",1,"Order Date",2)

    Note: if calling function on property sheet, the SortOrder Enum values must be entered as numbers (1=Ascending, 2=Descending), you can't use the named constants. Function uses the same user-defined Enum, Type, and GetSortOrder function shown previously (Post 208683). Note that ParamArray array argument can only be declared as a Variant; its lower bound is always zero (0) regardless of any Option Base statement in module. As a general rule, it's preferable to use specific data types (including user-defined types) as function arguments; ParamArray used here only to enable use of function on property sheet and to allow a variable number of fields to be specified w/o optional arguments. If you intend to sort forms by only 1 or 2 fields at most, may want to use simpler approach, though above works with single field.

    Attached zip file contains sample db (A2K format) that demonstrates this technique using form based on Northwind.mdb Orders table. To test, unzip & import form (frmTestSortFunction) and module (modSort) into Northwind.mdb; or open and re-link Orders table, if necessary - sample uses default location for Northwind in AXP.

    HTH
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •