By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Apply form filter to a underlying query

100+
P: 121
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

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Error_EquipFilter
  2.  
  3.    Dim FilterClause As String, D As Long
  4.  
  5. D = Me.DirectionGrp.Value
  6.  
  7.  
  8. If Len(Me.CboCat.Value & "") > 0 Then
  9.     If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  10.     FilterClause = FilterClause & " Category =" & Me.CboCat.Value
  11. End If
  12.  
  13. If Len(Me.CboModel.Value & "") > 0 Then
  14.     If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  15.     FilterClause = FilterClause & " Model ='" & Me.CboModel.Value & "'"
  16. End If
  17. & "[Treatment]='" & Me.Treatment.Value & "'"
  18.  
  19. If Len(Me.CboManu.Value & "") > 0 Then
  20.     If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  21.     FilterClause = FilterClause & " Manufacturer ='" & Me.CboManu.Value & "'"
  22. End If
  23.  
  24.    CurrentFilter = FilterClause: FilterClause = ""
  25.  
  26.     Forms("frmEquipBooking").Form.Filter = CurrentFilter
  27.     Forms("frmEquipBooking").Form.FilterOn = True
  28.  
  29. Exit_EquipFilter:
  30.    Exit Function
  31.  
  32. Error_EquipFilter:
  33.    MsgBox "EquipFilter Function Error" & vbCr & vbCr & _
  34.           Err.Number & " - " & Err.Description, vbExclamation, _
  35.           "Equipment Filter Error"
  36.    Resume Exit_EquipFilter
  37.  
Any help is much appreciated

Regards Phill
Oct 22 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
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?
Oct 22 '09 #2

NeoPa
Expert Mod 15k+
P: 31,433
What Chip said.

I'm equally confused by your question.
Oct 23 '09 #3

100+
P: 121
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
Oct 27 '09 #4

NeoPa
Expert Mod 15k+
P: 31,433
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 :
    Expand|Select|Wrap|Line Numbers
    1. strSQL = "SELECT *" & vbCrLf & _
    2.          "FROM   [YourQuery]" & vbCrLf & _
    3.          "WHERE  " & strFilter
.
Oct 27 '09 #5

NeoPa
Expert Mod 15k+
P: 31,433
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.
Oct 27 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.