Apply form filter to a underlying query

[phill86]

Hi,

I have a form that I have applied a filter to by using combo boxes which works fine.

Is there a way to apply that filter to the forms underlying query

Here is the code that I use to filter the form

On Error GoTo Error_EquipFilter
 
   Dim FilterClause As String, D As Long
 
D = Me.DirectionGrp.Value
 
 
If Len(Me.CboCat.Value & "") > 0 Then
    If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    FilterClause = FilterClause & " Category =" & Me.CboCat.Value
End If
 
If Len(Me.CboModel.Value & "") > 0 Then
    If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    FilterClause = FilterClause & " Model ='" & Me.CboModel.Value & "'"
End If
& "[Treatment]='" & Me.Treatment.Value & "'"
 
If Len(Me.CboManu.Value & "") > 0 Then
    If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    FilterClause = FilterClause & " Manufacturer ='" & Me.CboManu.Value & "'"
End If
 
   CurrentFilter = FilterClause: FilterClause = ""
 
    Forms("frmEquipBooking").Form.Filter = CurrentFilter
    Forms("frmEquipBooking").Form.FilterOn = True
 
Exit_EquipFilter:
   Exit Function
 
Error_EquipFilter:
   MsgBox "EquipFilter Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Equipment Filter Error"
   Resume Exit_EquipFilter
 

Any help is much appreciated

Regards Phill

[ChipR]

What do you mean when you say "apply that filter to the form's underlying query"? From my perspective, that's exactly what you're doing. Did you want to save the filter permanently as part of the query?

[NeoPa]

What Chip said.

I'm equally confused by your question.

[phill86]

Hi,

Thanks for the response and apologies for not getting back to you sooner.

I am using that query elsewhere in a recordset and when the query is run from within the recordset the filter that is applied to the form does not affect the query that is in the recordset.

Which I guess would make sense I just need to know how to apply the filter that I have applied on the forrm to the query on its own.

Hope this makes more sense sorry for any confusion caused.

Regards Phill

[NeoPa]

That rather depends on how you're using the query in your Recordset code.

Generally you can do it one of two ways :

1. Update the WHERE clause of the query to include the extra filtering.
2. Encapsulate the query in some simple SQL that selects * from your query.
   Assuming your filter has already been prepared in a string strFilter then :
   

   strSQL = "SELECT *" & vbCrLf & _
            "FROM   [YourQuery]" & vbCrLf & _
            "WHERE  " & strFilter

.

[NeoPa]

Phill, can I make a friendly suggestion relating to coding style, particularly when posting in forum :
It's generally worth starting new VBA commands on a separate line in your modules, rather than using the line separation character (:). It's fine for use in the Immediate Pane of the VBA IDE (See Debugging in VBA), but it can cause confusion when used elsewhere.

I'm referring of course to your line #24.