473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Main Form ComboBox to Filter Records in SubForm

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, 3189 views)
May 24 '09 #1

✓ answered by ADezii

Programmatically 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 subfOrderDetails.
  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
    4.  
    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] & "'")
    8.  
    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.

5 14573
ADezii
8,834 Expert 8TB
Programmatically 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 subfOrderDetails.
  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
    4.  
    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] & "'")
    8.  
    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
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
ADezii
8,834 Expert 8TB
@shiwawa
  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
NeoPa
32,556 Expert Mod 16PB
You should find the following article (Example Filtering on a Form) helpful. There is an example attached.
May 24 '09 #5
NeoPa
32,556 Expert Mod 16PB
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

3
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...
0
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...
4
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...
1
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...
2
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. ...
9
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...
7
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...
6
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
13
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.