can't sort after filtering

[Aravind]

Hi folks.

My form, frmHistory, has a combo box, cboName, that filters frmHistory
by member's name (using a macro). frmHistory also has a few labels
that I use for sorting the form (using VBA code). The problem is that,
when I filter the form using cboName, I can't sort the filtered form
(the form becomes empty if I try to sort). Here are the coding (I'm
using the sorting code for 1 of the labels). Does anyone know what
went wrong? I'm using Access 97. Thanks

--------------------------------------------------------------------------------
Private Sub Form_Open(Cance l As Integer)

Me.OrderBy = ""
Me.OrderByOn = True

DoCmd.ShowAllRe cords

End Sub
---------------------------------------------------------------------------------------
Private Sub lbl_AccNo_Click ()

Static x
x = x + 1
If x > 2 Then x = 1

Select Case x:
Case 1: Me.OrderBy = "AccNo"
Me.OrderByOn = True
Case 2: Me.OrderBy = "AccNo DESC"
Me.OrderByOn = True
End Select

End Sub
---------------------------------------------------------------------------------------
macro_cboName

Action: OpenForm
Form name: frmHistory
Where Condition: [MemName]=[Forms]![frmHistory].[cboName]

Action: SetValue
Item: [Forms]![frmHistory]![cboName]
Expression: Null

[Reggie]

There's other ways and this is sort of sloppy but it works. Initially set
the caption of this label to something like "Sort Ascending" (no quotes)

Private Sub lbl_AccNo_Click ()
Dim str As String
str = Me.lbl_AccNo.Ca ption

Select Case str
Case "Sort Ascending"
DoCmd.GoToContr ol "AccNo"
DoCmd.RunComman d acCmdSortAscend ing
DoCmd.Requery
Me.lbl_AccNo.Ca ption = "Sort Descending"
Case "Sort Descending"
DoCmd.GoToContr ol "AccNo"
DoCmd.RunComman d acCmdSortDescen ding
DoCmd.Requery
Me.lbl_AccNo.Ca ption = "Show All"
Case "Show All"
Me.OrderByOn = False
DoCmd.RunComman d acCmdRemoveFilt erSort
Me.Requery
Me.lbl_AccNo.Ca ption = "Sort Ascending"
End Select
End Sub

--
Reggie

----------
"Aravind" <so**********@h otmail.com> wrote in message
news:61******** *************** ***@posting.goo gle.com...

Hi folks.

My form, frmHistory, has a combo box, cboName, that filters frmHistory
by member's name (using a macro). frmHistory also has a few labels
that I use for sorting the form (using VBA code). The problem is that,
when I filter the form using cboName, I can't sort the filtered form
(the form becomes empty if I try to sort). Here are the coding (I'm
using the sorting code for 1 of the labels). Does anyone know what
went wrong? I'm using Access 97. Thanks

-------------------------------------------------------------------------- ------ Private Sub Form_Open(Cance l As Integer)

Me.OrderBy = ""
Me.OrderByOn = True

DoCmd.ShowAllRe cords

End Sub
-------------------------------------------------------------------------- ------------- Private Sub lbl_AccNo_Click ()

Static x
x = x + 1
If x > 2 Then x = 1

Select Case x:
Case 1: Me.OrderBy = "AccNo"
Me.OrderByOn = True
Case 2: Me.OrderBy = "AccNo DESC"
Me.OrderByOn = True
End Select

End Sub
-------------------------------------------------------------------------- ------------- macro_cboName

Action: OpenForm
Form name: frmHistory
Where Condition: [MemName]=[Forms]![frmHistory].[cboName]

Action: SetValue
Item: [Forms]![frmHistory]![cboName]
Expression: Null