Filter subform by combo box...

[holden_1]

Hi,

I've tried to reverse engineer the "Pine" example of manipulating the filter arguement for a subform using multiple comboboxes, but have failed. "13 - Type Missmatch Error". i also tried the earlier method of manipulating the recordsource but if i chose more then one combo box to filter by i got a "FROM arguement syntax" error...

My subform is based on a query... please have a look at the code below and tell me wher i've boo boo'd, or does anyone have an idiots guide to this?

Thanks for any help, Rob

Private Function SCARsearch()
On Error GoTo Error_SCARsearch

Dim FilterClause As String

'1st Combo - SCAR
If Len(Me.cbo_SCAR.Value) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & "AND"
FilterClause = FilterClause & "[SCAR_ID]='" & Me.cbo_SCAR.Value & "'"
End If
'2nd Combo - Engineer
If Len(Me.cbo_Engineer.Value) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & "AND"
FilterClause = FilterClause & "[Engineer_ID]='" & Me.cbo_Engineer.Value & "'"
End If
'3rd Combo - Supplier
If Len(Me.cbo_Supplier.Value) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & "AND"
FilterClause = FilterClause & "[Supplier_number]='" & Me.cbo_Supplier.Value & "'"
End If
'Place our created Filter Criteria into the Filter property of SubForm.
Forms(frm_close_scars)(sfrm_close_scar).Form.Filter = FilterClause
'Turn on the Filter
Forms(frm_close_scars)(sfrm_close_scar).Form.FilterOn = True

Exit_SCARsearch:
Exit Function

Error_SCARsearch:
MsgBox "SCARsearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"SCAR Search Error"
Resume Exit_SCARsearch
End Function

 

[holden_1]

Get same error message with alternative method: if i change a combo or click the reset button... 13 - type, mismatch

please help!

Private Function SCARsearch()
On Error GoTo Error_SCARsearch

Dim StrgSQL As String
Dim WhereClause As String

StrgSQL = "SELECT * FROM qry_close_scar"

'1st Combo - Engineer - Assuming the Table Field 'Engineer' is of a Text DataType
If (Me.cbo_Engineer.Value) <> Null Then
If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
WhereClause = WhereClause & "[Engineer_ID]=" & Me.cbo_Engineer.Value
End If

'2nd Combo - Supplier - Assuming the Table Field 'Supplier_number' is of a Text DataType
If Len(Me.cbo_Supplier.Value & "") > 0 Then
If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
WhereClause = WhereClause & "[Supplier_number]='" & Me.cbo_Supplier.Value & "'"
End If

'3rd Combo - Stock Location - Assuming the Table Field 'Location' is of a Text DataType
'If Len(Me.Location.Value & "") > 0 Then
' If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
' WhereClause = WhereClause & "[Location]='" & Me.Location.Value & "'"
'End If

'And the WHERE clause string to our SELECT query String
If WhereClause <> "" Then StrgSQL = StrgSQL & WhereClause

StrgSQL = StrgSQL & ";"

'Place our created SELECT Query into the Record Source property of SubForm.
Forms(frm_close_scars)(sfrm_close_scar).Form.RecordSource = StrgSQL

Exit_SCARsearch:
Exit Function

Error_SCARsearch:
MsgBox "SCARsearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"SCAR Search Error"
Resume Exit_SCARsearch
End Function