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
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
"gefilte" <gef...@discussions.microsoft.com> wrote in message
news:15525499-D392-48B3...@microsoft.com...
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.
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].