Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform sorting (Access 97)

    Hi all!
    i have a subform on a form for viewing records in a spreadsheet-like fashion. i am trying to find a way to sort this subform by whichever column heading is selected (without using the buttons on the toolbar or right-click menu). In my app, i have default menus off, as well as right-click menus. Ultimately i would like to be able to sort columns just by clicking the heading, and if it is already sorted (ascending, for example) it would then sort descending automatically. i just haven't figured out the code to handle this. any help would be appreciated.

    one more thing: is there a way to size the width of the fields in this subform to a default width? i want the users to be able to adjust the width if they wish, but i want a default size to be set (most of the fields are too wide when set up by the wizard).

    thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform sorting (Access 97)

    I don't know of a way to sort by clicking in the column heading in a datasheet view.

    You can simulate it in a continuous form by putting command buttons in the form header. There are several demos around that demonstrate this. For instance, you can download a demo version of the Selector utility from Peter's software that has this feature.

    I have attached a very simple demo (access 97, zipped).

    In a continuous form, users can't change the column width, however (unless you write code for that).
    Attached Files Attached Files

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

    Re: Subform sorting (Access 97)

    To toggle sort columns in ascending/descending order I normally use continuous forms using technique probably similar to that Hans provided. The only way I know to accomplish this with a form or subform in datasheet view is to use the Form Click or DblClick event. Would NOT recommend using Click event for this, it will be triggered whenever you click anywhere on form, such as when you click in a cell. DblClick is probably less prone to unexpected results. Sample code to sort columns:
    <pre>Private Sub Form_DblClick(Cancel As Integer)
    Dim strFld As String
    strFld = Me.ActiveControl.Properties("ControlSource")
    SortColumn (strFld)
    End Sub</pre>

    When you double-click on column heading, that field will be the active control. This uses SortColumn sub:
    <pre>Private Sub SortColumn(strFld As String)

    With Me
    If .OrderBy = strFld Then
    .OrderBy = strFld & " DESC"
    Else
    .OrderBy = strFld
    End If
    End With

    End Sub</pre>

    To ensure OrderByOn is on when form opens:
    <pre>Private Sub Form_Load()
    With Me
    .OrderBy = ""
    .OrderByOn = True
    End With
    End Sub</pre>

    To set default width for datasheet columns when form opens use code like this for each column on form Load or Open event (set width in twips - 1440 twips = 1 inch):
    <pre>Me.ControlName.ColumnWidth = 2880 'twips = 2 inches </pre>

    As noted above I normally use continuous form disguised as datasheet for this type of thing, the drawback is user cannot adjust column widths.

    HTH

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

    Re: Subform sorting (Access 97)

    What is the point in allowing the user to set column widths? Since you know the approximate width of the data in the fields, it seems more appropriate for you to set the column widths in advance in a continuous form. If you have a memo field that you want expanded, use the double click of that control to zoom the field, which will display far more than they could by changing column widths.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform sorting (Access 97)

    well, much of the data in some fields is only several words or so. however sometimes there are many words in the field, and i would like the users to be able to read all of what is in that field. perhaps i don't need to be able to resize the field, but can the user get to all the data in a field that is not long enough? (perhaps selecting the field and pressing the "End" key)

    thanks for your input

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform sorting (Access 97)

    Shift+F2 opens a "zoom" window that is very handy to view/enter/edit data.

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

    Re: Subform sorting (Access 97)

    In addition to the keyboard shortcut Hans mentioned, you can use a form event to zoom the field. I generally use the dbl-click event of the control to zoom the field and put a label in the form header or footer to indicate that double-clicking a field will zoom it. The code line is simply: RunCommand acCmdZoomBox
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Subform sorting (Access 97)

    We often do as Charlotte suggests using the dbl-click event, and put a tool-tip on the control so if they hoover over the control they will see the message that they can double-click to open the Zoom dialog.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform sorting (Access 97)

    I never let anyone hoover my controls <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

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

    Re: Subform sorting (Access 97)

    The typo doesn't look as funny to American eyes because the slang here is "vacuum", not "hoover". <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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