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

Applying a filter on a Subform. Combo box Filter

P: 41
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
  1.  
  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
Share this Question
Share on Google+
2 Replies


P: 41
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

P: 41
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.