I have an unbound combobox on a form that filters it according to the selection of the user. I would also like it to sort the form by the "Last_Name" field and I cannot get it to do it. I am sure this is a simple fix, but I cannot see it. Can anyone help? Thanks.
Here is my code:
Private Sub Filter_AfterUpdate()
Dim sql As String
If Me![Filter] = "ALL" Then
sql = "SELECT * FROM [Main]"
Me.RecordSource = sql
Me!Form.OrderBy = "Last_Name"
ElseIf Me![Filter] = "OSI" Then
sql = "SELECT * FROM [Main] WHERE [USCISProgram] = 'OSI'"
Me.RecordSource = sql
ElseIf Me![Filter] = "FDNS" Then
sql = "SELECT * FROM [Main] WHERE [USCISProgram] = 'FDNS'"
Me.RecordSource = sql
ElseIf Me![Filter] = "SCI" Then
sql = "SELECT * FROM [Main] WHERE [HasSCI] = 'Yes'"
Me.RecordSource = sql
ElseIf Me![Filter] = "Top Secret" Then
sql = "SELECT * FROM [Main] WHERE [ClearanceLevel] = 'Top Secret'"
Me.RecordSource = sql
Else
sql = "SELECT * FROM [Main]"
Me.RecordSource = sql
End If
End Sub
Here is my code:
Private Sub Filter_AfterUpdate()
Dim sql As String
If Me![Filter] = "ALL" Then
sql = "SELECT * FROM [Main]"
Me.RecordSource = sql
Me!Form.OrderBy = "Last_Name"
ElseIf Me![Filter] = "OSI" Then
sql = "SELECT * FROM [Main] WHERE [USCISProgram] = 'OSI'"
Me.RecordSource = sql
ElseIf Me![Filter] = "FDNS" Then
sql = "SELECT * FROM [Main] WHERE [USCISProgram] = 'FDNS'"
Me.RecordSource = sql
ElseIf Me![Filter] = "SCI" Then
sql = "SELECT * FROM [Main] WHERE [HasSCI] = 'Yes'"
Me.RecordSource = sql
ElseIf Me![Filter] = "Top Secret" Then
sql = "SELECT * FROM [Main] WHERE [ClearanceLevel] = 'Top Secret'"
Me.RecordSource = sql
Else
sql = "SELECT * FROM [Main]"
Me.RecordSource = sql
End If
End Sub