Sort records in form programmatically

[leroyb]

Hello,

using Access97 and WinNT

I am trying to have the records displayed on a continuous form sorted
programmatically by using the click event of the column label.
The forms displays a bunch of records, one of the fields is Date
Submitted .... when the user clicks on the label in the form header,
the records would then be sorted by Date. Clicking on the other labels
would sort the records accordingly.
The above works, however I would like to be able to switch between
Ascending and Descending .. to do that I have the following code

Me.OrderByOn = False
Me.OrderBy = ""

If Me.OrderBy = "[RI_Date] ASC" Then
Me.OrderBy = "[RI_Date] DESC"
Else
Me.OrderBy = "[RI_Date] ASC"
End If

Me.OrderByOn = True
Me.Filter = "source = 1"
Me.FilterOn = True

...... but that above does not work .... it only keeps on displaying
[RI_Date] in the Order By property of the form .... and obviously the
sort order does not change.

Could someone show me how to achieve this?

thanks,

regards,

Bertrand

 

[Arvin Meyer [MVP]]

Not sure what the filter is, but it doesn't particularly affect your
problem, unless the filter has its own Order By clause. In any case, I'd
apply the filter first, then the Sort (Order By) clause. But that's not you
problem here. Your problem occurs because you are setting the Order By to ""
first. In that case, it will never be able to satisfy your If ... Then ...
Else statement. Try this:

Me.Filter = "source = 1"
Me.FilterOn = True

If Me.OrderBy = "[RI_Date] ASC" Then
Me.OrderBy = "[RI_Date] DESC"
Else
Me.OrderBy = "[RI_Date] ASC"
End If

Me.OrderByOn = True
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[leroyb]

Arvin,

thank you very much for your reply.

I tried your solution but it did not work .
The order stays the same ie ascending (which is the default) ... any
ideas what could be going on?

regards,

leroyb

 

[Arvin Meyer [MVP]]

If that's the case, try setting a flag in the declarations section of your
form which will change with each click:

Dim bFlag As Boolean

Sub MyLabel_Click()

If bFlag = True
Me.recordsource = "Select blah from whatever order by [RI_Date] ASC"
Else
Me.recordsource = "Select blah from whatever order by [RI_Date] DESC"
bFlag = Not bFlag
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access