473,836 Members | 2,303 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Main Form ComboBox to Filter Records in SubForm

2 New Member
I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing something as this looks quite simple.

I have attached a screenshot to help.

What I need to for the records to be filtered by the Section Field using either of the three options in the ComboBox. The ComboBox looksup data from a Sections table (SectionID, Section).

Main Table Name: Orders (Master Link: Order ID)
SubForm Table Name: Order Details (Child Link: Order ID)
ComboBox Object Name: CboFilter

I will appreciate any help.
Attached Images
File Type: jpg ScreenShot.jpg (26.1 KB, 3208 views)
May 24 '09 #1
5 14645
8,834 Recognized Expert Expert
Programmaticall y setting the Filter Property for a Sub-Form from a Main Form can be a little problematic since you must know exactly when to turn the Filter ON and when to turn it OFF. That being said, here are a few, simple, assumptions:
  1. The 2 Tables involved are [Orders] and [Order Details].
  2. These Tables are Linked by the [Order ID] Field via a 1 to MANY Relationship, namely:
    [Orders].[Order ID]{1} ==> [Order Details].[Order ID]{MANY}.
  3. The [Order Details] Table contains a Field named [Section]{TEXT}.
  4. The Name of the Order Details Sub-Form is Order Details.
  5. The Source Object for the Order Details Sub-Form is subfOrderDetail s.
  6. The Name of the Filtering Combo Box is cboFilter.
  7. cboFilter draws its Data from a Sections Table consisting of [SectionID] and [Section] Fields.
  8. cboFilter is a Single Column Combo Box consisting of the [Section Field] only. If this is not the case, the code must be modified, namely substitute Me![cboFilter].Column(1) for Me![cboFilter].
  9. Here is the code which must reside in the AfterUpdate() Event of cboFilter:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cboFilter_AfterUpdate()
    2. Dim intNumOfDetails As Integer
    3. Dim strMsg As String
    5. 'Will the Filter (Order ID/Section) actually produce any Records?)
    6. intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
    7.                           " And [Section] = '" & Me![cboFilter] & "'")
    9. If Not IsNull(Me![cboFilter]) Then      'must be an entry
    10.   If intNumOfDetails > 0 Then           'Filter produces Records
    11.     Me![Order Details].Form.Filter = "[Section] = '" & Me![cboFilter] & "'"
    12.     Me![Order Details].Form.FilterOn = True
    13.   Else
    14.     strMsg = "No Order Details exist for Order ID [" & Me![Order ID] & "] and " & _
    15.              "Section [" & Me![cboFilter] & "]!"
    16.       MsgBox strMsg, vbExclamation, "No Order Details"
    17.   End If
    18. End If
    19. End Sub
  10. Any problems, just let us know.
May 24 '09 #2
2 New Member
Used your code and got an error stop on this line:

intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
" And [Section] = '" & Me![CboFilter] & "'")

Meanwhile, your assumptions were right except for nos 4 and 5 which I adjusted accordingly.

Any ideas?
May 24 '09 #3
8,834 Recognized Expert Expert
  1. What is the Error Message that you are receiving?
  2. I know some questions are redundant, but just to be sure...
  3. Is your Table Name [Order Details]?
  4. Is the [Order ID] Field named 'exactly' that, namely [Order ID], and not OrderID?
  5. Is the [Order ID] Field NUMERIC in nature?
  6. Does the [Order Details] Table have a Field named Section?
  7. Is the Section Field a TEXT Data Type?
  8. Is your Filter Combo Box named cboFilter?
  9. If all the above Response are Yes, would you be willing to mail me a Copy of the Database with some Sample Data?
May 24 '09 #4
32,584 Recognized Expert Moderator MVP
You should find the following article (Example Filtering on a Form) helpful. There is an example attached.
May 24 '09 #5
32,584 Recognized Expert Moderator MVP
A new thread (Add Button) has been created from a new post in this thread. Please visit if you think you can be of help.
Sep 17 '09 #6

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

Similar topics

by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I have a primary form named TestResults, which is connected to data in a table named TestResults. There are basically two other tables that are related to the TestResults table (and the primary form) named Names-Normalized and SiteAddresses. The...
by: Colin | last post by:
In access 2000 I need to filter records in a Subform by pushing a button on a command button located on the Main form. The Main form is blank. Its only purpose is to contain the subform which is in a datasheet format. So far… 1)I have created a query called "CatFilterQuery" which results in a prompt to enter the "category field" to filter on. 2)Created a macro called "FilterCategory" which is based on the query mentioned above and uses...
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form. In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous...
by: New2Access | last post by:
Hi, I have a form thats used to enter records into a table. The table and form have fields for Name, week, project, and hours. To assist users, I wish to add a subform that will show them how many hours and for which projects they've already made entries. To do this, I created a query based on the same table with criteria pointing to the current user, and the week selected in the main form. This seems to work fine except the...
by: origin197511 | last post by:
Hello all... I'm having an issue with MSAccess 2000. I have a Form that holds records of my cartridge loads for a rifle and a subform that lists all groups that have been fired with that load. They are linked by the load_id field and when I just browse through the loads everything shows up correctly. Each recorded group is displayed on a line of the datasheet subform. However, when I filter the form for say my "Selected" flag...
by: Susan Bricker | last post by:
Greetings. I am having trouble populating text data that represents data in my table. Here's the setup: There is a People Table (name, address, phone, ...) peopleID = autonumber key There is a Judge Table (information about judges) judgeID = autonumber key
by: Stu | last post by:
Hi, I have a combobox who's values change the recordsource of the form. Within this form, there is a subform, whos records also need to change pending the value in the combobox. I am able to get the main forms values to change, but not the subform. I've only been using Access 97 for about a month now, so it might be fairly simple. Here is my code: Private Sub combo1_Change() If Me.combo1.Value = "Standard Procedures" Then...
by: OllyJ | last post by:
Hope you can help guys. I have a scheduling database, the main schedule table contains the following fields: AddedDate Day/Night Machine Arm/Head StockCode ToolNumber
by: bkberg05 | last post by:
Hi - I have a form called Vendor (tied to table with same name). Each vendor_id can belong to more than one 'category'. So there's a table called Vendor_Category which contains just vendor_id and category. On the Vendor form, there's a subform called Vendor_Category. When a user is in the Vendor form and is in a record for a particular vendor_id, they use a combo_box in the Vendor_Category subform to select Vendor_Categories that can...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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,...
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...
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();...
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
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.