Ordering after a filter

[Huey462]

I have a filter code that works wonderfully for allowing the end user to only display records that match criteria "X". The only problem is when the filter is applied, the ordering that is done when the form is initially loaded is undone.
I’ve searched the forums and the web in general and found some examples on applying a filter, but I’m VBA ignorant enough to understand the concept, but not have enough of a grasp to be able to retool it for my file.
Here is the information

Table: CBM
Form: CBMForm
Field to sort by: Serial Number (two words)

If it helps, here is the search code

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"

        'Filter frmCustomers based on search criteria
        Form_CBMForm.RecordSource = "select * from CRL where " & GCriteria
        Form_CBMForm.Caption = "Sorted by: " & cboSearchField.Value & " containing '*" & txtSearchString & "*'"
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"

        ' Updates the Record Count
'        Form_ CBMForm.OrderByOn = True
'        Form_ CBMForm.OrderBy "Serial Number"
'        Form.OrderBy = "[Serial Number] ASC"
'        Form.OrderByOn = True
        DoCmd.GoToRecord , , acLast
        DoCmd.GoToRecord , , acFirst
        
    End If
    
End Sub

 

[dhookom]

I would try code like:

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"

        'Filter frmCustomers based on search criteria
        Forms!CBMForm.Filter = GCriteria
        Forms!CBMForm.FilterOn = True
        ' you changed the filter, not the sorting
        Forms!CBMForm.Caption = "Filtered by: " & _
          Me.cboSearchField & " containing '*" & Me.txtSearchString & "*'"
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"

        ' Updates the Record Count
        DoCmd.GoToRecord , , acLast
        DoCmd.GoToRecord , , acFirst
        
    End If
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Huey462]

dhookom,

Thank you, it works just like I needed =D