Filter eliminating records without and detail records

I need a filter to be able to hide records that don't have any records in the 
detail form.  My example is Member Master and Event Detail.  If there is no 
data in the Event Detail, I need to hide that Member Master record.
0
Utf
2/21/2008 6:57:02 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
759 Views

Similar Articles

[PageSpeed] 47

Change the Can Shrink property in the form to yes.

"Jeannie" wrote:

> I need a filter to be able to hide records that don't have any records in the 
> detail form.  My example is Member Master and Event Detail.  If there is no 
> data in the Event Detail, I need to hide that Member Master record.
0
Utf
2/21/2008 8:29:00 PM
I think you have a main form bound to the [Member Master] table, and a 
subform bound to the [Event Detail] table, and the two tables are linked by 
a [MemberID] field? Now you want to filter the main form to eliminate 
members who don't have any event details?

You could do that with a subquery as the filter.
Something like this:

    Dim strWhere As String
    strWhere = "EXISTS (SELECT [Member ID] FROM [Event Detail] " & _
    "WHERE [Event Detail].[Member ID] = [Member Master].[Member ID])"
    Me.Filter = strWhere
    Me.FilterOn = True

For more information, see:
    Filter a Form on a Field in a Subform
at:
    http://allenbrowne.com/ser-28.html

If subqueries are a new concept, here's an introduction:
    http://allenbrowne.com/subquery-01.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.

"Jeannie" <Jeannie@discussions.microsoft.com> wrote in message
news:D270CEB9-19DA-4789-AFE7-7DDEE32585DC@microsoft.com...
> I need a filter to be able to hide records that don't have any records in
> the detail form.  My example is Member Master and Event Detail. If there
> is no data in the Event Detail, I need to hide that Member Master record. 

0
Allen
2/22/2008 2:04:27 AM
Reply:

Similar Artilces:

Help! on filtering a report using a form & query
Hi. I've got a database with lookup fields. I have a form, a query and a report whose record source is the query. I want the user to select the lookup field from a drop down combo box And then run the query. If I change the query so the the criteria reads: [Forms]![frmCloseOutFilter]![txtCollection] and then run it, it will prompt me for the collection, I type a "3" and I get the desired results. It also works If I then run the report separately. But, If I try to run it from the form, I select my collection (It's a two column combo box, the first column has a width of 0...

How to Reply without resending attachments?
Outlook 2002 WinXP Pro An associate emails 10 jpg's he wants on his website. I want to hit reply to tell him I got them and they'll be on his site promptly. How do I do that without sending his jpg's back to him? Thanks for any help! Ed if you hit just reply, he won't get them. If you forward he will >-----Original Message----- >Outlook 2002 >WinXP Pro > >An associate emails 10 jpg's he wants on his website. > >I want to hit reply to tell him I got them and they'll be on his site >promptly. > >How do I do that without sending his j...

There is any way to post here without being insulted every time?
“Great minds discuss ideas; Average minds discuss events; Small minds discus people.� Vanilla, You can take the good with the bad or simply put posters in a kill file where you will not see their messages. What I have found is that nearly 100 percent of people who respond really are nice people who are willing to do all they can to help, but sometimes forget that when answering. I don't know why and cannot do anything about it, but I still come here for advice and knowledge. Mike Vanilla wrote: > “Great minds discuss ideas; Average minds discuss events; Small minds discuss >...

How to record a stock award
My company started giving out stock awards instead of stock options. How should these be recorded in Money (using 2004)? I have put it in as a stock option, when they "vest" they are sold and taxes are taken out. How do you record the transaction so that the taxes are shown as withheld? Thanks. Michelle ...

is there a limit to the amount of space in filter
I am using filters on exell. Does anynone know if there is a limit to the amount of space in the pop up filter box. I appear to have several lines missing when searching in looking in certain cells??? I believe Excel2002 displays up to 1,000 unique values in the autofilter dropdown list. There may be some workarounds to get what you want, depending on your data list structure. *********** Regards, Ron XL2002, WinXP-Pro "ian coney" wrote: > I am using filters on exell. > Does anynone know if there is a limit to the amount of space in the pop up > filter box. I ap...

Shortcut for Filter- Custom Autofilter
I use Custom Autofilter (Autofilter-pull down-custom...)on daily basis. Is there a way I can create shortcut or toolbar for this command? Thanks Hi Eileen Not that I know of. But maybe you like my Filter ADD-in to do what you want http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Eileen" <anonymous@discussions.microsoft.com> wrote in message news:951101c4d447$76cc17f0$a401280a@phx.gbl... >I use Custom Autofilter (Autofilter-pull down-custom...)on > daily basis. Is there a way I can create shortcut or > toolbar for this...

Export Filtered Form Data To Excel
Here's one that's been bugging me for about a week now... I have a form that allows users to filter records, simple enough. But I want to give them the option to export the filtered records to Excel. I don't want to use the docmd.outputTo due to it won't filter the records, it puts all of the records in the file. I've looked around and found some code (actually that I'm already using), but the problem is that it outputs EVERYTHING on the form. My goal is to output only the fields that are on the form. I found another bit of code (following) that does what I need...

Trigger Update Current Record Only
I have written a trigger that updates prices based on changes to subdescription3. The trigger works but it updates all records that meet the conditions. I only want to update the current record to improve processing time. How do I tell the trigger that I only want it to process the current item id? join to the "inserted" table in an Update or Insert trigger. See SQL Server books online or MSDN. -- -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post...

using for XML without SQLXMl
In good old asp I could do the following set objxml=server.CreateObject("MSXML2.DOMDOCUMENT.3.0") set tcmd=server.CreateObject("ADODB.COMMAND") tcmd.ActiveConnection=application("DBCONNECTION") tcmd.CommandText=sSQL tcmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" tcmd.Properties("Output Stream")=objxml tcmd.Execute ,,1024 sSQL was a xml template that used the sql namespance and would return multiple recordsets as xml into the resulting objxml. Worked great!! Is there a way of doing the exact same thing in .NET WI...

Outlook 2003 Mail Filter oddity
Hello, I've tested this issue on another computer running IMAP on Outlook 2003 and it doesn't occur. The user says this was not always occuring. My client uses Outlook 2003 configured with IMAP, it has a mail rule to move sent items from the local PST to the sent items folder in the IMAP account. When the mail is moved by the mail rule it places the email at the top of the list under the IMAP sent items folder. The user filters his sent items "Arranged by: TO" w/ "A on top" It is only filed appropriately after he clicks on the email in ...

unbound forms saving records in access 2003
i have created a unbound local form to record details and wish to save the record to a table which is linked to the database on the server Access 2003 -- RFortune Any reason why you don't just bind the form to the table? "Bob" <Bob@discussions.microsoft.com> wrote in message news:0928FA10-5E67-485F-ABB3-6A025E43B814@microsoft.com... > i have created a unbound local form to record details and wish to save > the > record to a table which is linked to the database on the server Access > 2003 > -- > RFortune The data base is multi user and i...

record the sign on
Hi all, I posted this on the formsprogramming but so far no response so I thought I would try here. I would like to know how I can record the username of an employee that has signed onto our network. We have a large Access Database with an Access front end and SQL backend on which I would like to record the user Id on a field in some of the tables so that we know who has created a particular record. I have already put in place the date and time but we need to know the user ID. All users use a username and a password to get onto the network Any help would be tremendous and thanks in advance ...

Open Form with Current Record
Hello, I have a user form that when the user clicks the ok button I want it to open another form based on the same record number. I can make this happen using the Access Macro as follows: Form Name: frmTask_Complete View: Form Filter Name: Where Condition: [Task_ID]=[Forms]![frmTask_List]![Task List Subform].[Form]! [Task_ID] Data Mode: Window Mode: Normal I want to use VB Code instead of the Access Macro, since I have other controls I want to run and Access Code is very limited, but I cannot get my VB Code to open the frmTask_Complete with the current record (Task_ID). ...

How to eliminate the Alert message in Excel Logaritmic Chart
I'm working with Scatter Charts in logaritmic scale, and there is a message that come out all the time saying that cero or negative values cannot be plotted ... etc... I want to eliminate this message in order to work more continuesly: how can I do it? Hi, You can not turn the warning off. Cheers Andy Aldo Del Monte wrote: > I'm working with Scatter Charts in logaritmic scale, and there is a message > that come out all the time saying that cero or negative values cannot be > plotted ... etc... > I want to eliminate this message in order to work more continuesly: ho...

Setting form bookmark
Can anyone help? I have a form bound to a table. When a drop down list on the form is changed, I want to change the current record the form displays. I tried using the 'usual' code to do this: Private Sub cmbCourseAlsoRunning_AfterUpdate() Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "intCourseRefno = " & Me.cmbCourseAlsoRunning If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark End If End Sub However, this of course doesn't work because the form is opened with a WHERECONDITION specified, meaning tha a filter is ap...

Crating a Macro or button to move record
I'm having an MS Access 2007 database for employees and every thing is going ok but what i want to do now is when a person is promoted or terminated i want to overwrite his data and keep a history record for him in another table where the record will be identical to the main record before the update. in other words i want to copy the old data automatically by pressing 1 button before i update his information. Is it applicable to be done in MS Access or not? and how? Many Thanks and Best regards -- Ahmed Fathi Systems Engineer MCSE - MCDBA- CCNA Hi Copy the table containing the reco...

Dont print Kit details on 40 column receipt template
Could somebody share how to make it so all of the Kit detail does not print onto the 40 column receipts? Microsoft does have an article in the knowledge base that discusses how to do this, however, it seems to be old material that needs to be updated. It does not match or work with the current receipt.xml that comes with 1.3 Refresh. I believe it is something very simple to do, but I am having problems making it work correctly. Any help would be appreciated. thank you. Scratch that question... the 1.3 Refresh receipt.xml already has the fix included into it. haha, Ive spent the last ...

BeforeUpdate without update?
I have a form which is using Oracle as the datasource = single table and the controls are mostly bound (2 textboxes are not bound and exist in order to display names from ids). the form displays only a single record at a time. If I make changes to a new record (i.e. fill in the first field) and attempt to navigate to the next record, the beforeUpdate event fires and then I get an error saying such and such a field is null and must be populated first,etc. This works everytime. My form also has 2 buttons to navigate away from the form (all other navigation such as closing the form, etc. have b...

Problems validating an xml file without specifying the default namespace.
Hi All, I'm trying to validate an xml document. I'm having trouble setting the default namespace of the xml document. If I hard encode the namespace in the xml file then everything works fine. But I can't do this because it breaks our old tools which validate the xml to a dtd (error saying the dtd doens't allow xmlns attribute on the root element). So I'm trying to add the default namespace to the xml file by code. As follows: string DefaultNameSpace = "http://www.mydefaultnamespace.com"; NameTable nt = new NameTable(); XmlNamespaceManager nsmgr = new XmlName...

How do I makd the tab go from column 'L' back to 'A' without cont.
After setting up a table in excel, I only use up to column 'L'. As I tab from column to column I want the tab to revert back to 'A' after getting done with 'L'. It just keeps going on to 'M' and farther. Can I stop it at column 'L' and go back without using the mouse? See Bob Phillips' site for info. http://www.xldynamic.com/source/xld.xlFAQ0008.html Also....... If you start in, say A1, and TAB your way across to column L, hit the <ENTER> key when leaving L1. You will drop to A2. Gord Dibben Excel MVP On Fri, 29 Oct 2004 14:00:04 -...

Filtering a list
Hello. I have a list of 'N' number of names, phone numbers, addresses, which looks like the following: Serial Name Phone Address ------ ---- ----- ------- 1 A 44656 Somewhere 2 B 41464 Someplace 3 C 65465 Nowhere .. . . . .. . . . .. . . . N NN 45646 Noplace Now I need to single out some of the phone numbers and corre that matches the serial numbers which I have put in another column. So, how do I copy the entire rows that matches the phone numbers I have placed in a separate column? I don't know whether I am clear enough or not, but any help will be much appreciated. Need...

Mail merge does not work with the old records from CRM 3.0
I am stucked with one of the upgrade from CRM 3.0 to CRM 4.0. When i create a new accounts , mail merge works fine but when i use existing accounts from CRM 3.0 , then mail merge doesnt not work and come with the error "Record 1 contained too many data fields .....". Source does not even contain data with the old records . anyone have seen this behaviour ? Thanks /Aamir ...

Bottom left corner of the screen stops displaying number of record
I use the filter mode a lot. I particularly use seeing the number of records found based on the filter criteria I selected in the filter mode. However, I have several worksheets where something has happened such that the bottom left corner of the screen stops displaying the number of records found whenever I'm iin the filter mode. How can I get it so that I can see the number of records found again? Hi see: http://www.contextures.com/xlautofilter02.html#Count -- Regards Frank Kabel Frankfurt, Germany "Dr. Sidle" <Dr. Sidle@discussions.microsoft.com> schrieb im Newsb...

Money Bill Pay without Fees
A couple of years ago my bank decided that the free bill pay no longer applied to Money users. So they started charging $6.95 per month. Now they want $8.00 per month. I am having difficulty finding banks in the US that support Money bill pay for a smaller fee or no fee. The list of institutions on the Money website is non specific regarding the support of Money bill pay. It's pretty common for banks to charge for Money connectivity -- they pay licensing fees to offer the service to you. Often, you can use their web-based billpay at no cost. "It's me again" <Itsme...

SBS 2003 R2 Exchange MX Record?
To anyone who can help the now emotionally challenged..... Scenario: have SBS 2003 R2 installed and configured on network. Our current exchange is outsourced but am attempting to bring email in-house... slowly. We have 2 options - cut off the hosted exchange completely or utilize them as a backup server while our in-house exchange serves as the primary mail server. Problem is that I need to locate both the MX and A records. I have the IP for the A record but have yet to find anything within SBS 2003 indicating that it is an MX record. This is really becoming a tickler for me seeing...