469,621 Members | 2,244 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Applying a filter on a Subform. Combo box Filter

What I am trying to do is have a combo box which allows me to filter for a certain field in a subform.

I can't think how to do this and have tried with the follwoing code. Basically the field in question is :

POL_Change_Allocated - This is a yes/no tick box. I want to filter for all forms where the tick hasn't been applied. (Am I right in thinking that this means this field is no unless ticked?)

The main form feeds from table OCP Base Tables
The Subform feeds from table POL Actions

These two tables are both linked on the form using OCP Ref

I started to write the code below, but realised this would just return non blanks, whereas I am looking to return all forms where "no" is selected. Also I started to get bogged down in the code, but as the filter needs to be on the subform I didnt think I could apply a normal filter.

I am new to both access and VBA so please can you explain what on earth I am doing wrong.
Expand|Select|Wrap|Line Numbers
  2. Private Sub Pending_Changes_Click()
  3. Dim strSQL As String
  4. If IsNull(Me.{POL_Change_Allocated) Then
  5.     ' If the combo is Null, use the whole table as the RecordSource.
  6.     Me.RecordSource = "OCP_Base_tables"
  7. Else
  8.     strSQL = "SELECT DISTINCTROW tblOCP_Base_Tables.* FROM tblOCP_Base_Tables " & _
  9.         "INNER JOIN tblPOL_Actions ON " & _
  10.         "tblOCP_Base_Tables.OCP_Ref = tblPOL_Actions.OCP_Ref " & _
  11.         "WHERE tblPOL_Actions.POL_Change_Allocated = " & Me.Pending_Changes & ";"
  12.     Me.RecordSource = strSQL
  13. End If
  14. End Sub
Jan 10 '08 #1
2 2741
I thought the isnull would work as the filter I am trying to apply is one with a tick in it. If that makes sense.
Jan 10 '08 #2
Just for information what I am trying to do is set a filter to return forms that are yet to be completed. therefore the checkbox hasn't been ticked.
Jan 10 '08 #3

Post your reply

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

Similar topics

2 posts views Thread by cefrancke | last post: by
1 post views Thread by jjchristiansen | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.