[AccessD] Problem with OrderBy function

Hi Bob

Sounds like you use the table directly as RecordSource for the form.
Have you tried using:

  Select * From tblAgencies Order By AgencyName;

as the source?

/gustav

>>> rbgajewski at adelphia.net 18-05-2008 19:01 >>>
Dear Friends

Once again, I find myself prevailing upon you to help me solve what should
be an easy problem.

I have a table (tblAgencies) which has an AutoNumber primary key and an
AgencyName index (ascending).

On my Form_Open (single form), I automatically apply a filter to show only
those records that have a Yes/No field (AgencyMutualAidFlag) equal to true.
On the form, there are two command buttons - one to show the filtered list
(cmdApplyFilter) and one to show all records (cmdShowAllRecords).

My problem is that when the form opens, the filtered recordset shows in
proper order - alphabetically by AgencyName. However, clicking on
cmdShowAllRecords returns the entire recordset but in AutoNumber order; not
by name. How do I add the OrderBy code to the command button code to ensure
that the records will ALWAYS be in AgencyName order? BTW, clicking on
cmdApplyFilter does return the filtered recordset in alphabetical order.

TIA,
Bob Gajewski


-----------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Call cmdApplyFilter_Click
End Sub
-----------------------------------------------------
Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click

    DoCmd.ApplyFilter , "AgencyMutualAidFlag = True"

Exit_cmdApplyFilter_Click:
    Exit Sub

Err_cmdApplyFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdApplyFilter_Click
    
End Sub
-----------------------------------------------------
Private Sub cmdShowAllRecords_Click()
On Error GoTo Err_cmdShowAllRecords_Click

    DoCmd.ShowAllRecords

Exit_cmdShowAllRecords_Click:
    Exit Sub

Err_cmdShowAllRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdShowAllRecords_Click
    
End Sub
-----------------------------------------------------