2nd search box not sort properly after using 1st search box

[Song Su]

I have 2 combo boxes on a continuse form. One to search name and Other to
search Department.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Me.OrderBy = ""
Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND [First Name] = '"
& Me![cboFind].Column(1) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

Private Sub cboDepartment_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Department] = '" & Me![cboDepartment] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboDepartment.BackColor = -2147483633
Me.Department.SetFocus
End Sub

My problem is: If I use cboDepartment only, search sort ok. If I use cboFind
first, then use cboDepartment, it still sort by name, and find first
matching department instead of sort by Department, then by name.

What did I do wrong?

 

[John W. Vinson]

My problem is: If I use cboDepartment only, search sort ok. If I use cboFind
first, then use cboDepartment, it still sort by name, and find first
matching department instead of sort by Department, then by name.

The combo boxes shouldn't affect the sort order... if you've specified a sort
order! If you haven't, Access will display the records in whatever order it
finds convenient; it may be that searching by department makes the Department
index active so that the records are displayed in that order.

If you base the Form on a Query sorted the way you want - instead of on the
table directly - you should be able to preserve the desired sort order.

John W. Vinson [MVP]