473,839 Members | 1,375 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Applying a filter on a Subform. Combo box Filter

41 New Member
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_Allo cated - 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
2 2906
jambonjamasb
41 New Member
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
jambonjamasb
41 New Member
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

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

Similar topics

2
2853
by: cefrancke | last post by:
I have a form (no underlying record set) that has two separate sub-forms on it. Each sub-form has data from two different tables. Above each sub-form there is one unbound combo box with a SQL record source that returns an ID field and a Text field. Next to each combo box is two buttons, one that 'Applys' the filter of the sub-form to the value in the combo box and one that 'Clears' the form's current filter. When the click event of the...
1
1927
by: jjchristiansen | last post by:
I have a form in which I am trying to filter a query using up to 6 combo bixes. The problem is, unless all 6 combo boxes are selected properly, nothing will apear in the Subform. I would like to be able to see the entire query until I select a filter. Essentially, if no dropdowns selected, I want to show all, if Box 1 selected, filter by child field by Box 1, ignore rest, etc.
4
3578
by: MS | last post by:
I'm having trouble applying a filter to a subform. I create a String in a Module based on various selections on the form. Clicking a button on the "stand alone form" that changes the filter property string to the string from the module works fine. As soon as I put the form on the "main form" (thereby coverting it to a sub form) things no longer work. Of course the record source for the sub form is no longer a table or query, but the...
3
12510
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a continuous form. Each staff member is linked to a Service through a many-to-many relationship, using a junction table called jctStaffServices. I would like to place a Combo Box in frmStaff where you can 'filter' staff by the Service (i.e. ServiceName)...
9
15850
by: natwong | last post by:
Hi All, I'm a newbie in terms of Access and some of its functionality . I've been stuck on this problem for a couple days, even after searching the Web, etc. Currently I have five combo boxes (actually list boxes) that are multiselects in my main form. I need to use these combo boxes to filter a subform within my main form. My combo boxes are as follows: 1. A - 4 select options 2. B - 10 select options 3. C - 4 select options
5
1526
by: favor08 | last post by:
I have a database and I am using a function that captures the shortname and displays it on the mainMenu screen. What I want to do is filter the information on the "past due" screen based on the person who has just open the database so that they can see just their items. The shortname is stored in a table called "TShortNames" The shortname ties to an 4 character "Opid". This same "opid' is in the main "TARA" table that stores all the...
1
1552
by: jcf378 | last post by:
Is it possible to set a main-form with an embedded subform to "Filter by Form", subsequently enter the desired variables in fields in BOTH the main-form and subform, and then save the resulting filter as a single query? or, do the variables entered on the main form need to be saved as its own individual query, and the variables entered on the subform saved as a separate query. I ask because I would ideally like to take a single query,...
2
4268
by: patriciashoe | last post by:
Greeting All I have several public variables which I use to build a filter string that I would like to use when I open a form and several associated sub forms. I have set the value of the combo box on one of the subforms with this variable. I have not yet figured a way to have the update event code on the combo box run automatically, so I put the following code in the open statement of the main subform. Here is some current code: ...
1
2869
by: kinglioness | last post by:
Hello All, I am new to this forum so I apologize if this quesiton was asked already. Although i did search but i couldn't find. I have 2 combo boxes one unbound and the other bound they are on the main form. One (combo box) filters the other. I would like for both boxes to filter the subforms which by the way are on tab controls I have 8 pages. I am very new to vba, but I am trying to get a code that will filter whatever subform is...
0
9697
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10649
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10295
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7829
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7018
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5867
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4487
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.