By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

Main Form ComboBox to Filter Records in SubForm

P: 2
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, 2329 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.

Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,616
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

P: 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
Expert 5K+
P: 8,616
@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
Expert Mod 15k+
P: 31,308
You should find the following article (Example Filtering on a Form) helpful. There is an example attached.
May 24 '09 #5

NeoPa
Expert Mod 15k+
P: 31,308
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

Post your reply

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