Filter form in query

[thart21]

Can't seem to locate a similar situation in the forum for this problem. I have a form based on "qryProjects". I have another form "Menu" which has cboType, lstStatus and lstSubtype. I thought that I could select criteria from each and, with a command button, have the form open up filtered with the three criteria. In qryProjects pane I have as the criteria for each of the three fields:

[Forms]![Menu].[lstStatus] - Value list
Bound column = 1

[Forms]![Menu].[cboType]
SQL in cboType = SELECT DISTINCT [SubType].[Type] FROM SubType;
Bound column = 1

[Forms]![Menu].[lstSubtype] = this is filtered on the After Update of cboType
SELECT [SubTypeID], [Subtype] FROM Subtype;
Bound column = 1

I have them on separate lines in the query. When I run the query, it only filters on cboType and not the others. If I place the criteria all on the same criteria line, I get nothing.

Is my criteria in the wrong place? I was originally trying to make the form completely "unbound" and build a SQL string to pull the records but don't know if that is possible. My purpose is to eliminate macros that I was using as it took over 2 minutes to open the form with the BE residing on a shared network drive.

Would appreciate any ideas/suggestions.

Thanks,

Toni

 

[thart21]

Syntax error

Getting closer to what I need but am getting an error when running the sql statement.
Runtime error 3075
Syntax error in query expression "SELECT * FROM qryProjects WHERE (Subtype='10') AND (status="Active")

Subtype is is an integer and status is text. Can anyone see where I am going wrong with this?

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 & "(Subtype =" & Chr(39) & Me![lstSubtype].Column(0, varItem) & Chr(39) & " 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(34) & Me![lstStatus].Column(0, varItem) & Chr(34) & " OR "


Next varItem
End If

strWhere = Left(strWhere, Len(strWhere) - 4) & ")"
strCriteria = strSQL & " " & strWhere
MsgBox strCriteria
DoCmd.OpenForm "Projects", acNormal, , strCriteria
End Sub

Would appreciate any tips.

Thanks!

 

[thart21]

UPDATE: Changed this line:

strWhere = strWhere & "(Subtype =" & Chr(39) & Me![lstSubtype].Column(0, varItem) & Chr(39) & " OR "

To:
strWhere = strWhere & "subtypeid =" & Me![lstSubtype].Column(0, varItem) & " OR " since the name of the field on my form "Projects" is subtypeid and is a number.

Still getting the 3075 runtime error but the SQL looks correct (????)