Multiple listboxes and open form

[hockeylvr]

I am very close to this working but am hung up on a syntax error (Run time error 3075 in query expression). Would someone please take a look at my code and see where I could be going wrong? "qryProjects" is the recordsource for the form "Projects". Is it even possible to filter a form based on a query that is a recordsource?

My SQL statement is appearing like this:

SELECT * FROM qryProjects WHERE (subtypeid = 9) AND (status='Active').
I have tried using both ' & " around "Active".

Code:
Private Sub cmdTest_Click()
Dim strWhere As String
Dim varItem As Variant
Dim strSQL As String
Dim strCriteria As String

strSQL = "SELECT * FROM qryProjects"
strWhere = "WHERE" & "("

If Me.lstSubtype.ItemsSelected.Count > 0 Then
For Each varItem In Me![lstSubtype].ItemsSelected
strWhere = strWhere & "subtypeid =" & Me![lstSubtype].Column(0, varItem) & " OR "


Next varItem
End If

strWhere = Left(strWhere, Len(strWhere) - 4) & ") AND ("

If Me.lstStatus.ItemsSelected.Count > 0 Then
For Each varItem In Me![lstStatus].ItemsSelected
strWhere = strWhere & "status=" & Chr(39) & Me![lstStatus].Column(0, varItem) & Chr(39) & " OR "

'strWhere = strWhere & ",'" & Me.StatusList.Column(0, varItem) & "'"

Next varItem
End If

strWhere = Left(strWhere, Len(strWhere) - 4) & ")"

strCriteria = strSQL & " " & strWhere

MsgBox strCriteria
DoCmd.OpenForm "Projects", acNormal, , strCriteria

End Sub

Thanks in advance for any help.

 

[PHV]

DoCmd.OpenForm "Projects", acNormal, , Mid(strWhere, 6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hockeylvr]

Thanks PHV - that was it! I'll be researching that "Mid" part to better understand.