Subform sorting (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

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:

Private Sub Form_DblClick(Cancel As Integer)
    Dim strFld As String
    strFld = Me.ActiveControl.Properties("ControlSource")
    SortColumn (strFld)
End Sub

When you double-click on column heading, that field will be the active control. This uses SortColumn sub:

Private Sub SortColumn(strFld As String)

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

End Sub

To ensure OrderByOn is on when form opens:

Private Sub Form_Load()
    With Me
        .OrderBy = ""
        .OrderByOn = True
    End With
End Sub

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):

Me.ControlName.ColumnWidth = 2880  'twips = 2 inches 

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I never let anyone hoover my controls .

Reply | Quote

The typo doesn’t look as funny to American eyes because the slang here is “vacuum”, not “hoover”.

Reply | Quote