Orderform Problem

[smbarney]

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

 

[boblarson]

Just add this part to your SQL statements:

ORDER BY [Main].[LastName]

So totally it would be like:

sql = "SELECT * FROM [Main] ORDER BY [Main].[LastName];"

 

[pdx_man]

Sorry to throw my $.02 in here, but I gotta clean this code up a bit.

Private Sub Filter_AfterUpdate()
Dim WhereCls As String

SELECT CASE Me![Filter] 
	CASE "OSI": WhereCls =  " WHERE [USCISProgram] = 'OSI'"
	CASE "FDNS": WhereCls = " WHERE [USCISProgram] = 'FDNS'"
	CASE "SCI": WhereCls =  " WHERE [HasSCI] = 'Yes'"
	CASE "Top Secret": WhereCls = " WHERE [ClearanceLevel] = 'Top Secret'"
	CASE ELSE: WhereCls = ""
END SELECT

Me.RecordSource = "SELECT * FROM [Main]" & WhereCls & " ORDER BY [Last_Name]"


End Sub

 

[boblarson]

And a good .02 worth it is

pdx_man - Great job as usual. Hey, by the way, what area of Portland are you located? I live in Fairview and work in Tigard.

 

[smbarney]

Thanks!

Thanks all. I got it working. You guys rule!

 

[pdx_man]

Sure thing.

I live in Beaverton and work in Lake O. Hwy 217 is a b!tch most of the time. I'm guessing your commute isn't too bad.