469,627 Members | 936 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,627 developers. It's quick & easy.

Apply form filter to a underlying query

121 100+
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
5 4968
ChipR
1,287 Expert 1GB
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
32,202 Expert Mod 16PB
What Chip said.

I'm equally confused by your question.
Oct 23 '09 #3
phill86
121 100+
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
32,202 Expert Mod 16PB
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
32,202 Expert Mod 16PB
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.

Similar topics

16 posts views Thread by Nhmiller | last post: by
9 posts views Thread by David White | last post: by
7 posts views Thread by Ceebaby via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.