Subform Filter by VBA

Hello all,
I am trying to change the filter on a subform via code at the click of a 
button. The subform will always have [Project Manager] as the master and 
child fields, but I want to toggle b/w the following WHERE conditions at the 
click of a button.

[Current Status]= '12. Project Complete'
[Current Status]<>'12. Project Complete'

These will change the data in the sub b/w current and completed projects.

I am sure I can figure out the IF function just fine, but how do I set the 
filter of a subform when clicking a button on the parent?

Me.Subform.Filter seems like it should work but doesn't. Am I missing 
something?

Thanks for all the help in advance.

Adam
1
Utf
8/14/2007 8:22:01 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
5276 Views

Similar Articles

[PageSpeed] 13

You need two lines, try this:

Me.Subform.Form.Filter =  "[Current Status]= '12. Project Complete'"
Me.Subfrom.Form.FilterOn = True 'applies the filter

Remember, when you use Me.Subform you are actually referencing a Subform 
Control (a container for your subform) not the actual subform. So you need to 
use .Form to reference the current form in that control. A Subform Control 
does not have a filter property but the form that it contains does...see what 
I mean?

You can have one Subform Control on a form and change its Source Object and 
Master/Child Link properties to show different subforms within it. This is a 
better way of handling multiple subforms that you want to appear in the same 
place as overlapping controls can give you problems in your forms.

Steve

"F1stman" wrote:

> Hello all,
> I am trying to change the filter on a subform via code at the click of a 
> button. The subform will always have [Project Manager] as the master and 
> child fields, but I want to toggle b/w the following WHERE conditions at the 
> click of a button.
> 
> [Current Status]= '12. Project Complete'
> [Current Status]<>'12. Project Complete'
> 
> These will change the data in the sub b/w current and completed projects.
> 
> I am sure I can figure out the IF function just fine, but how do I set the 
> filter of a subform when clicking a button on the parent?
> 
> Me.Subform.Filter seems like it should work but doesn't. Am I missing 
> something?
> 
> Thanks for all the help in advance.
> 
> Adam
0
Utf
8/15/2007 1:10:00 AM
Steve has given you a good answer, but if you filter the main form as well 
as the subfom, you will run into problems.

Details:
    Incorrect filtering of forms and reports
at:
    http://allenbrowne.com/bug-02.html


-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SteveM" <SteveM@discussions.microsoft.com> wrote in message
news:613F87F9-4D35-42D9-B3C4-5D912E82A0A9@microsoft.com...
> You need two lines, try this:
>
> Me.Subform.Form.Filter =  "[Current Status]= '12. Project Complete'"
> Me.Subfrom.Form.FilterOn = True 'applies the filter
>
> Remember, when you use Me.Subform you are actually referencing a Subform
> Control (a container for your subform) not the actual subform. So you need 
> to
> use .Form to reference the current form in that control. A Subform Control
> does not have a filter property but the form that it contains does...see 
> what
> I mean?
>
> You can have one Subform Control on a form and change its Source Object 
> and
> Master/Child Link properties to show different subforms within it. This is 
> a
> better way of handling multiple subforms that you want to appear in the 
> same
> place as overlapping controls can give you problems in your forms.
>
> Steve
>
> "F1stman" wrote:
>
>> Hello all,
>> I am trying to change the filter on a subform via code at the click of a
>> button. The subform will always have [Project Manager] as the master and
>> child fields, but I want to toggle b/w the following WHERE conditions at 
>> the
>> click of a button.
>>
>> [Current Status]= '12. Project Complete'
>> [Current Status]<>'12. Project Complete'
>>
>> These will change the data in the sub b/w current and completed projects.
>>
>> I am sure I can figure out the IF function just fine, but how do I set 
>> the
>> filter of a subform when clicking a button on the parent?
>>
>> Me.Subform.Filter seems like it should work but doesn't. Am I missing
>> something?
>>
>> Thanks for all the help in advance.
>>
>> Adam 

0
Allen
8/15/2007 1:29:00 PM
Reply:

Similar Artilces:

Unhiding All hidden Worksheets with a Macro/VBA Code
Hi, Can anyone help me please ? Whilst working with an Excel Workbook I often hide all unwanted Worksheets leaving only the ones I need to work on visible. What I would like to do is have a macro or VBA code to click on a button to unhide all hidden worksheets rather repeating Format-Sheet-Unhide to unhide each worksheet. Any help would be greatly appreciated (Excel version Excel 2000) Many thanks Rob -- robertguy 44 Married two kids ------------------------------------------------------------------------ robertguy's Profile: http://www.excelforum.com/member.php?action=getinf...

VBA: Procedure runs fine except when invoked by its shortcut
Hi all! There is a problem that I have had for *years* and which drives me crazy every time I come across it. I really look forward to find out the reason behind it and, if possible, to figure out a solution for good. The problem is as follows. Certain VBA procedures don't work well when I call them using the associated keyboard shortcuts, but run perfectly fine when I execute them using the Excel macro dialog box or from the Visual Basic interface (by either using the F5 (run) or F8 (debug step into)). I have noticed that the problem arises in procedures that have to do with external fil...

DoCmd Apply Filter
I have two forms: frmStudent and frmCourse. What I want to do is link frmStudent and frmCourse through the EmployeeID control to get a filter of each student's courses. Problem: EmployeeID is a string. What I am doing wrong with the below cited? stLinkCriteria = "[EmployeeID] = " & Me![EmployeeID] DoCmd.OpenForm stDocName, , , , acNormal, , stLinkCriteria DoCmd.ApplyFilter , "EmployeeID="", & EmployeeID & """ -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 ...

vba: paste special problems
i'm trying to perform a pastespecial inside a loop for opening multipl files. i.e a as each file is opened i want to copy some cells to th master. when i select skipblanks it brings up source data outta range error, o somethimng like that, ie working on the opened book in the loop stuff up the pastespecial when u have skipblanks?????? anyway to avoid this, i want to assign a unique name to each book as i is opened. How do I do that inside a for loop For f = 1 To UBound(fn) Workbooks.Open fn(f) MsgBox ActiveWorkbook.Name, , "Active Workbook Name:" Staff(f) = ActiveWorkbook.Name...

VBA Dir () function
Hi every one, I use the following code to find and populate a combobox with *.ppt files myfile=Dir(file pathe\+"*.ppt") but this return not only *.ppt file but also *.pptx file ! Anything I'm missing here? Thanks for help. -- Best regards, Edward I believe that is by default. because you used *.ppt it will return all files where the file extension begins with ppt. -- Michael Koerner MS MVP - PowerPoint "Edward" <Edward@discussions.microsoft.com> wrote in message news:7CAEDF5E-0C71-443B-9F25-DB163C2739DC@microsoft.com... > Hi e...

VBA- Application.Wait?
Does PowerPoint not have "Application.Wait" like Excel? Is there another way to pause code for a few seconds in PPT 2003/2007 while a small bat file runs? -Mel On 3/10/10 2:29 AM, Mel wrote: > Does PowerPoint not have "Application.Wait" like Excel? Is there > another way to pause code for a few seconds in PPT 2003/2007 while a > small bat file runs? > > -Mel From Example 8.4 on my Web site (http://www.PowerfulPowerPoint.com/): Sub Wait() waitTime = 5 Start = Timer While Timer < Start + waitTime DoEvents ...

Which Spam filtering application?
At work I am running Outlook 2000 for my corporate email account. The problem is I get about 200 junk/spam emails each and everyday selling blue pills or viagra or offering how to enlarge your male or female body parts! I'd like to run some good spam filtering/stopping/killing application that works for Outlook 2000. Any recommendations? Thanks. James "corporate email account". So, does that mean you use MS Exchange or some other groupware-capable mail server? Or does "corporate" really mean you just use POP3 and SMTP server from whomever is your Internet provide...

Structured Table Refs in VBA
I'm confused about the relationship between tables (using structured references) and ranges. I can work on individual cells from a table column, similar to working with a range, using- Dim oSh As Worksheet Set oSh = ActiveSheet Dim r As Range For Each r In oSh.Range("MyRange[ColumnName]") r.Value = 10 Next But if I try to create a range object from a table, using any of- r = oSh.Range("MyRange") r = oSh.Range("MyRange[#Data]") r = oSh.Range("MyRange[ rowvariable, [ColumnName]]") I think I am fai...

Excel VBA & Web
Hi All, I need to run Macro on the opened web site. Assume I have opened an Web ( Internet Explorer). I navigate the web. Now, If I run the Macro from Excel, It need to activate the already opened Internet Explorer. And do the different activity. Is this possible ? See website below 'vba_corner: Working with Internet Explorer Using VBA' (http://vba-corner.livejournal.com/4623.html) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthrea...

Hide 0 records subform to reveal txtbox underneath
If there are no records in a subform when the underlying query runs, then I wish the subform to be invisible so as to reveal alternate data in a textbox hidden underneath the subform. I have tried all the solutions I could find in these forums, but have been unsuccessful. I have set the subform visible property to no and then tried various VBA solutions in the On Load event. I get the subform to show the correct data when it exists, but when there is no data, there is still a blank white space overlaying the txtbox hidden underneath. Would appreciate some advice. Access 2007 on Win XP. ...

connection filtering RBL logging
I have followed the KB here: http://support.microsoft.com/kb/823866/en-us and I was wondering if there is a way that I can tell if it is working or not. I have the logging turned on but AFAIK this log logs everything (blocked and unblocked). Is there a way that I can look at some logs and it tell me when something is blocked and perhaps an error code? TIA. -Steven- On Mon, 26 Jun 2006 14:42:32 -0500, "Steven Platt" <me@nowhere.com> wrote: >I have followed the KB here: http://support.microsoft.com/kb/823866/en-us >and I was wondering if there is a way that I c...

Find invalid names via VBA
Using VBA, how do I identify invalid dynamic range names? For Example, I would like to flag the following: "TargetRng" is defined as OFFSET(StartPoint, 0, 0, 1, 100) where "StartPoint" is undefined. Thank you for your help If your names always refer to ranges, then maybe something like this: Option Explicit Sub testme() Dim testRng As Range Dim myName As Name For Each myName In ActiveWorkbook.Names Set testRng = Nothing On Error Resume Next Set testRng = myName.RefersToRange On Error GoTo 0 If testRng ...

Excel 4 macro to VBA translation
Where can I find a good reference source where I can lookup and translate Excel 4 macro codes to their VBA equivalence? Thanks for any help. Tom Tom, My .hlp version of the following has a section titled... "Visual Basic Equivalents for Macro Functions and Commands " I assume the .chm download version here will have the same... http://makeashorterlink.com/?K68332CBB Excel 2000 Help File: Running Excel 4.0 Macros Jim Cone San Francisco, USA "Tom" <tclim36@zip.com.au> wrote in message news:BFLSe.28299$Le2.254141@nasal.pacific.net.au... Where can I find a g...

Lookups with Modifier/VBA in GP 9.0
I have a need to place a Vendor ID on the item card (client requirement - not mine). I have no problems defining a field to hold it, or storing and retrieving via VBA/DUOS. I am running into trouble because I want to make a lookup button to link to this so the user can select the Vendor ID from a list. But it seems lookup buttons are not part of what Modifier does. Am I missing something, or am I going to have to do something more with VBA (like ADO) to roll my own lookup form? Thanks To solve this problem try this. Define a global variable to hold the selected vendor id. Create a re...

Possible to trigger rules from MSN through VBA?
I understand that it is not possible to apply rules to email that comes in from MSN. In my case, my Outlook 2003 knows about two email sources, a POP3 account and an MSN account. Is it possible to write a VB macro that, upon delivery of an MSN email message, transfers that email message to the top of the POP3 email tree and have the rules execute on it from there? -- Richard Lewis Haggard www.Haggard-And-Associates.com ...

OutlookExpress filters files with certain extensions (like .ASP, .PDF, etc.),
I have OutlookExpress 6. When I receive files with certain extensions (like ..ASP, .PDF, etc.), it says "OE removed access to the following unsafe attachments in your mail: filename.PDF". The file does not contain virus, but it still filters any files with this extension and other extensions like ..ASP. This did not used to happen before, just start happening recently. How can I fix this problem ? Thank you. Tools->Options->Security. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) vi...

Inbound email filtering beyond Rules
I'd like to understand how Outlook 2010 Rules have improved over v2003. Several years ago I found limitations in Outlook 2003 Rules. Outlook would behave weirdly and crash if there were too many rules. It also doesn't deal well with rules for non-standard headers. My solution was to create a series of VBA scripts that processed inbound emails against an external file of rules. Each rule in the file identifies a header, a condition, a value to match the condition, and the target folder when a match is made. Examples: SenderEmailAddress|includes|linkedin.com| \\Personal Folders\I...

Dynamic range across sheets and Advanced filter
Hi, I have two sheets of data with same structure (data more than one sheet can carry), and want to use advanced filter to copy data to a new location. How to make advanced filter List Range includes these sheets? Thanks. Regards, Angus Hi Angus You can't make the range for AF span 2 worksheets. You will need to carry out your extraction form each sheet separately, using the same criteria, then combine the 2 extracts to another sheet. -- Regards Roger Govier "Angus" <Angus@discussions.microsoft.com> wrote in message news:0B14E27C-F722-4636-B82D-AE98903BAC9B@mic...

setting gridlines in VBA?
Maybe I didn't name the subject correctly. In general, is there any good documentation on interfacing to excel from C++? I can get a few things to work but others won't compile. Maybe I have incompatible dll's? Is this the right newsgroup? I've been able to cause VC++ 6.0 (no mfc) to create a chart in excel. Eg, ..... chart->ChartWizard( (Excel::Range*) range, // source (long) Excel::xlXYScatter, // Gallery 6L, // Format (1-10) (long) Excel::xlColumns, // PlotBy 1L, // 1 CategoryLabels 0L, // 0 SeriesLabels 2L, title, "Time", &quo...

vba to print Form from Excel ?
I've got a for for entering data into a spreadsheet. Can you print the Form as it is using Excel VBA ? If so, any code snippits that may give me the idea? thanks for any help. tmb You can use the PrintForm method: Private Sub cmdPrint_Click() frmOrders.PrintForm End Sub tmb wrote: > I've got a for for entering data into a spreadsheet. > > Can you print the Form as it is using Excel VBA ? > > If so, any code snippits that may give me the idea? > > thanks for any help. > > tmb > > -- Debra Dalgleish Excel FAQ, Tips & Book List h...

#Error in subform foreign key
Hi all, I am getting "#Error" in my foreign key id field for two subforms, but not the third. As far as I can tell each is setup in their respective tables similarly. I have tried deleting, changing thpe data type, I've even re-created the whole database (re-created suspect tables and imported everything else). Whenever I try to add values in any field on the subform I get "The value you entered isn't valid for this field..." I'm at a loss. Thanks in advance, Nick "NickX" <nicknnator@aol.com> wrote in message news:1176403733.363533.24828...

HR Filters should be applied to HR Applicants
HR Filters should be applied to Applicants in the same manner as they are to Employees. For example, if I have five applicants that are setup with Management as their department and have applied filters – selecting other departments to view but not management – my five Management applicants should not be able to be viewed by the user that the filters were applied to . ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do no...

Open password protected ppt file using excel vba
Dim pptfile As Object On Error Resume Next Application.DisplayAlerts = false Set pptfile = CreateObject("powerpoint.application") pptfile.Visible = True Set pshow = pptfile.Presentations.Open(FileItem, Password:="") With pshow .Password = "test" ' ppt password .SaveAs FileItem .Close End With pptfile.Quit Application.DisplayAlerts = True End If How can i pass password from the vba code. If the file does not have a password then it catches error. Any...

Before Query filter not working
I have an Ado datasource in GP and would like to filter it in the Before Query script. Nothing seems to work. The query returns the same number of rows. I'm using a variable that I'm setting in the before integration script. The variable is definitely getting set and is being retrieved in the before query script. What am I doing wrong? Code is below Thanks in advance, Kevin Vogler I've tried this with just a simple Select * query (no where) Dim rID rID = GetVariable("Location") dim crit crit = "vwCustomer.CMimp_ROW_ID = " & rID & "" ...

filter #5
Sum gives the total of all numerical values even when autofilter is on Subtotal gives the correct sum for the filtered rows How can I have a list of all functions that works with autofilter is on. Only subtotal, if you need to do countif etc on filtered lists you can use the method here =SUMPRODUCT(--($K$2:$K$560=1),(SUBTOTAL(3,OFFSET($K$2,ROW($K$2:$K$560)-MIN(ROW($K$2:$K$560)),,)))) will count the visible cells where K2:K560 equals 1 -- Regards, Peo Sjoblom "Khalil Handal" <handalk@stthom.edu> wrote in message news:ew6RtMmHJHA.1364@TK2MSFTNGP04.phx.gbl... >...