populating listboxes

[skate]

I have a listbox that is populated depend on my search criteria. Unfortunately it doesn't work. I've also made a button called ViewAll so the user can view all the files if they so choose instead of seeing the search results. I figure if i can get this working the rest would be easier to figure out. I originally had the rowsource as seen by 'strSQL= "SELECT.... but realized I can just use the table as a rowsource.

why does this not work?

Private Sub ViewAll_Click()
Dim strSQL As String
'strSQL = "SELECT Company.CIndex, Company.Company, Company.Location, Company.[Pricing Info] FROM Company"
strSQL = Me.lstResults.RowSource
strSQL = strSQL & "ORDER BY Company.Company"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtSearch.SetFocus
Me!FrameSortBy.Value = 0
End Sub

 

[Fornatian]

You can't change order a table by just selecting the current rowsource and adding a SQL orderby statement to it.

This is because the rowsource is not an SQL statement merely a reference to the datatable to use as the rowsource.

If your rowsource is an SQL statement then your code has an error, the one I see is that you have'nt put a space prior to the ORDERBY statement, it should be " ORDERBY TableOrQuery.Field"

 

[WayneRyan]

skate,


strSQL = Me.lstResults.RowSource
strSQL = strSQL & "ORDER BY Company.Company"
Me!lstResults.RowSource = strSQL

On initial look, I think that you need a space before the
ORDER BY.

Try setting a breakpoint and viewing Me!lstResults.RowSource
in the Immediate window.

Wayne

 

[skate]

wow, thanks that was it.
then I think there must be something wrong w/ the rest of my code? this part doesn't work either

Dim txtSearchString As Variant
Dim strSQL As String
txtSearchString = Me![txtSearch].Text
strSQL = "SELECT Company.CIndex, Company.Company, Company.Location, Company.[Pricing Info] FROM Company"
strSQL = strSQL & "WHERE ((Company.Location) Like '" & txtSearchString & "*') "
strSQL = strSQL & " ORDER BY Company.Location"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery

 

[skate]

nevermind
it all has to do w/ adding the space!
this has been driving me crazy for so long!

thanks a lot!

(now I'm curious why my other form, which is similar, works w/o the spaces)