How do i filter an UNBOUND listbox in VB code?

[leicklda]

Hi everyone, I need help.

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). I see lots of Me.Filter solutions, but because this box is unbound, that doesn't work.

Any help would be so much appreciated.

Thanks
Darleen

 

[BillPower]

Try this:

Dim strSQL As String
strSQL = "SELECT lots.ID, lots.house, lots.Morning, lots.Sun, " & _
"lots.Loft FROM lots WHERE (((lots.house)='" & Me!house & "'));"
Me.lots.RowSource = strSQL