Visual Basic code to Sort (A2K)

Topic

New Reply

I want to put a cmd button on my form to allow the user to ‘sort ascending’ the table on the form field that has the focus.
The process works great if I use the Records, Sort, Sort Ascending selections from the Menu bar but I can not make it work
using code. I will appreciate any suggestions. Thank you.

Reply | Quote

Replies

The problem is that when you click the command button, the button gets the focus, so there is no field that has the focus any more,

As an alternative, you can put a combo box on the form with Row Source Type set to List of Fields and Row Source set to the name of the table or query that acts as record source of the form. In the AfterUpdate event of the combo box, you can set the OrderBy property of the form to the selected field, and the OrderByOn property to True

If you don’t want to use a combo box, you must use a keyboard shortcut or a toolbar button that reads the Control Source of the ActiveControl, and orders the form by that field. You can set the KeyPreview property of the form to True, and handle keystrokes at the form level instead of for each control separately.

Reply | Quote

In attachment you find an example using a menu button, which calls a macro (which calls a function) sorting your form on the active control. I created two buttons, for ascending & descending sort order respectively.
You still need to convert it from Access 97 to 2K but that shouldn’t raise any problems (!?). It’s the first time I put an attachment on this forum. I hopes it works…
Greetings,
Hasse

Reply | Quote

In case anyone should be interested in the code (stored in the module), which, I presume, is moreless what HansV suggested:

‘Sort active form ascending using active control
Public Function ffrmSortAsc()
Dim frm As Form, ctl As Control
Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl
frm.OrderBy = ctl.ControlSource
frm.OrderByOn = True
End Function

‘Sort active form descending using active control
Public Function ffrmSortDesc()
Dim frm As Form, ctl As Control
Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl
frm.OrderBy = ctl.ControlSource & ” DESC”
frm.OrderByOn = True
End Function

Reply | Quote

Thank you very much your suggestion was very helpful. I had to make some modifications but I did get it to work as I wanted. Again thank you. It is wonderful to have experts that are willing to help us ‘not so expert’. Have a wonderful day.

Reply | Quote

Hey, Ifwatson,
I’m happy I could, for a change, help someone else too :-).
Please notice that I changed my posts: I cut the code of the second function procedure, which factually wasn’t needed at all.
Just one last question, out of curiosity: why do you want to sort using code instead of the available Access sort buttons?
Hasse

Reply | Quote

Hasse,
The reason for the command button is that my users are NOT very savvy with Access and by putting everything they need to do on the screen it makes them have to think even less. I have been coding Access inhouse applications for about 10 years. Before Access I created applications with dBase. If I can be nosey, are you an application programmer?
Thank you again.

Reply | Quote

Hey, Ifwatson,
honestly, ‘programmer’ might still be a bridge too far in my case. For example, I think I still lack some automatisms skilled IT-people have,… But I’m able to do moreless what I want in VBA. Access is more a hobby to me, but as I’m having the opportunity to amuse myself with it (he he) at my job as well, I-m a bit a professional as well (though I think most of us can say that :-).

Thanks both for telling why ‘doing on your own’ what Access also offers, is sometimes unavoidable.

And by the way, Charlotte, thanks for your tip. Often, many ways lead to Rome but this is a neat suggestion, especially when you use continuous forms.

Hasse

Reply | Quote

It’s quite common to provide custom methods for users to sort data because the built in menus are not usually available in finished applications. One technique I use is the click event of the label for the column. In that event, I set the OrderBy property of the form to the associated field and then set the OrderBy on. Users don’t have to know about anything except that clicking the label will sort by that column. I usually set it up so that the sort toggles. If they click the label again, the sort reverses itself between ascending and descending. Many users have never figured out what the sort buttons are for even if they are visible.

Reply | Quote