filter subform with combo boxes

[hedge]

i have searched the forum and found this example similar to what i am trying to achieve.
http://www.access-programmers.co.uk/forums/showthread.php?t=157601

i have a search form (consignment Search) with 5 combo boxes i would like to use to filter a subform (Consignments Details). I have copied and paste the example from the forum however the filter does not seem to work. any assistance would be greatly appreciated. A copy of my database is attached.



Private Function StockSearch()
On Error GoTo Error_StockSearch

Dim FilterClause As String, D As Long

'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value

'1st Combo - Container number - Assuming the Table Field 'Grade' is of a Text DataType
If Nz(Me.Container_Number.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Container_Number]='" & Me.Container_Number.Value & "'"
End If

'2nd Combo - Order Number - Assuming the Table Field 'Treatment' is of a Text DataType
If Nz(Me.Order_Number.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Order_Number]='" & Me.Order_Number.Value & "'"
End If

'3rd Combo - Job Number - Assuming the Table Field 'Location' is of a Text DataType
If Nz(Me.Job_number.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Job_Number]='" & Me.Job_number.Value & "'"
End If

'4th Combo - Product Value - Assuming the Table Field 'Drying' is of a Text DataType
If Len(Me.Product.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Product]='" & Me.Product.Value & "'"
End If

'5th Combo - Discharge Port - Assuming the Table Field 'Finish' is of a Text DataType
If Len(Me.Discharge_Port.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Discharge_Port]='" & Me.Discharge_Port.Value & "'"
End If


'Fill this Form wide variable so that it can be used for
'the Report.
CurrentFilter = FilterClause: FilterClause = ""

'Place our created Filter Criteria into the Filter property of SubForm.
Forms("Consignment Search")("Consignments Details").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("Consignment Search")("Consignments Details").Form.FilterOn = True

Exit_StockSearch:
Exit Function

Error_StockSearch:
MsgBox "StockSearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Stock Search Error"
Resume Exit_StockSearch
End Function

 

[JamesMcS]

If it was me, I'd create some text boxes that took the value from the relevant column of each combo box (i.e. text box control source = combo box.column(x)), then set the linkmasterfields property of your subform to each of the text boxes, then linkchildfields to the matching fields in your subform's record source, in the same order as your linkmasterfields property.

I'm not sure but that might mean you have to select a value in all the combo boxes... worth a try anyway.

 

[Trevor G]

Welcome ot the forum,

I use the following code to filter a sub form. You would need to adapt it to your control names and form names.

I have also included a reset option.

Private Sub cmdFilterNew_Click()
'This code will look to filter records in a subform
'It is using 8 string commands
'The form has 8 combo controls
'It is also using individual if statements
'Finally applying the filter on the subform
'Created by Trevor G 06 January 2010
Dim strUWYear As String
Dim strBUG As String
Dim strTerritory As String
Dim strPortFolio As String
Dim strPolicyRef As String
Dim strInsuredName As String
Dim strTrade As String
Dim strUnderWriter As String

If IsNull(Me.cboUWYear.Value) Then
strUWYear = "Like '*'"
Else
strUWYear = "='" & Me.cboUWYear.Value & "'"
End If
If IsNull(Me.cboBUG.Value) Then
strBUG = "Like '*'"
Else
strBUG = "='" & Me.cboBUG.Value & "'"
End If
If IsNull(Me.cboTerritoryName.Value) Then
strTerritory = "Like '*'"
Else
strTerritory = "='" & Me.cboTerritoryName.Value & "'"
End If
If IsNull(Me.cboPortFolio.Value) Then
strPortFolio = "Like '*'"
Else
strPortFolio = "='" & Me.cboPortFolio.Value & "'"
End If
If IsNull(Me.cboPolicyNumber.Value) Then
strPolicyRef = "Like '*'"
Else
strPolicyRef = "='" & Me.cboPolicyNumber.Value & "'"
End If
If IsNull(Me.cboInsuredName.Value) Then
strInsuredName = "Like '*'"
Else
strInsuredName = "='" & Me.cboInsuredName.Value & "'"
End If
If IsNull(Me.cboTrade.Value) Then
strTrade = "Like '*'"
Else
strTrade = "='" & Me.cboTrade.Value & "'"
End If
If IsNull(Me.cboUnderWriter.Value) Then
strUnderWriter = "Like '*'"
Else
strUnderWriter = "='" & Me.cboUnderWriter.Value & "'"
End If
Me.frmCombinedSearchEngineFilter.Form.Filter = "[UWYear]" & strUWYear & " AND [BUG]" & strBUG & " AND [Location1]" & strTerritory & " AND [Reporting Class 2]" & strPortFolio _
& " AND [Policy Ref]" & strPolicyRef & " AND [INSURED NAME]" & strInsuredName & " AND [Trade]" & strTrade & " AND [UWR]" & strUnderWriter
Me.frmCombinedSearchEngineFilter.Form.FilterOn = True
End Sub

Private Sub cmdReset_Click()
'*************************************************
'VBA Code created by Trevor G 06 January 2010
'Contact via mobile
'Email
'*************************************************
Me.cboBUG.Value = Null
Me.cboInsuredName.Value = Null
Me.cboPolicyNumber.Value = Null
Me.cboPortFolio.Value = Null
Me.cboTerritoryName.Value = Null
Me.cboTrade.Value = Null
Me.cboUnderWriter.Value = Null
Me.cboUWYear.Value = Null
Me.Form.Requery
Me.frmCombinedSearchEngineFilter.Requery
Me.frmCombinedSearchEngineFilter.SetFocus
Me.frmCombinedSearchEngineFilter.Form.FilterOn = False
End Sub

 

[hedge]

Thanks for the help.

1/james tried the text boxes your right each box had to be filled in so that did not suit.
2/ Trevor tried you code, just getting stuck on the red area as a compile error


Private Sub Command28_Click()
'This code will look to filter records in a subform
'It is using 8 string commands
'The form has 8 combo controls
'It is also using individual if statements
'Finally applying the filter on the subform
'Created by Trevor G 06 January 2010
Dim Container_Number As String
Dim Order_Number As String
Dim Job_Number As String
Dim Product As String
Dim Discharge_Port As String

If IsNull(Me.Container_Number.Value) Then
Container_Number = "Like '*'"
Else
Container_Number = "='" & Me.Container_Number.Value & "'"
End If
If IsNull(Me.Order_Number.Value) Then
Order_Number = "Like '*'"
Else
Order_Number = "='" & Me.Order_Number.Value & "'"
End If
If IsNull(Me.Job_Number.Value) Then
Job_Number = "Like '*'"
Else
Job_Number = "='" & Me.Job_Number.Value & "'"
End If
If IsNull(Me.Product.Value) Then
Product = "Like '*'"
Else
Product = "='" & Me.Product.Value & "'"
End If
Me.Consignment_Search.Form.Filter = "[Container_Number]" & Container_Number & " AND [Order Number]" & Order_Number & " AND [Job Number]" & Job_Number & " AND [Product]" & Product & " AND [Discharge_Port]" & Discharge_Port
Me.Consignment Search.Form.FilterOn = True
End Sub
Private Sub cmdReset_Click()
'*************************************************
'VBA Code created by Trevor G 06 January 2010
'Contact via mobile
'Email
'*************************************************
Me.Container_Number.Value = Null
Me.Order_Number.Value = Null
Me.Job_Number.Value = Null
Me.Product.Value = Null
Me.Discharge_Port.Value = Null
Me.Form.Requery
Me.Consignment_Search.Requery
Me.Consignment_Search.SetFocus
Me.Consignment_Search.Form.FilterOn = False
End Sub

 

[Trevor G]

You aren't applying the form name

Me.Consignment_Search.Form.Filter = "[Container_Number]" & Container_Number & " AND [Order Number]" & Order_Number & " AND [Job Number]" & Job_Number & " AND [Product]" & Product & " AND [Discharge_Port]" & Discharge_Port
Me.Consignment Search.Form.FilterOn = True

shouldn't it be the form name consignment_details