VB Filter FROM unbound listbox TO unbound listbox 1

[leicklda]

I have two unbound list boxes "Houses" and "Lots" on form "Main"

The value in Houses is supposed to filter the records shown in Lots

The way I have always done this with no problem is by setting criteria in SQL in the rowsource for Lots. This criteria would be:

SELECT lots.ID, lots.house, lots.Morning, lots.Sun, lots.Loft FROM lots WHERE (((lots.house) Like [forms]![main]![houses]));

I now need to move beyond SQL (I think) because it seems I'm headed for some more advanced criteria specs once I also try to add the filters for the other filter you see such as Morning, Sun, Loft, etc.. But more of that later, the first thing I need to do is figure out how to make the transition from SQL to VB code. The code I have so far is:

Private Sub Search_Click()
Dim strFilter As String
strFilter = "[house] =" & Me!houses
Me.lots.RowSource = "SELECT lots.ID, lots.house, lots.Morning, lots.Sun, lots.Loft FROM lots " & strFilter
lots.Requery
End Sub

This does not work - it says SQL synatx error, and I've played around a lot with the wording. It's like it's looking for a WHERE statement, but I can't do that in VB from what I can tell. I cannot find any previous discussions onhow to apply a filter to an UNBOUND list box (well I found one but that is pretty much it above, and it doesn't work).

Any help would be so much appreciated.

Thanks
Darleen

 

[PWise]

you are almost there but, "where" are you are just missing the word "where"
Private Sub Search_Click()
Dim strFilter As String
strFilter = "[house] =" & Me!houses
Me.lots.RowSource = "SELECT lots.ID, lots.house, lots.Morning, lots.Sun, lots.Loft FROM lots where " & strFilter & ";"
End Sub