sort a listbox on a combo

[gerard1979]

I have the code below to sort a listbox on a value in the combobox but I'm not happy with it.
Somebody know some cleaner code which do the same?
Thnx in advance,
gerard

Private Sub sortLijst_AfterUpdate()
tempStrSQL = "SELECT PROJECT.leadnummer AS Leadnr, PROJECT.projectnaam AS Projectnaam, " & _
"PROJECT.plaats AS Plaats, PROJECT.gevolgd_door AS Van, PROJECT.sessie_gezien AS Gezien, " & _
"PROJECT.vervalcode FROM PROJECT WHERE (((PROJECT.vervalcode)=False))"
If sortLijst = "van" Then
tempStrSQL = tempStrSQL & " ORDER BY [gevolgd_door]"
ProjectLijst.RowSource = tempStrSQL
ElseIf sortLijst = "leadnummer" Then
tempStrSQL = tempStrSQL & " ORDER BY [leadnummer]"
ProjectLijst.RowSource = tempStrSQL & " ORDER BY [leadnummer]"
Else
tempStrSQL = tempStrSQL & " ORDER BY [sessie_gezien]"
ProjectLijst.RowSource = tempStrSQL & " ORDER BY [sessie_gezien]"
End If
ProjectLijst.Requery
End Sub

 

[LittleSmudge]

You could try

Private Sub sortLijst_AfterUpdate()
Dim tempstrSQL As String
tempStrSQL = "SELECT PROJECT.leadnummer AS Leadnr, " _
           & "projectnaam, Plaats, " _
           & "gevolgd_door AS Van, " _
           & "sessie_gezien AS Gezien, " _
           & "vervalcode " _
           & "FROM PROJECT " _
           & "WHERE vervalcode = False "

If sortLijst = "van" Then
    tempStrSQL = tempStrSQL & " ORDER BY [gevolgd_door]"
    ProjectLijst.RowSource = tempStrSQL
ElseIf sortLijst = "leadnummer" Then
    tempStrSQL = tempStrSQL & " ORDER BY [leadnummer]"
    ProjectLijst.RowSource = tempStrSQL
Else
    tempStrSQL = tempStrSQL & " ORDER BY [sessie_gezien]"
    ProjectLijst.RowSource = tempStrSQL
End If
ProjectLijst.Requery
End Sub

If does the job in exactly the same way - it just looks neater.

With the exception of a few ORDER BY statements too many, the function of your code is fine.


G LS

 

[gerard1979]

thnx for ur quick reaction, but I was interested in an other way to handle this. I want to avoid the rowsource method and do it with a query or something....


Gerard

 

[LittleSmudge]

NO That's my real point.

You are already doing it in an efficient manner.

Move on to a real challenge - this bit works now. Don't spend any more time on "faffing" with it.


G LS

 

[gerard1979]

OK, I just believe u, the problem was: on some places in code I have to set the rowsource again instead of 'only' requery the cbo. So I had to truck on a few places, but not big troubles, As u said

Thnx
gerard

 

[LittleSmudge]

Sorry Gerard - I don't understand what you're saying.

Are you resetting the RowSource a number of times - from different stimuli ?

You could always make tempstrSQL into a Module level variable. Set it = the SQL string up to the WHERE clause but not the ORDER BY clause.

Then just re-use it each time using the single line
ProjectList.RowSource = tempStrSQL & " ORDER BY [gevolgd_door]"
in the If .. Then clauses.

( By the way - the whole If .. Then .. ElseIf .. structure could be replaces by a SELECT CASE structure and would probobly be more efficient.


G LS

 

[NVSbe]

I think I know what he means, being that the rowsource is needed to be updated quite often... leading of course to a lot of code if it's done each time by what you've suggested.

So my suggestion would be to place that code in a sub...

Public Sub UpdateCombo ()
' The suggested code here
End Sub

So then every time you wanted to update the combo it could be done by one command: Call UpdateCombo().

Is that what you meant?

 

[LittleSmudge]

Oh -- okay NVSbe - if that's what he means then yes.

With all the gusto of "reusable code" - begin mantra now .. .. ..

Then do exactly as NVSbe has posted above.

I was thinking only partway down that road - to needing different ORDER By clauses to the same basic dynaset.


G LS