user interface, multiple sorting

[gefilte]

I've seen plenty of solutions for initial sorting, but nothing for this...


I have a form with multiple fields.
On the onclick event for the lables for the columns, is:
[fieldname].SetFocus
DoCmd.RunCommand acCmdSortAscending

How can I add a feature to allow the user to click it again and sort
descending if it is already ascending?

I'm guessing that I can pass a reference to a variable to store the current
state of sorting ie.
if me.[sort_tbl].[sort_date] = true
then sort desc
me.me.[sort_tbl].[sort_date] = false
else
sort asc
me.[sort_tbl].[sort_date] = true

however, it's giving me an error that it can't find the field for
[sort_tbl].[sort_date]
How should I reference it?

Thanks

 

[Graham Mandeno]

First, a couple of tips:

1. You don't need to SetFocus to a control in order to sort by that field.
Instead, you can say:
Me.OrderBy = "[fieldname]"
Me.OrderByOn = True

Using this method is more flexible because you can specify
secondary and more fields to sort by:
Me.OrderBy = "[field1], [field2], [field3]"
Me.OrderByOn = True

2. Rather than repeating your Click procedure for each label, with the only
difference being the fieldname, you can have just one procedure (it must
be a Function) and pass the fieldname as an argument. Then, in the
properties for each label you have:
On Click: =SetSortOrder("[fieldname]")
(note the quotes)

Now, to answer your question

You can use static variables in your shared function to "remember" what the
last sort order was. Something like this:

Private Function SetSortOrder( sFieldName as string )
Static sSortField as string
Static fDescending as boolean
If sFieldname = sSortField then
' same field as last time - switch ascending/descending
fDescending = Not fDescending
Else
' set new field and reset to Ascending
sSortField = sFieldName
fDescending = False
End If
' now set the sort order
Me.OrderBy = sSortField & IIf(fDescending, " DESC", "")
Me.OrderByOn = True
End Function

Now just set the OnClick properties of each label as in (2) above.

 

[gefilte]

Wow, perfect instructions. thank you so much.

Works great!

I'm still somewhat new to vba, but does using the static declaration keep
that variable value as long as the current form is open. I know it says that
it keeps the value as long as the module is running, is that as long as the
form is open in this case?

This will help a lot for other issues I'd like to solve.

First, a couple of tips:

1. You don't need to SetFocus to a control in order to sort by that field.
Instead, you can say:
Me.OrderBy = "[fieldname]"
Me.OrderByOn = True

Using this method is more flexible because you can specify
secondary and more fields to sort by:
Me.OrderBy = "[field1], [field2], [field3]"
Me.OrderByOn = True

2. Rather than repeating your Click procedure for each label, with the only
difference being the fieldname, you can have just one procedure (it must
be a Function) and pass the fieldname as an argument. Then, in the
properties for each label you have:
On Click: =SetSortOrder("[fieldname]")
(note the quotes)

Now, to answer your question

You can use static variables in your shared function to "remember" what the
last sort order was. Something like this:

Private Function SetSortOrder( sFieldName as string )
Static sSortField as string
Static fDescending as boolean
If sFieldname = sSortField then
' same field as last time - switch ascending/descending
fDescending = Not fDescending
Else
' set new field and reset to Ascending
sSortField = sFieldName
fDescending = False
End If
' now set the sort order
Me.OrderBy = sSortField & IIf(fDescending, " DESC", "")
Me.OrderByOn = True
End Function

Now just set the OnClick properties of each label as in (2) above.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I've seen plenty of solutions for initial sorting, but nothing for this...


I have a form with multiple fields.
On the onclick event for the lables for the columns, is:
[fieldname].SetFocus
DoCmd.RunCommand acCmdSortAscending

How can I add a feature to allow the user to click it again and sort
descending if it is already ascending?

I'm guessing that I can pass a reference to a variable to store the
current
state of sorting ie.
if me.[sort_tbl].[sort_date] = true
then sort desc
me.me.[sort_tbl].[sort_date] = false
else
sort asc
me.[sort_tbl].[sort_date] = true

however, it's giving me an error that it can't find the field for
[sort_tbl].[sort_date]
How should I reference it?

Thanks

 

[Graham Mandeno]

Wow, perfect instructions. thank you so much.

Works great!

Glad to help

I'm still somewhat new to vba, but does using the static declaration keep
that variable value as long as the current form is open. I know it says
that
it keeps the value as long as the module is running, is that as long as
the
form is open in this case?

Yes, that is precisely correct. Static variables (and globals too, for that
matter) also get reset when your code environment is reset - for example,
when you get an unhandled error and click on [End] rather than [Debug].